2015-07-26

@@TRANCOUNT = 2 During DML statement Executing

In SQL Server BOL, it said @@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. Also, it said the open_tran column of sys.sysprocesses returns the number of open transactions for the process. So you may intuitively think that if that transaction count value of a process is greater than 1, it should be an explicit nested transaction.
However, 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 example:

IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND name = 'T1') DROP TABLE T1;
GO
CREATE TABLE T1
(
Col1 int
);
GO

-- INSERT
INSERT INTO T1
--SELECT @@TRANCOUNT;
SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID;
-- returns 2
SELECT Col1 FROM T1;

-- UPDATE
UPDATE T1 SET
Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
WHERE Col1 = 2;
-- returns 2
SELECT Col1 FROM T1;

-- DELETE
DELETE
FROM T1
WHERE Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
-- returns nothing (as @@TRANCOUNT / sys.sysprocesses open_tran = 2)
SELECT Col1 FROM T1;

-- INSERT within Explicit Transaction
BEGIN TRAN
INSERT INTO T1
--SELECT @@TRANCOUNT;
SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID;
COMMIT
-- Still returns 2
SELECT Col1 FROM T1;

-- UPDATE within Explicit Transaction
BEGIN TRAN
UPDATE T1 SET
Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
WHERE Col1 = 2;
COMMIT
-- Still returns 2
SELECT Col1 FROM T1;

-- DELETE within Explicit Transaction
BEGIN TRAN
DELETE
FROM T1
WHERE Col1 = --@@TRANCOUNT
(SELECT open_tran FROM sys.sysprocesses WHERE spid = @@SPID)
COMMIT
-- returns nothing (as @@TRANCOUNT / sys.sysprocesses open_tran = 2)
SELECT Col1 FROM T1;

This shows that during execution of a DML statement, there will be more than one opened transactions reported. The results are the same for @@TRANCOUNT, open_tran column from sys.sysprocesses, or the open_transaction_count column from the sys.dm_exec_requests.
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 DML statement’s execution. To be clear, the second transaction is open only while a DML statement is executing. Such situation can be easily observed especially during Process Blocking.

No comments:

Post a Comment