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?