How to Handle Errors in SQL Server Nested Transactions

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.

Case 1: Both outer and inner transactions are committed.

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.

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.

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.

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.

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.

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. 

References

Nesting Transactions

SAVE TRANSACTION

Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.