June 26, 2019 at 12:33 pm
I have come across some stored procedures in our company database that follow this format:
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
-- Do some stuff here
-- Do some more stuff here
COMMIT
END TRY
BEGIN CATCH
ROLLBACK;
THROW;
END CATCH
Am I correct in thinking that the CATCH code block will never run because of the XACT_ABORT setting?
June 26, 2019 at 1:35 pm
If the error is raised inside the BEGIN TRANSACTION/COMMIT block then I think there will still be a transaction open in the CATCH block. I don't use "XACT_ABORT ON" but I would still test the @@TRANCOUNT in the catch block before rollback:
BEGIN CATCH
WHILE @@TRANCOUNT > 0 BEGIN;
ROLLBACK;
END;
THROW;
END CATCH
You could check XACT_STATE() instead, which gives you more information on whether or not the transaction is committable.
June 26, 2019 at 1:57 pm
Jonathan is right. To answer your question directly, no it doesn't mean that. The CATCH block will be invoked because there was an error in the TRY block. The XACT_ABORT setting means your transaction may be automatically rolled back before the CATCH block starts - hence Jonathan's suggestion of testing the transaction state before attempting to roll back.
You can test this easily enough:
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
SELECT 1/0; -- will cause error
COMMIT;
PRINT 'Transaction committed';
END TRY
BEGIN CATCH
PRINT @@trancount;
PRINT 'Catch block invoked';
ROLLBACK;
THROW;
END CATCH
GO
BEGIN TRY
BEGIN TRAN
SELECT 0/1; -- will not cause error
COMMIT;
PRINT 'Transaction committed';
END TRY
BEGIN CATCH
PRINT @@trancount;
PRINT 'Catch block invoked';
ROLLBACK;
THROW;
END CATCH
John
June 26, 2019 at 2:00 pm
Awesome, thank you both for your help. I will add the additional code to check the transaction state.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply