In this article, we will explore SQL Server Nested Transactions, a transaction block with one or several transactions.
The image describes a simple model of the nested transaction.
The inner transaction is a stored procedure that consists of transaction blocks. MSDN recommends “keeping transactions as short as possible” that is totally opposite to the first approach. In my opinion, I do not recommend using nested transactions. Still, sometimes we have to use them to solve some business problems.
Thus, we are going to figure out:
- What will occur when an outer transaction is rolled back or committed?
- What will occur when an inner transaction is rolled back or committed?
- How to handle nested transactions errors?
To begin with, we will create a demo table and test possible cases.
USE AdventureWorks -----Create Demo Table---- CREATE TABLE CodingSightDemo (NumberValue VARCHAR(20))
Case 1: Both outer and inner transactions are committed.
TRUNCATE TABLE CodingSightDemo
--<*************OUTHER TRANSACTION START*************>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
--<INNER TRANSACTION START>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
COMMIT TRAN
--< INNER TRANSACTION END>
INSERT INTO CodingSightDemo VALUES('Three')
COMMIT TRAN
--<************* OUTHER TRANSACTION END*************>
SELECT * FROM CodingSightDemo
In this case, all the records are successfully inserted into the table. We assumed that every INSERT statement does not return an error.
Case 2: Outer transaction is rolled back, inner transaction is committed.
TRUNCATE TABLE CodingSightDemo
--<*************OUTHER TRANSACTION START*************>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
--<INNER TRANSACTION START>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
COMMIT TRAN
--< INNER TRANSACTION END>
INSERT INTO CodingSightDemo VALUES('Three')
rollback TRAN
--<************* OUTHER TRANSACTION END*************>
SELECT * FROM CodingSightDemo
As you can see, the records aren’t inserted into the table because the inner transaction is a part of the outer transaction. For this reason, the inner transaction rolls back.
Case 3: Outer transaction is committed, inner transaction is rolled back.
TRUNCATE TABLE CodingSightDemo
--<*************OUTHER TRANSACTION START*************>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
--<INNER TRANSACTION START>
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
ROLLBACK TRAN
--< INNER TRANSACTION END>
INSERT INTO CodingSightDemo VALUES('Three')
COMMIT TRAN
--<************* OUTHER TRANSACTION END*************>
SELECT * FROM CodingSightDemo
In this case, we got an error and inserted the latest statement into the table. As a result, some questions arise:
- Why did we get an error?
- Why was the latest INSERT statement added to the table?
As a rule, the ROLLBACK TRAN statement rolls back all open transactions executed in the current session. We cannot write a query because it will return an error.
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
ROLLBACK TRAN
ROLLBACK TRAN
We will examine how this rule may impact our case. The ROLLBACK TRAN statement rolls back inner and outer transactions. For this reason, we get an error when running the COMMIT TRAN statement because there are no open transactions.
Next, we will add an error handling statement to this query and modify it based on the defensive programming approach (as Wikipedia states: Defensive programming is a form of defensive design intended to ensure the continuing function of a piece of software under unforeseen circumstances). When we write a query without taking care about error handling and get an error, we may face the data integrity corruption.
With the next script, we will use save points. They mark a point in the transaction and if you want, you can rollback all DML (Data Manipulation Language) statements to the marked point.
BEGIN TRY
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
--<INNER TRANSACTION START>
SAVE TRANSACTION innerTRAN
BEGIN TRY
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION innerTRAN
PRINT 'Roll back occurs for inner tran'
END
IF XACT_STATE() <> 0
BEGIN
COMMIT TRAN
PRINT 'Commit occurs for firt open tran'
END
END CATCH
--< INNER TRANSACTION END>
INSERT INTO CodingSightDemo VALUES('Three')
COMMIT TRAN
END TRY
BEGIN CATCH
BEGIN
IF XACT_STATE() <> 0
ROLLBACK TRAN
PRINT 'Roll back occurs for outer tran'
END
END CATCH
--<************* OUTHER TRANSACTION END*************>
SELECT * FROM CodingSightDemo
This query will handle the error when the inner transaction gets an error. Also, outer transactions are successfully committed. However, in some cases, if the inner transaction gets an error, the outer transaction must roll back. In this case, we will use a local variable that will keep and pass the inner query error state value. We will design the outer query with this variable value and the query will be as follows.
--<*************OUTHER TRANSACTION START*************>
DECLARE @innertranerror as int=0
BEGIN TRY
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('One')
--<INNER TRANSACTION START>
SAVE TRANSACTION innerTRAN
BEGIN TRY
BEGIN TRAN
INSERT INTO CodingSightDemo
VALUES('Two')
COMMIT TRAN
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
SET @innertranerror=1
ROLLBACK TRANSACTION innerTRAN
PRINT 'Roll back occurs for inner tran'
END
IF XACT_STATE() <> 0
BEGIN
COMMIT TRAN
PRINT 'Commit occurs for firt open tran'
END
END CATCH
--< INNER TRANSACTION END>
INSERT INTO CodingSightDemo VALUES('Three')
if @innertranerror=0
BEGIN
COMMIT TRAN
END
IF @innertranerror=1
BEGIN
ROLLBACK TRAN
END
END TRY
BEGIN CATCH
BEGIN
IF XACT_STATE() <> 0
ROLLBACK TRAN
PRINT 'Roll back occurs for outer tran'
END
END CATCH
--<************* OUTHER TRANSACTION END*************>
SELECT * FROM CodingSightDemo
Conclusions
In this article, we explored nested transactions and analyzed how to handle errors in this type of query. The most important rule about this transaction type is to write defensive queries because we can get an error in outer or inner transactions. For this reason, we have to design error handling behavior of the query.
