Nested Transactions in SQL Server

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:

USE tempdb;
GO

CREATE TABLE tbl
(ID int IDENTITY(1,1) PRIMARY KEY,
Name char(3));
GO

Right now, the transaction nesting level is zero, which means that there is no open transaction in my session:

PRINT @@trancount
-----
0

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:

BEGIN TRAN
PRINT @@trancount
INSERT INTO tbl (Name) VALUES (''aaa'');
-----
1

(1 row(s) affected)

Now it’s time to execute BEGIN TRAN statement again, check the current nesting level and insert another row:

BEGIN TRAN
PRINT @@trancount
INSERT INTO tbl (Name) VALUES (''bbb'');
-----
2

(1 row(s) affected)

At first, everything looks exactly the same as previously — the transaction nesting level has increased and there are two rows in the table:

SELECT * FROM tbl;
------
ID Name
1 aaa
2 bbb

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:

BEGIN TRAN
PRINT @@trancount
UPDATE tbl SET Name = ''ccc'' WHERE ID = 1;
-----
3

(1 row(s) affected)

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:

COMMIT TRAN
PRINT @@trancount
SELECT * FROM tbl;
-----
ID Name
1 ccc
2 bbb

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:

SELECT request_owner_id, resource_type, request_mode, request_status
FROM sys.dm_tran_locks;
-----
request_owner_id resource_type request_mode request_status
90664 OBJECT IX GRANT
90664 PAGE IX GRANT
90664 KEY X GRANT
90664 KEY X GRANT
90664 OBJECT IX GRANT

So, what will happen when we execute ROLLBACK statement?

ROLLBACK

Suddenly, the transaction nesting level dropped from two to zero, and there are no rows in our table anymore:

PRINT @@trancount
SELECT * FROM tbl;
------
0

(0 row(s) affected)

Lesson learned:

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

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in SQL Server, T-SQL and tagged by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.