If you think there is such a thing as nested transaction in SQL Server, this post is for you.
Nesting Transaction vs. Nested Transactions
Technically you can begin a new transaction inside another one. What’s more, there is @@TRANCOUNT function that returns current transaction nesting level.
Note. This function is quite handy as there is a requirement in SQL Server that the transaction nesting level at the module’s entry has to be exactly the same when the module finishes execution. We will get back to this later.
But the possibility of nesting a transaction is not the same as nested transactions support. A transaction has to be atomic, consistent, isolated and durable, right?
Let me check if a transaction began inside another one behaves in this way. First, I will create a sample table in tempdb database:
Right now, the transaction nesting level is zero, which means that there is no open transaction in my session:
After an explicitly started transaction, the nesting level is set to one (there is an active transaction). Inside this transaction I will insert a row into our table:
Now it’s time to execute BEGIN TRAN statement again, check the current nesting level and insert another row:
At first, everything looks exactly the same as previously — the transaction nesting level has increased and there are two rows in the table:
To see what really happens, we have to execute BEGIN TRAN statement once again and check the current nesting level. This time, we will also update the first row:
And this is when things are getting interesting. If we execute COMMIT TRAN statement, the transaction nesting level will decrease to two, and the updated row will be in the table:
But nobody else who is at the READ COMMITTED (or higher) transaction isolation level is able to read this row. And if we check the locks being held, we find that both rows are locked by the same transaction:
So, what will happen when we execute ROLLBACK statement?
Suddenly, the transaction nesting level dropped from two to zero, and there are no rows in our table anymore:
1. Committing inner (“nested”) transactions is ignored by the SQL Server. In fact, there are no real inner transactions at all.
2. All what BEGIN TRANSACTION statement executed inside a transaction does is increments @@TRANCOUNT counter.
3. All what COMMIT TRANSACTION statement executed inside a transaction does is decrements @@TRANCOUNT counter.
4. On the other hand, ROLLBACK always rolls back active transaction, no matter at what nesting level it is executed.
And the last fact is quite important — what it essentially means is that by executing ROLLBACK statement inside procedures, you will roll back the very one transaction and set the @@TRANCOUNT counter to zero. If this transaction starts outside this procedure, the @@TRANCOUNT will be different when the procedure finishes and SQL Server will throw an error.
My advice — do not use BEGIN TRAN inside a transaction. Use SAVE TRAN to create a savepoint (a point to which a cancelled transaction can return) instead.
Hope this helps