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
INSERT INTO T1
SELECT Col1 FROM T1;
UPDATE T1 SET
Col1 = @@TRANCOUNT
WHERE Col1 = 2;
WHERE Col1 = @@TRANCOUNT;
Here’re the results, with comments added after the output from each statement:
(1 row(s) affected)
^^ INSERT statement ^^
^^ first SELECT statement ^^
^^ UPDATE statement ^^
^^ second SELECT statement ^^
^^ DELETE statement ^^
(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.
Thank you for posting this - nice if Microsoft added this note to the BOL entries for the associated DMVs!
Very nice finding and explanation. I was troubleshooting my application and was wondering where the second count of transaction is coming from.
So can I infer from this, if ran my DML(delete) statement without an explicit transaction, sql server will take care of rolling back stuff in case anything weird happen during the execution, to maintain Database Atomicity and Consistency.
@Varun: Yes, ACID is certainly in effect for single DML statements in SQL Server, with or without an explicit transaction.
Here i have 1 doubt :(
Update command automatically starts implicit transaction which will rollback in case of err. Then why it again started another nested transaction :(
@Manish: The second transaction is just a SQL Server internal implementation detail. Without looking at the SQL Server source code, I cannot say why it was implemented that way, although I am sure there is a reason. My intent in writing this post was to make the readers aware of this behavior, because it could be potentially confusing and lead to wrong conclusions.