October 13, 2006 at 4:44 am
I am continue to read MS's official course for SQL 2005. Now they talk about structured exception-handling.
In the following example they sets XACT_ABORT ON and tests the transaction state within the CATCH block.
SET XACT_ABORT ON --<<<<important!
BEGIN TRY
BEGIN TRAN
...
sql_statement_block
...
COMMIT TRAN
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1 -- uncommittable
ROLLBACK TRAN
ELSE IF (XACT_STATE()) = 1 -- committable
COMMIT TRAN <<< if XACT_ABORT=ON can we go here??
END CATCH
I think when XACT_ABORT is ON we just CAN'T go to sting "COMMIT TRAN" in the catch block. In other words - if XACT_ABORT is ON and we hit catch block - XACT_STATE may be -1 and only -1, i.e. our transaction for certain in uncommittable(and ONLY in uncommittable!) state. If XACT_ABORT would be OFF above example would be absolutely correct - in this case transaction may be in committable as well as in uncommittable state.
Am I mistaken anywhere?
October 13, 2006 at 11:56 am
It's a good practice always to check XACT_STATE() becuase you don't need to care it's in a commitable tran or not, or even its in a tran at all when the exception happens. The reason is your SP may be called by others and XACT_ABORT may be set there even u don't set it in your SP.
In your sample, yes, the XACT_STATE can only be -1.
October 13, 2006 at 5:34 pm
OK, thanks for detailed answer! I catch your thought concerning "best practice"... Sound sensibly, indeed! Thanks once more for this hint.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply