Transactions Rolling Back by default when XACT_ABORT is off

Question

I have the following transaction

BEGIN TRAN
        DECLARE @TransactionAmount MONEY = 5.00
        UPDATE Account SET Balance -= @TransactionAmount WHERE AccountID = 1
        UPDATE Account SET Balance += @TransactionAmount WHERE AccountID = 'blah'
COMMIT TRAN

As there is a failure in the second UPDATE statement (AccountID is an int column) and the whole statement is wrapped in a TRAN block, the change in the first statement rolls back and the balance for AccountID 1 is not deducted.

As there is no ROLLBACK statement in the code above it seems that SQL server does the rollback automatically without the need for an explicit ROLLBACK statement

When I looked into it, it seems the automatic rollback behaviour is controlled by a setting called xact_abort.

I found the following script online which prints out the settings which are on

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

However, when run on my server, XACT_ABORT doesn't appear in the printed list so is not switched on.

My question is: what is the need for a ROLLBACK statement when it seems that SQL server is doing the rollback automatically?

Answer 1

If XACT_ABORT = OFF then it is very unpredictable if the transaction rolls back or not. SQL Server sometimes does not, sometimes it does and sometimes it even aborts the batch. (Yes, this does not make any sense.) Other possible outcomes include dooming the transaction or cutting the connection.

In your case you can reliably use TRY-CATCH to prevent a rollback and handle the exception.

I found it a good practice to not rely on error processing if possible. Instead, roll back the transaction. Also push error handling into the client if possible.

Answer 2

If you are using transactions it is advisable to set XACT_ABORT = ON at the start of your Stored Procedure. Erland Sommarskog has an excellent piece on error handling:

http://www.sommarskog.se/error_handling/Part1.html