Note:  this article was originally published to the Wharton Computing Developer’s Center

Recently I encountered a SQL Server error-handling trap that caused a transaction not to roll back as expected. At the risk of sounding like an amateur, here’s what happened. The code was issuing two insert statements that were grouped into a single transaction:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

BEGIN TRANSACTION
 
INSERT INTO dbo.Table1 (SOME COLUMNS)
VALUES (SOME VALUES)
 
SELECT @error = @@error, @rowcount = @@rowcount
 
IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the first insert!', 16, 1) RETURN END
 
[DO STUFF]
 
INSERT INTO dbo.Table2 (SOME COLUMNS)
VALUES (SOME VALUES)
 
SELECT @error = @@error, @rowcount = @@rowcount
 
IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the second insert!', 16, 1) RETURN END
 
COMMIT TRANSACTION

The above error-handling works most of the time. However, if there is a run-time T-SQL error (for example, a missing object or a divide by 0 error), the offending statement fails but the rest of the transaction executes. In my case, the second insert failed because Table2 had been renamed. The subsequent @error check was never invoked, so the first insert wasn’t rolled back.

This behavior can be overridden by setting SET XACT_ABORT to ON, which specifies that the current transaction will automatically roll back in the event of a T-SQL run-time error.

External Link: (SET XACT_ABORT)