@@TRANCOUNT = 2?

Question

Post reply

@@TRANCOUNT = 2?

duckpond_wkb

SSC Enthusiast

Points: 112

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.

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

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

ScottPletcher SSC Guru Points: 101200 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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 4 years, 7 months ago by Goldner.
This reply was modified 4 years, 7 months ago by Goldner.