XACT_ABORT & XACT_STATE

Giao dịch và Xử lý lỗi trong SQL Server

Miriam Antona

Software Engineer

XACT_ABORT

Chỉ định liệu giao dịch hiện tại có tự động rollback khi xảy ra lỗi hay không.

SET XACT_ABORT { ON | OFF }

SET XACT_ABORT OFF

Thiết lập mặc định
Nếu có lỗi: Có thể còn giao dịch mở

SET XACT_ABORT ON

Nếu có lỗi: Rollback giao dịch và dừng thực thi

XACT_ABORT - ví dụ

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 - ví dụ

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 VỚI 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 với 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 -> không có giao dịch mở
1 -> giao dịch mở và có thể commit
-1 -> giao dịch mở nhưng không thể commit (doomed)
- không thể commit
- không thể rollback về savepoint
- có thể rollback toàn bộ giao dịch
- không thể thay đổi/có thể đọc dữ liệu

XACT_STATE - mở và có thể 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 - mở và có thể commit

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

XACT_STATE - mở và không thể 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 - mở và không thể commit (doomed)

SELECT * FROM customers WHERE first_name = 'Mark';

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

Ayo berlatih!

Giao dịch và Xử lý lỗi trong SQL Server