Home > SQL Tips > XACT_ABORT with TRANSACTIONS

XACT_ABORT with TRANSACTIONS


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.

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 416 other followers

%d bloggers like this: