@@TRANCOUNT

Question

@@TRANCOUNT

Jerry Day

SSC-Addicted

Points: 454
More actions
June 21, 2011 at 12:15 pm

#242927

Run the below query. When the value of @@TRANCOUNT never exceeds 1, why is 2 being written to the table?
SELECT @@TRANCOUNT
BEGIN TRAN
CREATE TABLE #temp (col1 int);
SELECT @@TRANCOUNT
INSERT INTO #temp VALUES (@@TRANCOUNT);
SELECT @@TRANCOUNT
SELECT * FROM #temp
ROLLBACK TRAN
Jerry D

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Because the insert starts a transaction and automatically commits it as soon as its finished.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jerry Day SSC-Addicted Points: 454 More actions · Answer 2

@@TRANCOUNT = 0 before and after the insert. So why is 2 being written to the table?

CREATE TABLE #temp (col1 int);

SELECT @@TRANCOUNT

BEGIN TRAN

ROLLBACK TRAN

SELECT @@TRANCOUNT

INSERT INTO #temp VALUES (@@TRANCOUNT);

SELECT @@TRANCOUNT

SELECT * FROM #temp

DROP TABLE #temp

Jerry D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said

GilaMonster (6/21/2011)
Because the insert starts a transaction and automatically commits it as soon as its finished.

So you started one (BEGIN TRANSACTION), the insert started one that it autocommitted. Hence 2.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 4

Did you notice that the explicit transaction immediately gets rolled back? I cleared some irrelevant stuff out of the code and pulled some extra info into the temp table:

CREATE TABLE #temp (col1 int, transaction_id int, tran_name varchar(max));

--Confirm no active transactions

SELECT @@TRANCOUNT [BetweenTableCreateAndInsert]

--Insert into temp table

INSERT INTO #temp select @@TRANCOUNT, tst.transaction_id, tat.name from sys.dm_tran_session_transactions tst

inner join sys.dm_tran_active_transactions tat on tst.transaction_id = tat.transaction_id

where tst.session_id = @@SPID

--Confirm no active transactions now

SELECT @@TRANCOUNT [AfterInsert]

--Cleanup

SELECT * FROM #temp

DROP TABLE #temp

That query should return @@trancount equal to the number of rows returned but doesn't. And even though there isn't any explicit begin tran it still returns 2 for @@trancount but only info on one transaction. I also declared an explicit transaction around the insert with no real change in the output. I also took a quick try at pulling info from sys.dm_tran_current_transaction but that didn't seem to give anything additional.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

I did some more poking but haven't been able to find an explanation yet. The following post mentions the behavior, and duplicates it slightly differently, but doesn't address why it's happening.

SQL Teaser Count That Trancount