The XACT_ABORT option: What it is and what it is used for, all explained in a simple way!

Hi Guys,
Welcome back!

After a dip in the maze of the logic of the SQL Server optimizer, today we talk about the command XACT_ABORT.
We will see how important this command is when we deal with transactions.

Do not worry, I will try to explain in the simplest way possible!

XACT_ABORT

The XACT_ABORT is an option which can have only two possible values: ON and OFF.
 

You can simply set it's value with the T-SQL command:

       
SET XACT_ABORT ON      
 

OR

       
SET XACT_ABORT OFF
 

If XACT_ABORT is set to ON then if a T-SQL command throws an error then the transaction will be rollbacked.

Suppose of having a trigger triggered by an update on the table A
If an istruction inside the trigger fail then also the update on the table will be rollbacked.

Yes, this is the concept.

Let's have this example with XACT_ABORT set to ON

Suppose we have a TABLE_A table with the field CAMPO1 non nullable with a 0 value.

       
SET XACT_ABORT ON
 
BEGIN TRAN
UPDATE TABLE_A SET CAMPO1 = 1 WHERE ID = 1  ($)
COMMIT

TRIGGER ON UPDATE ON THE TABLE_A
BEGIN
   UPDATE TABLE_A SET CAMPO1 = NULL WHERE ID = 1  (*)
END

The update (*) fail because CAMPO1 does not accept the null.
The transation is rolled back and so the update ($) in canceled.

Q: What is the value of the column CAMPO1?
A: The value is 0 because the update ($) is never happened!

And if XACT_ABORT is set to OFF?

Well, it depends!

In some cases only the T-SQL statement that raised the error is rolled back but not the entire transaction.
The behavior depend from the severity of the error.

Let's try..

SET XACT_ABORT OFF
 
BEGIN TRAN
UPDATE TABLE_A SET CAMPO1 = 1 WHERE ID = 1  ($)
COMMIT

TRIGGER IN UPDATE ON THE TABLE_A
BEGIN
    UPDATE TABLE_A SET CAMPO1 = NULL WHERE ID = 1  (*)
END

As before the update (*) fail because CAMPO1 does not accept the null.

Only the T-SQL update is rolled back.
The update ($) in commited

What is the value of the column CAMPO1?
The value is 0 because the update ($) is never happened!

Q: What is the value of the column CAMPO1?
A: The value is 1 because the update ($) is committed


It's not really easy? Yes i suppose!

Just remeber that the THROW statement honors SET XACT_ABORT.
While then RAISERROR command does not.
So use THROW instead of RAISERROR.


That's all for today.
I wish you an amazing saturday.

See you soon,
Luca


Luca Biondi @ SQLServerPerformance blog 2020!

Previous post: Inside the SQL Server Query Optimizer - part 6 What is the memo?