Transaction count is stored incorrectly in the table

yashwin g shetty 40

Since session is not under transaction control i am expecting to be 0. If we use Variable to store @@Tran count and then insert it works fine.

note: If transaction count is more than 2 then it stores count of active transactions + 1

LiHongMSFT-4306 26,706 Reputation points

2023-08-31T07:15:41.3933333+00:00

Hi @yashwin g shetty

Not sure what you want, could you post more explanation?
yashwin g shetty 40 Reputation points

2023-09-01T05:51:10.15+00:00

The transaction count is not will be wrong when we try to insert into table. If actual Active transactions are 0 or 1 then the count which it shows is 2. But if its more than 2 then we always get as Current Active transaction count plus 1. But if we assign the transaction count into a variable then variable will always return Correct value
Erland Sommarskog 110.3K Reputation points

2023-09-01T21:00:34.82+00:00

You are always getting the correct value, but because an INSERT is a transaction in itself, that changes the value of @@trancount.

Accepted answer

Erland Sommarskog 110.3K Reputation points

2023-08-30T21:49:48.1933333+00:00
You mean that you are doing something like:

INSERT tbl (val) VALUES(@@trancount)

Since the INSERT statement defines a transaction even if there is no user transaction, the expected value for @@trancount is 1, but if memory serves you get two for some reason.
Please sign in to rate this answer.
yashwin g shetty 40 Reputation points

2023-09-01T05:52:20.58+00:00

Can anyone explain the reason behind this ?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Transaction count is stored incorrectly in the table

0 additional answers

Your answer