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)