Recently I was troubleshooting a blocking problem where a number of sessions were blocked, waiting to acquire a page latch. While blocking was occurring, there were several rows in the output from sysprocesses that looked like this (only relevant columns are shown):
spid status blocked open_tran waitresource cmd lastwaittype
—— ———- ——- ——— ————- ——- ————-
1001 suspended 1294 2 8:6:792624 INSERT PAGELATCH_UP
In this case, session 1001, executing an INSERT statement, is waiting to acquire a latch on page 792624, which happens to be a PFS page (792624 / 8088 = 98, a whole number of PFS intervals). While this may provide a clue as to the cause of blocking, this is not the main topic of this post.
Note that the value in the open_tran column is 2. The open_tran column is described in documentation as the “number of open transactions for the process.” The intuitive conclusion from this is that session 1001 has two explicit transactions open, one nested in the other. However, this system uses stored procedures exclusively, and a review of all stored procedures that insert rows did not find any code that used explicit nested transactions.
After some research, I found that explicit nested transactions are not the only reason why the transaction count can be greater than 1 during execution of a DML statement. Consider the following code fragment:
CREATE TABLE T1
(
Col1 int
);
GO
INSERT INTO T1
SELECT @@TRANCOUNT;
SELECT Col1 FROM T1;
UPDATE T1 SET
Col1 = @@TRANCOUNT
WHERE Col1 = 2;
SELECT Col1 FROM T1;
DELETE
FROM T1
WHERE Col1 = @@TRANCOUNT;
SELECT Col1 FROM T1;
Here’re the results, with comments added after the output from each statement:
(1 row(s) affected)
^^ INSERT statement ^^
Col1
———–
2
(1 row(s) affected)
^^ first SELECT statement ^^
(1 row(s) affected)
^^ UPDATE statement ^^
Col1
———–
2
(1 row(s) affected)
^^ second SELECT statement ^^
(1 row(s) affected)
^^ DELETE statement ^^
Col1
———–
(0 row(s) affected)
^^ third SELECT statement ^^
This shows that during execution of a DML statement that is not within any explicit transaction, there are actually two open transactions reported. The results are the same if instead of @@TRANCOUNT we use the open_tran column from sysprocesses, or the open_transaction_count column from the sys.dm_exec_requests DMV. Effectively, in addition to the one transaction always associated with any DML statement, there is another nested transaction opened internally by SQL Server, lasting for the duration of statement’s execution. This behavior occurs on all recent versions of SQL Server, starting with SQL Server 2000 (I did not test on older versions).
To be clear, the second transaction is open only while a DML statement is executing. The @@TRANCOUNT function (as well as sysprocesses and sys.dm_exec_requests) behaves as expected if used in a non-DML statement in procedural T-SQL code, which is the typical use case.
So as a practical matter, if you see the number of reported open transactions that is greater than expected, consider the context where that number was obtained, before concluding that it must be due to explicit nested transactions being used.
© 2019 Microsoft. All rights reserved.