In this article, I will explain what are nested transactions and how we can use them. The article is structured in the form of interview questions.
Some questions may have a more detailed answer than what is needed, but the purpose is to give you a clear overview on this subject, not only a cheat sheet for your next interview 🙂
1. What are nested transactions?
According to Wikipedia, “a nested transaction is a database transaction that is started by an instruction within the scope of an already started transaction.“
Even though the implementation of nested transactions is may differ from a database system to another, the big picture is the same: they are executed as a whole and the changes they make in the database are not visible from the outside, until the outermost transaction was committed. This means also that if a rollback occurs during an inner transaction, no changes are persisted in the database.
A simple visual example is presented below:
Fig. 1: A nested transaction with two levels. It is obvious that only the outermost COMMIT statement finalizes the transaction set (
@@TRANCOUNT becomes 0 again).
2. What is the effect produced by an inner COMMIT from a nested set of transactions?
Besides decreasing the @@TRANCOUNT variable to 1, an inner COMMIT statement has no effect of the nested transactions set.
3. What is the effect of the outermost COMMIT statement in a nested set of transactions?
The outermost COMMIT statement is the one that gets executed last in a nested transaction set ( @@TRANCOUNT = 1 before the COMMIT and then becomes 0). This last COMMIT statement is the one that decides that the transaction is going to be persisted in the database.
4. What is the effect of an inner ROLLBACK statement in a nested set of transactions?
A ROLLBACK statement (it doesn’t matter on what level it is issued), will roll back all the changes from the nested transactions and automatically reset the @@TRANCOUNT counter to 1. An example of the powerful ROLLBACK is in the image below:
Fig. 2: A nested transaction with two levels. After a ROLLBACK is encountered, the @@TRANCOUNT becomes 0, no matter the level it had before and the transaction is considered finished.
5. Is there a situation when ROLLBACK does not roll back the entire set of nested transactions?
Yes, when we are using savepoints in our code. Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the
ROLLBACK TRAN without a specific name will always roll back the entire transaction.
An example of using savepoints can be seen in the image below:
Fig. 3: A nested transaction with two levels. We have 2 savepoints: T1_save and T2_save. After a ROLLBACK to a savepoint is encountered, the @@TRANCOUNT does not get reset to 0, but continues with the value it had before. However, after a ROLLBACK, @@TRANCOUNT = 0, no matter what.
6. What is the purpose of nested transactions?
A common scenario when nested transactions are useful is when we want to call a code that executes within a transaction (a stored procedure, for example), but we are inside a different stored procedure, so inside a different transaction.