XACT_ABORT & XACT_STATE

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

XACT_ABORT

Specifies whether the current transaction will be automatically rolled back when an error occurs.

SET XACT_ABORT { ON | OFF }

SET XACT_ABORT OFF

Default setting
If there is an error: There can be open transactions

SET XACT_ABORT ON

If there is an error: Rollbacks the transaction and aborts the execution

XACT_ABORT - examples

SET XACT_ABORT OFF; --Default setting

BEGIN TRAN;
    INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
    INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;

(1 row affected)
Msg. 2627, Level 14, State 1, Line 5
Violation of UNIQUE KEY 'unique_email'...

| customer_id | first_name | last_name | email                | phone     |
|-------------|------------|-----------|----------------------|-----------|
| 14          | Mark       | Davis     | [email protected]   | 555909090 |

XACT_ABORT - examples

SET XACT_ABORT ON;

BEGIN TRAN;
    INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
    INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
COMMIT TRAN;

Msg. 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY 'unique_email'...

SELECT * FROM customers WHERE first_name = 'Mark';

| customer_id | first_name | last_name | email                | phone     |
|-------------|------------|-----------|----------------------|-----------|

XACT_ABORT WITH RAISERROR

SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
    RAISERROR('Raising an error!', 16, 1);
    INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');
COMMIT TRAN;

Msg. 50000, Level 16, State 1, Line 5
Raising an error!

SELECT * FROM customers WHERE first_name IN ('Mark', 'Zack');

| customer_id | first_name | last_name | email                | phone     |
|-------------|------------|-----------|----------------------|-----------|
| 14          | Mark       | Davis     | [email protected]   | 555909090 |
| 15          | Zack       | Roberts   | [email protected] | 555919191 |

XACT_ABORT with THROW

SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
    THROW 55000, 'Raising an error!', 1;
    INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');
COMMIT TRAN;

(1 rows affected)
Msg. 50000, Level 16, State 1, Line 5
Raising an error!

SELECT * FROM customers WHERE first_name IN ('Mark', 'Zack');

| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|-------|-------|

XACT_STATE

XACT_STATE()

0 -> no open transaction
1 -> open and committable transaction
-1 -> open and uncommittable transaction (doomed transaction)
- can't commit
- can't rollback to a savepoint
- can rollback the full transaction
- can't make any changes/can read data

XACT_STATE - open and committable

SET XACT_ABORT OFF;
BEGIN TRY
    BEGIN TRAN;
        INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
        INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRAN;
    IF XACT_STATE() = 1
        COMMIT TRAN;
    SELECT ERROR_MESSAGE() AS error_message;
END CATCH

| error_message                               |
|---------------------------------------------|
| Violation of UNIQUE KEY 'unique_email'...   |

XACT_STATE - open and committable

| customer_id | first_name | last_name | email                | phone     |
|-------------|------------|-----------|----------------------|-----------|
| 14          | Mark       | Davis     | [email protected]   | 555909090 |

XACT_STATE - open and uncommittable (doomed)

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
        INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');
        INSERT INTO customers VALUES ('Dylan', 'Smith', '[email protected]', '555888999'); -- ERROR!
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
        ROLLBACK TRAN;
    IF XACT_STATE() = 1
        COMMIT TRAN;
    SELECT ERROR_MESSAGE() AS Error_message;
END CATCH

XACT_STATE - open and uncommittable (doomed)

SELECT * FROM customers WHERE first_name = 'Mark';

| customer_id | first_name | last_name | email                | phone     |
|-------------|------------|-----------|----------------------|-----------|

Let's practice!

Transactions and Error Handling in SQL Server