Transaction count is stored incorrectly in the table

Question

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 31,576 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 122K Reputation points MVP Volunteer Moderator

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

0 additional answers

Your answer

LiHongMSFT-4306 31,576 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 122K Reputation points MVP Volunteer Moderator

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.

Answer 1

Erland Sommarskog 122K MVP Volunteer Moderator

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.

Yashwin G Shetty 40 Reputation points

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

Can anyone explain the reason behind this ?

Share via

Transaction count is stored incorrectly in the table

0 additional answers

Your answer