I have outlined a set of queries and scenarios that show the workings of XACT_ABORT, TRANSACTIONS and TRY/CATCH.
The following has been tested on Compatibility Level = 140
Each scenario will perform the same basic tasks and throw the same error so it will make it much easier to see what is happening and why.
Run all statements in each scenario in a single batch.
Setup
Before going into the scenarios let run through some set up. Create a very simple table which is going to hold three values for every test. This allows us to easily isolate and validate results as we progress.
CREATE TABLE dbo.test_scenarios (
test_id INT NOT NULL
,delete_id INT NOT NULL
);
INSERT INTO dbo.test_scenarios (test_id, delete_id)
SELECT t.n AS test_id
,d.n AS delete_id
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS t(n) -- The Test IDs
CROSS JOIN
(VALUES (0),(1),(2)) AS d(n); -- The Delete IDs
-- Verify
SELECT * FROM dbo.test_scenarios ORDER BY test_id, delete_id;
GO
Scenario 1
XACT_ABORT OFF
NO EXPLICIT TRANSACTION
NO TRY CATCH
Therefore...
Each delete statement runs in an autocommit transaction.
Any errors or failures have no impact on previous or following statements.
DECLARE @test_id INT = 1;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT OFF;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
1 0
1 1
1 2
After
test_id delete_id
1 1
*/
When the 1/0 error is hit, it is the only statement running in the autocommit transaction therefore only that statement is rolled back and everything else continues.
The final select statement executes when the batch is executed.
This may lead to inconsistent results and data quality issues.
Scenario 2
XACT_ABORT OFF
EXPLICIT TRANSACTION
NO TRY CATCH
Each delete statement runs within an explicit transaction.
Any errors or failures have no impact on previous or following statements.
The transaction here will simply define what will be committed but there is no logic to check what should happen if any of the statements fail.
The salient difference here between this and scenario 1 is the timing of the commits.
DECLARE @test_id INT = 2;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT OFF;
BEGIN TRANSACTION;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
COMMIT TRANSACTION;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
2 0
2 1
2 2
After
test_id delete_id
2 1
*/
When the 1/0 error is hit that particular statement is rolled back but as there is no logic to handle errors the rest of the statements proceed and the commit then commits the successful statements (0 and 2).
This is one of the riskiest applications of explicit transactions where failure/errors are not handled somehow and the end user expects the transaction to be rolled back in its entirety.
This can lead to inconsistent results and data quality issues.
Scenario 3
XACT_ABORT ON
EXPLICIT TRANSACTION
NO TRY CATCH
Each delete statement runs within an explicit transaction.
Any errors or failures within the transaction are now handled by the xact_abort.
DECLARE @test_id INT = 3;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
COMMIT TRANSACTION;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
3 0
3 1
3 2
After - The final select statement is not issued as part of the batch. Validation of the end state must be run separately after the first execution of the batch
*/
When the 1/0 error is hit the explicit transaction is rolled back and the batch terminates immediately. BATCH_ABORT signal is intercepted. Therefore no statements after the one that resulted in an error are executed.
In order to see the effect of the batch this must be re-run...
DECLARE @test_id INT = 3;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
After - The final select statement is not issued as part of the batch. Validation of the end state must be run separately after the first execution of the batch
test_id delete_id
3 0
3 1
3 2
*/
Depending on your use case this may be safe enough. The downside is that while the data remains consistent there any subsequent error handling/logging/tidy up are deferred to the client.
This is also risky code if there is a possibility that it is being run within the context of an explicit transaction that has been defined previous to the code being called.
I would trust this for an adhoc script but not in production code being called from other applications or clients. But even though I trust it, I would still never use it in this form. (opinion!)
Scenario 4
XACT_ABORT ON
NO EXPLICIT TRANSACTION
NO TRY CATCH
Each delete statement runs in an autocommit transaction.
Any errors or failures have no impact on previous statements but do impact following statements.
This can lead to inconsistent results and data quality issues.
DECLARE @test_id INT = 4;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
4 0
4 1
4 2
After - The final select statement is not issued as part of the batch. Validation of the end state must be run separately after the first execution of the batch
*/
When the 1/0 error is hit
1. It is the only statement running in the autocommit transaction therefore only that statement is rolled back.
2. However, it is not the only statement within the batch.
3. The batch itself is terminated so that and any following statements are never executed.
DECLARE @test_id INT = 4;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
After
4 1
4 2
*/
This is another very dangerous usage of XACT_ABORT if the true behaviour of how it operates is unknown. There is no immediate feedback available from the batch as to what has occurred.
Scenario 5
XACT_ABORT OFF
NO EXPLICIT TRANSACTION
TRY CATCH
Here we finally start introducing TRY/CATCH. Lets see what this brings to the table.
Each delete statement runs in an autocommit transaction within a try block.
Any errors or failures have no impact on previous statements but do impact following statements.
This can lead to inconsistent results and data quality issues.
DECLARE @test_id INT = 5;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT OFF;
BEGIN TRY
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
END TRY
BEGIN CATCH
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
5 0
5 1
5 2
After
5 1
5 2
Prints...
CATCH
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=0
*/
When the 1/0 error is hit
1. It is the only statement running in the autocommit transaction therefore only that statement is rolled back.
2. However, it is not the only statement within the try block.
3. Controls jumps to the catch block so any remaining statements within the try block, following the problematic code, are not executed.
This is another dangerous usage of TRY/CATCH if one assumes that the TRY begins an explicit transaction.
Scenario 6
XACT_ABORT ON
NO EXPLICIT TRANSACTION
TRY CATCH
The difference here is that we now have xact_abort on and one may expect a different result to scenario 5.
Each delete statement runs in an autocommit transaction within a try block.
Any errors or failures have no impact on previous statements but do impact following statements.
This can lead to inconsistent results and data quality issues.
DECLARE @test_id INT = 6;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
BEGIN TRY
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
END TRY
BEGIN CATCH
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
6 0
6 1
6 2
After
6 1
6 2
Prints...
CATCH
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=0
*/
When the 1/0 error is hit
1. It is the only statement running in the autocommit transaction therefore only that statement is rolled back.
2. However, it is not the only statement within the try block.
3. Controls jumps to the catch block so any remaining statements within the try block, following the problematic code, are not executed.
The CATCH block now intercepts the BATCH_ABORT signal so that any statements following the CATCH block will be executed. This explains how the final select statement is executed despite being part of the same batch.
This is another dangerous usage of TRY/CATCH if one assumes that the TRY begins an explicit transaction OR xact_abort will somehow provide some safety.
Scenario 7
XACT_ABORT OFF
EXPLICIT TRANSACTION
TRY CATCH
Each delete statement runs in an explicit transaction within a TRY/CATCH.
Any errors or failures impact any statements covered by the transaction.
This safely covers the error thrown from within the logic of the statements.
DECLARE @test_id INT = 7;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
ROLLBACK TRANSACTION;
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
7 0
7 1
7 2
After
test_id delete_id
7 0
7 1
7 2
Prints...
CATCH
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=1
*/
When the 1/0 error is hit:
1. Control jumps to the CATCH block.
2. The transaction is explicitly rolled back. (Note that the XACT_STATE = 1 so the transaction is in a state where a COMMIT could be issued.)
3. Execution CONTINUES after the END CATCH (The batch survives as BATCH_ABORT is intercepted).
This is safe and good use of TRY/CATCH and transactions, however it does not catch all errors that may impact the transaction behaviour. I.e. Client Timeouts.
It is also not ideal if the code is called where an explicit user transaction has already been started.
Scenario 8
XACT_ABORT ON
EXPLICIT TRANSACTION
TRY CATCH
Each statement runs in an explicit transaction within a try/catch.
XACT_ABORT ON ensures that severe errors (that bypass CATCH) still force a rollback.
TRY/CATCH allows us to gracefully handle logic errors and log them.
DECLARE @test_id INT = 8;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'CATCH'
PRINT 'The "Batch Abort" signal is now cleared.';
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
ROLLBACK TRANSACTION;
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
8 0
8 1
8 2
After
test_id delete_id
8 0
8 1
8 2
Prints...
CATCH
The "Batch Abort" signal is now cleared.
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=-1
*/
When the 1/0 error is hit:
1. The transaction is marked "Uncommittable" (XACT_STATE = -1).
2. Control jumps to the CATCH block.
3. The transaction is explicitly rolled back.
4. Execution CONTINUES after the END CATCH (The batch survives as BATCH_ABORT has been intercepted by CATCH).
NOTE ON TIMEOUTS:
If a Client Timeout occurs, the CATCH block is SKIPPED.
However, XACT_ABORT ON guarantees the transaction is still rolled back by the server.
This is the format that I would suggest using for adhoc scripts. I still think this is not safe code to be used within stored procedures or application code anywhere (opinion!)
Scenario 9
XACT_ABORT ON
EXPLICIT TRANSACTION
NESTED EXPLICIT TRANSACTION
NESTED TRY CATCH
TRY CATCH
Some serious caveats with the terminology being used here. There is no such thing as a nested transaction but there are nested BEGIN TRANSACTION statements. Different topic and not the focus here where we are mainly looking at xact_abort.
Each statement runs in an explicit transaction within a try/catch.
XACT_ABORT ON ensures that severe errors (that bypass CATCH) still force a rollback.
TRY/CATCH allows us to gracefully handle logic errors and log them.
DECLARE @test_id INT = 9;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION --@@TRANCOUNT = 1
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
BEGIN TRY
BEGIN TRANSACTION --@@TRANCOUNT = 2
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'INNER';
PRINT 'The "Batch Abort" signal is now cleared.';
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
ROLLBACK TRANSACTION; --@@TRANCOUNT = 0
END CATCH
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2; --Batch Abort cleared so this can proceed in autocommit.
COMMIT TRANSACTION; --Fail! @@TRANCOUNT already at 0
END TRY
BEGIN CATCH
PRINT 'OUTER';
PRINT 'The "Batch Abort" signal is now cleared.';
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
ROLLBACK TRANSACTION;
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
9 0
9 1
9 2
After - The final select statement is not issued as part of the batch. Validation of the end state must be run separately after the first execution of the batch
Prints...
INNER
The "Batch Abort" signal is now cleared.
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=-1
OUTER
The "Batch Abort" signal is now cleared.
ERROR_MESSAGE()=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
XACT_STATE()=0
Msg 3903, Level 16, State 1, Line 487
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
*/
When the 1/0 error is hit:
1. The transaction is marked "Uncommittable" (XACT_STATE = -1).
2. Control jumps to the INNER CATCH block.
3. BATCH_ABORT is intercepted
4. The transaction is explicitly rolled back.
a. TRANCOUNT goes from 2 -> 0
5. Execution CONTINUES after the END CATCH.
6. The next statement in the batch is DELETE for delete_id = 2
7. The delete succeeds as this is performed within an autocommit transaction and the previous BATCH_ABORT had been intercepted and reset.
8. The (OUTER) COMMIT statement fails as the explicit transaction has already been rolled back.
9. Controls jumps to the OUTER CATCH block where the message indicates no begin transaction found.
10. There is no explicit transaction in operation, the XACT_STATE is 0
11. The ROLLBACK now throws an ERROR
12. BATCH_ABORT signal re-issued by XACT_ABORT
13. The batch is terminated and the final select statement is not executed
DECLARE @test_id INT = 9;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
After
9 0
9 1
Now that we start delving into nested BEGIN TRANSACTION statements and even with TRY/CATCH and XACT_ABORT we start to see that results can be unexpected.
Scenario 10
XACT_ABORT ON
EXPLICIT TRANSACTION
NESTED EXPLICIT TRANSACTION (Conditional)
NESTED TRY CATCH
TRY CATCH
The main difference between this and the previous test is that we are checking to see if an explicit transaction has already been defined. If yes we will always leave the transaction operations to be handled where the transaction has been started.
This is why you should always join existing transactions in SQL server.
This is the safest way to construct stored procedures that handle transactions in SQL Server
Each statement runs in an explicit transaction within a try/catch.
XACT_ABORT ON ensures that severe errors (that bypass CATCH) still force a rollback.
TRY/CATCH allows us to gracefully handle logic errors and log them.
DECLARE @test_id INT = 10;
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
SET XACT_ABORT ON;
BEGIN TRY
DECLARE @tc INT = @@TRANCOUNT;
IF @tc = 0 BEGIN TRANSACTION;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 0;
BEGIN TRY
DECLARE @tc1 INT = @@TRANCOUNT;
IF @tc1 = 0 BEGIN TRANSACTION;
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 1/0;
IF @tc1 = 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'INNER';
PRINT 'The "Batch Abort" signal is now cleared.';
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
IF @tc1 = 0
BEGIN
PRINT 'INNER - ROLLING BACK';
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
PRINT 'INNER - THROW';
THROW;
END
END CATCH
DELETE FROM dbo.test_scenarios WHERE test_id = @test_id AND delete_id = 2;
IF @tc = 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT 'OUTER';
PRINT 'The "Batch Abort" signal is now cleared.';
PRINT 'ERROR_MESSAGE()='+ERROR_MESSAGE();
PRINT 'XACT_STATE()='+CAST(XACT_STATE() AS VARCHAR);
IF @tc = 0
BEGIN
PRINT 'OUTER - ROLLING BACK';
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
PRINT 'OUTER - THROW';
THROW;
END
END CATCH
SELECT test_id, delete_id FROM dbo.test_scenarios WHERE test_id = @test_id;
/*
Before
test_id delete_id
10 0
10 1
10 2
After
test_id delete_id
10 0
10 1
10 2
Prints...
INNER
The "Batch Abort" signal is now cleared.
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=-1
INNER - THROW
OUTER
The "Batch Abort" signal is now cleared.
ERROR_MESSAGE()=Divide by zero error encountered.
XACT_STATE()=-1
OUTER - ROLLING BACK
*/
When the 1/0 error is hit:
1. The transaction is marked "Uncommittable" (XACT_STATE = -1).
2. Control jumps to the INNER CATCH block.
3. The transaction is not rolled back.
a. The nested try/catch transaction 'joined' the exiting one and so does not roll the tranasction back
4. Execution CONTINUES after the END CATCH (The batch survives).
a. Controls immediately jumps to the OUTER CATCH block
6. XACT_ABORT treats this like a second batch within the same transaction
7. The transaction is already marked as uncommittable
8. As the OUTER created the initial transaction, it rolls it back.
a. If the OUTER here were called from another statement or procedure that had initiated an explicit transaction then it would operate the same way as the INNER
9. BATCH_ABORT has been intercepted by the outer CATCH block so the select statement proceeds.
This would be my recommendation for why I would recommend setting XACT_ABORT ON, checking to see if explicit transactions have already been started, and using TRY/CATCH to log any errors or perform tidy up.
Conclusion
If you're not sure then write a test and see if your code performs as you would expect. I have no doubt I have some typos somewhere in this post so please make sure you verify everything yourself before implementing anything in a production environment.
