An easy way to avoid SQL Server nested transactions
Transactions are an integral part of any (serious) database development when there is need for data consistency.
Transactions in a database environment have two main purposes:
- To provide reliable mechanism to allow correct recovery from failures and keep a database consistent
- To provide isolation between programs accessing a database concurrently.
It’s impossible to mention transactions without mentioning the ACID (Atomic, Consistent, Isolated and Durable) attributes that every transaction has to support.
- Atomicity: Indicates that everything or nothing get’s executed.
- Consistency guarantees that a transaction never leaves your database in a half-finished state.
- Isolation no transaction should be able to interfere with another transaction
- Durability once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.
Every transaction in Microsoft SQL Server has to start with
BEGIN TRANSACTION, to end with
COMMIT in case everything went fine, or
ROLLBACK to undo the changes made so far. There is a possibility to
SAVE a checkpoint before the commit, in some particularly long transactions.
Where to start the transaction
There are mainly two places where to start a transaction
- By the calling application (client)
- In the database itself as part of a stored procedure
Transaction within a Stored procedure
Managing transactions within a stored procedure mean that the transaction will be open and closed within the database. Usually this is a favorite way of managing transactions when there are a large amount of objects to be manipulated (retrieved or saved).
The content of a stored procedure with transactions could be summarized in following steps:
- Stored procedure creates a transaction
- There is some data manipulations
- Stored procedure commits (or rollbacks) the transaction
- Stored procedure returns the results to the calling application
A skeleton of a very simple stored procedure with transactions could be as follows:
CREATE PROCEDURE dbo.TestStoredProcedure AS BEGIN BEGIN TRY -- transaction start BEGIN TRANSACTION -- data manipulation here... -- transaction end COMMIT END TRY BEGIN CATCH -- error situation ROLLBACK END CATCH END
Nested transactions is a condition where we start several transactions that are part of some other, parent transactions, as shown in the image below.
SQL Server manages the nested transactions by increasing a count every time we create one, and this is reflected in the
@@TRANCOUNT variable. At any time we are able to check how many transactions are open at the time of execution. Every time we call
COMMIT the count get’s decreased by one, and exceptionally if we call a
ROLLBACK this count will be set automatically to 0.
After calling the
ROLLBACK from an internal transaction (not the outer most), as mentioned the
@@TRANCOUNT will set to 0. If we try to
ROLLBACK again the transaction we are going to get a nasty error:
Msg 3903, Level 16, State 1, Procedure xxx, Line xx The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. Msg 50000, Level 16, State 2, Procedure xxx, Line xx Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
This will be especially true if you are using
TRY and CATCH mechanism of managing exceptions.
Please check this great post regarding the Try and Catch topic
Avoiding nested transaction
In my opinion the best way of solving the issues with nested transactions is to avoid them as much as possible, by using a pattern shown here below when creating the stored procedures. The goal is code your stored procedures so that there is only one transaction. This very easy to achieve if you follow this pattern in all of your stored procedures.
As you may see, the
dbo.OuterMostStoredProcedure creates a transaction only if there is no transactions created. The
dbo.InnerStoredProcedure has to apply the SAME pattern with exactly the same checks. The end result is that there will be only one transaction running across several stored procedures, and we would be able to call all of them independently from the client application. This means, if we want to call the
dbo.InnerStoredProcedure directly, this transaction scenario would be automatically perfectly handled.
check out the example:
/* -------------------------------------*/ CREATE PROCEDURE dbo.OuterMostStoredProcedure AS BEGIN BEGIN TRY DECLARE @local bit set @local = 0 -- Starting transaction only if it is the outermost sp IF(@@TRANCOUNT BEGIN BEGIN TRANSACTION SET @local = 1 END -- calling some internal stored procedures -- that could raise errors ... EXEC dbo.InnerStoredProcedure EXEC dbo.InnerStoredProcedure -- committing changes only if it is the outermost sp IF (@@trancount>0 and @local=1) BEGIN COMMIT END END TRY BEGIN CATCH IF (@@trancount>0 and @local=1) BEGIN ROLLBACK END RAISERROR ('error message', 16, 1) ; END CATCH END GO /* -------------------------------------*/ CREATE PROCEDURE dbo.InnerStoredProcedure AS BEGIN BEGIN TRY DECLARE @local bit set @local = 0 -- Starting transaction only if it is the outermost sp IF(@@TRANCOUNT BEGIN BEGIN TRANSACTION SET @local = 1 END -- some internal code -- committing changes only if it is the outermost sp IF (@@trancount>0 and @local=1) BEGIN COMMIT END END TRY BEGIN CATCH IF (@@trancount>0 and @local=1) BEGIN ROLLBACK END RAISERROR ('error message', 16, 1) ; END CATCH END GO /* -------------------------------------*/ EXEC dbo.OuterMostStoredProcedure GO