January 25, 2021 at 11:06 pm
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
January 26, 2021 at 3:17 pm
@@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.
February 1, 2021 at 9:14 am
Thanks for the info. I am looking for the same info you shared. Keep suggesting such informative post.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply