SET option XACT_ABORT and SQL Server Transaction

SET option XACT_ABORT and SQL Server Transaction

Error is something which nobody wants to have in their software/code though, as a developer, we used to see errors so many times no matter whether it is development environment or live environment.

If we talk specifically about SQL Server, we used to come across following types of errors in SQL Server.

1.) Statement Terminate:

2.) Scope Terminate:

3.) Batch Terminate:

4.) Connection Terminate:

People tend to use “TRANSACTION” when they are using DML command, especially more then one DML operation based on each other. This is completely fine. I, too, tend to use it but we never like to hang the transaction as it is. We may want to ROLLBACK all DML statement if any run-time error come across in-between execution of any DML statement given in one batch.

We may use “ROLLBACK” statement and TRY…CATCH to manage our transaction even sometime bigger severity runtime error occurs and your code don’t follow the CATCH block either and hence ROLLBACK TRANSACTION never executes so If we have XACT_ABORT option set to ON, we can be sure that if run-time error occurs, we will have all DML transaction rolled back.

Let us understand this by following example:

Creating sample temporary table with some sample data:

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#TestingTHROWCommand’) IS NOT NULL
DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
,OvertimeAmount INT
)

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO
[/sourcecode]

Now we will execute one TSQL code block which will update our temp table twice. First UPDATE statement is fine but second will generate the error. Keep in mind that we neither have ROLLBACK nor we have XACT_ABORT ON in following block:

[sourcecode language=”sql”]
BEGIN TRY
BEGIN TRANSACTION

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=1

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=2

COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
END CATCH
GO
[/sourcecode]

Can you imagine what would happens to both the UPDATE? Let us see what happened to both UPDATE by executing simple SELECT statement on temp table.

[sourcecode language=”sql”]
SELECT * FROM #TestingTHROWCommand
GO
[/sourcecode]

Here is the output I have received for my SELECT statement.

WithoutXACT_Abort

You can see that there wasn’t any error in first UPDATE so that value is updated and second UPDATE had an error and hence it is not updated obviously. We may want that if error occurs, neither of the records should be updated. We might use CATCH block for ROLLBACK TRANSACTION but what, if error was with high severity and it doesn’t falls under CATCH statement? So it is better to be in safe side and have XACT_ABORT with ON status in every code block wherever we are using DML statements with TRANSACTION.

Let us insert all original values in our temp table.

[sourcecode language=”sql”]
TRUNCATE TABLE #TestingTHROWCommand
GO

INSERT INTO #TestingTHROWCommand
SELECT ‘Ritesh Shah’,15 UNION ALL
SELECT ‘Teerth Shah’,0 UNION ALL
SELECT ‘Rajan Jain’,9
GO
[/sourcecode]

Now we can execute same UPDATE statements with XACT_ABORT.

[sourcecode language=”sql”]
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=1

UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
WHERE ID=2

COMMIT TRANSACTION
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
END CATCH
GO
[/sourcecode]

As soon as I ran above code block, I greeted with following error:

WithXACT_Abort

[sourcecode language=”sql”]
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
[/sourcecode]

Can you imagine what would happens to both the UPDATE? Let us see what happened to both UPDATE by executing simple SELECT statement on temp table.Here is the output I have received for my SELECT statement.

[sourcecode language=”sql”]
SELECT * FROM #TestingTHROWCommand
GO
[/sourcecode]

WithXACT_AbortSELECT

You can see that none of the values are updated. XACT_ABORT did ROLLBACK by its own even without explicitly written ROLLBACK statements. I am not saying that don’t put ROLLBACK statements. It MUST be there but XACT_ABORT can be useful in certain situation we might have missed to handle.

If you are interested to read error handling article I have written previously, have a look at list below:

Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)
Importance of transaction in SQL Server (Click Here)
Capture and log error of TSQL code in SQL Server 2012 (Click Here)
SET option XACT_ABORT and SQL Server Transaction (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

2 thoughts on “SET option XACT_ABORT and SQL Server Transaction”

Comments are closed.