February 6, 2020 at 12:00 am
Comments posted to this topic are about the item XACT_STATE(), @@TRANCOUNT and ROLLBACK
February 6, 2020 at 9:07 am
oops.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2020 at 9:41 am
I disagree with the answers. The first one is correct- after the second insert it's transfered into the catch statement and the XACT_STATE = -1
April 4, 2020 at 10:41 am
I agree with Dotathinker, how can 2 and 3 be correct? As the entire transaction is under uncommittable state which is -1.
As per docs from Microsoft please note the below details( https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15)
XACT_STATE() = 1
The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
XACT_STATE() = -1
The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction.
April 27, 2020 at 1:51 am
You can add SELECT @@TRANCOUNT after the catch block and see whether the transaction is rolled back or not. For a detailed explanation, you can read the following article:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply