When developing data-driven software, it is often vital that you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls in a single, logical unit-of-work. The classic example of debiting one bank account, then crediting another, is often used to illustrate the importance of transactions. If the process blows up somewhere in the middle, you want everything put back as it was initially. It is the ‘all or nothing’ principle.
For Microsoft developers who write applications against a SQL Server 2005 database, it is common to write transaction logic in C#, using the System.Data.SqlClient.SqlTransaction class that wraps calls to SQL Server statements or stored procedures. Another option is to utilize MTS (Microsoft Transaction Server) to support distributed transactions. When these approaches are used, writing T-SQL code to manage transactions typically can be avoided. Sometimes, however, it makes the most sense to manage transactions at the stored procedure level. For example, you might choose to implement a process that requires numerous, process-intensive queries and data manipulation statements as a database stored procedure or a set of procedures. This tip will show you how to avoid transaction-related errors when nesting procedure calls in SQL Server.
In its simplest form, here is a SQL Server stored procedure that manages a transaction:
CREATE PROCEDURE [dbo].[simple_proc]
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
PRINT 'Executing simple proc.'
--Execute logic within transaction...
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
RAISERROR ('Error in simple proc!',16,1)
END CATCH
END
A transaction is started. If things go well, changes are committed in the database. Otherwise, the changes are rolled back. This works fine in most situations. That is, unless you have an environment in which you have other stored procedures that invoke this one and they too manage transactions. In such an environment, problems can arise because, though each ‘BEGIN TRANSACTION’ increments @@TRANCOUNT by one and each ‘COMMIT TRANSACTION’ decrements the count, a ‘ROLLBACK TRANSACTION’ rolls back all changes. That is, a rollback sets @@TRANCOUNT all the way back to zero. Attempting to execute a second rollback will generate an error with the message ‘The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.’.
This situation can occur if ‘simple_proc’ invokes a second procedure, ‘another_simple_proc’, but ‘another_simple_proc’ can also be called on its own. To account for the later scenario, ‘another_simple_proc’ must manage its own transactions instead of letting a caller do it for him. Because, in reality, it is not feasible to always know which procedure will be the outermost procedure in the chain, a certain amount of flexibility is necessary. So, some logic must be written to deal with this.
Next is an updated version of simple proc with some additional logic.
CREATE PROCEDURE [dbo].[simple_proc]
AS
BEGIN
--Holds info on whether or not this proc originated the
--transaction.
DECLARE @v_trans_started BIT
BEGIN TRY
PRINT 'Executing simple proc.'
SET @v_trans_started = 0
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION;
SET @v_trans_started = 1
END
ELSE
SET @v_trans_started = 0
--Execute logic within transaction...
IF @v_trans_started = 1
BEGIN
SET @v_trans_started = 0
COMMIT TRANSACTION;
END
END TRY
BEGIN CATCH
IF @v_trans_started = 1
BEGIN
SET @v_trans_started = 0
ROLLBACK TRANSACTION;
END
RAISERROR ('Error in simple proc!',16,1)
END CATCH
END
The logic surrounding ‘BEGIN TRANSACTION’ interrogates the value of @@TRANCOUNT. If there already is an open transaction, it does nothing. The subsequent commit and rollback statements will execute only if the current procedure started the transaction. In other words, the ‘COMMIT TRANSACTION’ or ‘ROLLBACK TRANSACTION’ can only execute if the ‘BEGIN TRANSACTION’ was invoked in the same procedure. The key principal is this: If you didn’t start the transaction, you have no business managing it. Simply leave it alone and return that responsibility to the caller. The caller, in turn, will either deal with it or in turn pass it up further, and so on. There are a number of different ways to achieve the same goal. It is probably a good idea to write a separate function that can encapsulate some of this routine logic so it can be reused throughout your application.
About the Author
Michael Klaene is a Principal Consultant with Sogeti USA. He has over 10 years of experience in IT, and specializes in J2EE, .NET, and Oracle design and development.
