XACT_ABORT & XACT_STATE

Transaksi dan Penanganan Kesalahan di SQL Server

Miriam Antona

Software Engineer

XACT_ABORT

Menentukan apakah transaksi saat ini akan otomatis di-rollback saat terjadi error.

SET XACT_ABORT { ON | OFF }

SET XACT_ABORT OFF

Pengaturan default
Jika terjadi error: Bisa ada transaksi terbuka

SET XACT_ABORT ON

Jika terjadi error: Me-rollback transaksi dan menghentikan eksekusi

XACT_ABORT - contoh

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 - contoh

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 DENGAN 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 dengan 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 -> tidak ada transaksi terbuka
1 -> transaksi terbuka dan bisa di-commit
-1 -> transaksi terbuka tapi tidak bisa di-commit (doomed)
- tidak bisa commit
- tidak bisa rollback ke savepoint
- bisa rollback seluruh transaksi
- tidak bisa mengubah/bisa membaca data

XACT_STATE - terbuka dan bisa di-commit

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 - terbuka dan bisa di-commit

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

XACT_STATE - terbuka tapi tidak bisa di-commit (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 - terbuka tapi tidak bisa di-commit (doomed)

SELECT * FROM customers WHERE first_name = 'Mark';

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

Ayo berlatih!

Transaksi dan Penanganan Kesalahan di SQL Server