September 27, 2018 at 3:34 am
Hello
Using the example provided by BOL for XACT_STATE
With stripped out comments and print statements below
I'm struggling to see how XACT_STATE() is used in TRY...CATCH
As a result, I'm struggling to understand exactly why XACT_STATE is used
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.Product WHERE ProductID = 980;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1
COMMIT TRANSACTION;
END CATCH;
GO
Surely, if the transaction fails due to FK issues i.e. the transaction would be uncommitable, it would jump to the CATCH part
If so, why is XACT_STATE even relevent
Surely, in this instance, it will always be -1 so you will always ROLLBACK
Appreciate, it could be 0 if the statement is non-critical e.g. SELECT ...
In this case though, it's been specifically coded as a delete
Suppose, I'm really trying to get a good example of exactly how/where/why XACT_STATE() is used
Thanks
- Damian
September 27, 2018 at 8:16 am
DamianC - Thursday, September 27, 2018 3:34 AMHelloUsing the example provided by BOL for XACT_STATE
With stripped out comments and print statements belowI'm struggling to see how XACT_STATE() is used in TRY...CATCH
As a result, I'm struggling to understand exactly why XACT_STATE is used
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Production.Product WHERE ProductID = 980;COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1
COMMIT TRANSACTION;
END CATCH;
GOSurely, if the transaction fails due to FK issues i.e. the transaction would be uncommitable, it would jump to the CATCH part
If so, why is XACT_STATE even relevent
Surely, in this instance, it will always be -1 so you will always ROLLBACKAppreciate, it could be 0 if the statement is non-critical e.g. SELECT ...
In this case though, it's been specifically coded as a deleteSuppose, I'm really trying to get a good example of exactly how/where/why XACT_STATE() is used
Thanks
Some of it depends on what you are using for xact_abort and if you are managing the rollback of a single statement or an entire transaction. There is a good explanation and example in the following article:
On Transactions, errors and rollbacks
Sue
September 27, 2018 at 12:24 pm
You use XACT_STATE() to:
(1) avoid attempting a ROLLBACK or COMMIT when a trans is not active
(2) to know whether to do a ROLLBACK or COMMIT in certain cases (if it's -1, you can't COMMIT the trans, even if you want to).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 3, 2018 at 3:05 am
Thanks for the feedback
Think I get this now
Think some of the examples where a record is deleted then an audit of the deletion are better
e.g. XACT_STATE example
If, within a transaction, we want a deletion to occur even if the auditting fails
SET XACT_ABORT ON first
So, if the delete succeeds BUT the audit insert fails, it jumps to CATCH (as an error as occured)
Here XACT_STATE() will equal 1 as we have a commitable active user transaction (the delete)
So, this is committed and @@TRANCOUNT is reset to 0
If both failed, XACT_STATE would be -1 as both are uncommitable
So, this is rolled back and @@TRANCOUNT is reset to 0
- Damian
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply