@@TRANCOUNT = 2?

Question

Post reply

@@TRANCOUNT = 2?

duckpond_wkb

Valued Member

Points: 62

January 25, 2021 at 11:06 pm

#3837397

Just a curiosity question...

When executing a DML statement in autocommit (normal) mode, the @@TRANCOUNT seems to jump from 0 directly to 2 for no apparent reason, but when surrounded by explicit transactions, it behaves as one would expect:

CREATE TABLE #foo (i INT NOT NULL);
INSERT INTO #foo(i) VALUES (0);

BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
  BEGIN TRANSACTION;                  -- @@TRANCOUNT = 2
    UPDATE #foo SET i = @@TRANCOUNT;  -- @@TRANCOUNT = 3
  COMMIT TRANSACTION;
COMMIT TRANSACTION;
SELECT i FROM #foo;                   -- Returns 3

BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
  UPDATE #foo SET i = @@TRANCOUNT;    -- @@TRANCOUNT = 2
COMMIT TRANSACTION;
SELECT i FROM #foo;                   -- Returns 2

UPDATE #foo SET i = @@TRANCOUNT;      -- @@TRANCOUNT should be 1
SELECT i FROM #foo;                   -- Returns 2?

DROP TABLE #foo;

Can anyone explain why this happens? This often shows up when looking at deadlock graphs and it is very misleading, so I'm curious as to what is going on here.

January 26, 2021 at 7:33 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

@@TRANCOUNT itself is fine, I think. Does UPDATE increment and decrement @@TRANCOUNT as it processes?!

CREATE TABLE #foo (i INT NOT NULL);
INSERT INTO #foo(i) VALUES (0);
SET NOCOUNT ON;

SELECT 'A', @@TRANCOUNT

BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
    SELECT 'B', @@TRANCOUNT
  BEGIN TRANSACTION;                  -- @@TRANCOUNT = 2
    SELECT 'C', @@TRANCOUNT
    UPDATE #foo SET i = @@TRANCOUNT;  -- @@TRANCOUNT = 3
    SELECT 'D', @@TRANCOUNT
  COMMIT TRANSACTION;
    SELECT 'E', @@TRANCOUNT
COMMIT TRANSACTION;
    SELECT 'G', @@TRANCOUNT
SELECT i FROM #foo;                   -- Returns 3

BEGIN TRANSACTION;                    -- @@TRANCOUNT = 1
    SELECT 'I', @@TRANCOUNT
  UPDATE #foo SET i = @@TRANCOUNT;    -- @@TRANCOUNT = 2
COMMIT TRANSACTION;
    SELECT 'J', @@TRANCOUNT
SELECT i FROM #foo;                   -- Returns 2

    SELECT 'K', @@TRANCOUNT
UPDATE #foo SET i = @@TRANCOUNT;      -- @@TRANCOUNT should be 1
SELECT i FROM #foo;                   -- Returns 2?

DROP TABLE #foo;

SET NOCOUNT OFF;

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Goldner Grasshopper Points: 11 More actions · Answer 2

Thanks for the info. I am looking for the same info you shared. Keep suggesting such informative post.

myaarpmedicare.com

This reply was modified 3 years, 2 months ago by Goldner.
This reply was modified 3 years, 2 months ago by Goldner.