Transaction count is stored incorrectly in the table

Question

Transaction count is stored incorrectly in the table

yashwin g shetty 20

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

CosmogHong-MSFT 14,591 Reputation points Microsoft Vendor

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 20 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 87,171 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.

CosmogHong-MSFT 14,591 Reputation points Microsoft Vendor

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 20 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 87,171 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.

Answer 1

Erland Sommarskog 87,171

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 20 Reputation points

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

Can anyone explain the reason behind this ?

Transaction count is stored incorrectly in the table

0 additional answers

Activity