Archive

Posts Tagged ‘XACT_ABORT’

SQL Server’s weird behavior in error handling with transactions

December 6, 2011 2 comments

In my previous post I mentioned that SQL statements inside the BEGIN & COMMIT TRANSACTION block do not auto rollback the batch if an error occurs, and SET XACT_ABORT ON is required for auto rollbacking the transaction batch in such cases.

As I mentioned earlier in my previous post that by default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

But there is an exception for some errors, like for error level 16, example: Datatype Conversion error, the transaction batch is automatically rolled back.

–> Let’s check this with an example below:

-- Create a table and insert test some records
CREATE TABLE X (NBR INT PRIMARY KEY)

INSERT INTO X VALUES(1)
INSERT INTO X VALUES(2)

-- Tran t1 : PK violation error
BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SELECT * FROM X
-- Results 4 records inserted above, the entire transaction batch was not rolled back, but only the 2nd INSERT statement.

-- To automatically rollback above transaction batch we will set XACT_ABORT to ON:
SET XACT_ABORT ON

BEGIN TRANSACTION T1
	INSERT INTO X VALUES(3)
	INSERT INTO X VALUES(3) -- Msg 2627, Level 14, State 1, Line 9, Violation of PRIMARY KEY constraint...
	INSERT INTO X VALUES(4)
COMMIT TRANSACTION T1

SET XACT_ABORT OFF

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

--// But for some types of errors setting the XACT_ABORT to ON is not required. Like for Datatype Conversion errors (level = 16). Let's check this:

-- Tran t2: Datatype conversion error
BEGIN TRANSACTION T2
	INSERT INTO X VALUES(5)
	INSERT INTO X VALUES('a') -- Msg 245, Level 16, State 1, Line 19, Conversion failed...
	INSERT INTO X VALUES(6)
COMMIT TRANSACTION T2

SELECT * FROM X
-- No results, Entire transaction batch is rolled back.

-- Final Cleanup
DROP TABLE X

Erland Sommarskog in his tech-site lists and distinguish such error types: http://www.sommarskog.se/error-handling-I.html#statementbatch

I also discussed this topic in MSDN TSQL forum & here is the link.

Advertisement

XACT_ABORT with TRANSACTIONS in SQL Server

December 1, 2011 2 comments

SQL Server by default does not ROLLBACK a Transaction if there is error anywhere in the code inside a BEGIN TRANSACTION & COMMIT TRANSACTION block. It only skips the statement that causes the error and moves ahead.

To allow a transaction to complete entirely or fail entirely you can use the TRY-CATCH feature with ROLLBACK statement or the SET XACT_ABORT statement.

By default the SET XACT_ABORT is OFF. There ar very few cases where the T-SQL statement that raised exception are rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

But When SET XACT_ABORT is ON, if a T-SQL statement raises an exception, then the entire transaction is terminated and rolled back.

–> Here let’s check how we can use XACT_ABORT with an example:


-- Create a test tabel and insert some records:
CREATE TABLE Tab (x INT NOT NULL PRIMARY KEY)

INSERT INTO Tab VALUES (1)
INSERT INTO Tab VALUES (2)
INSERT INTO Tab VALUES (3)
GO

-- =================================
-- Check with XACT_ABORT OFF option:
-- =================================
SET XACT_ABORT OFF; -- By default it is OFF

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (4)
	INSERT INTO Tab VALUES (4) -- Statement fails but transaction is committed.
	INSERT INTO Tab VALUES (5)
COMMIT TRANSACTION
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (4).
The statement has been terminated.

(1 row(s) affected)
*/

SELECT * FROM Tab
-- List records: 1,2,3,4,5
GO

-- ================================
-- Check with XACT_ABORT ON option:
-- ================================
SET XACT_ABORT ON

BEGIN TRANSACTION
	INSERT INTO Tab VALUES (6) -- Inserts 6, but the transaction is rollbacked in next step.
	INSERT INTO Tab VALUES (6) -- Statement fails and rollbacks the entire transaction.
	INSERT INTO Tab VALUES (7) -- Statement skipped as transaction is rollbacked at previous statement.
COMMIT TRANSACTION

SET XACT_ABORT OFF
GO
/*
-- On message pane:

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__Tab__3BD019E56991A7CB'. Cannot insert duplicate key in object 'dbo.Tab'. The duplicate key value is (6).
*/

SELECT * FROM Tab
-- Still list records: 1,2,3,4,5
GO

-- Final cleanup
DROP TABLE Tab
GO

We can also use TRY-CATCH constructs that helps in handling transaction and can be used instead of XACT_ABORT statement. Check here how we can use them.