XACT_ABORT & XACT_STATE
SQL Server에서의 트랜잭션과 오류 처리
Miriam Antona
Software Engineer
XACT_ABORT
오류 발생 시 현재 트랜잭션을 자동 롤백할지 지정합니다.
SET XACT_ABORT { ON | OFF }
SET XACT_ABORT OFF
- 기본 설정
- 오류 발생 시: 열린 트랜잭션이 남을 수 있음
SET XACT_ABORT ON
- 오류 발생 시: 트랜잭션 롤백 후 실행 중단
XACT_ABORT - 예시
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 - 예시
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과 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과 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 -> 열린 트랜잭션 없음
- 1 -> 열려 있고 커밋 가능
- -1 -> 열려 있으나 커밋 불가(무효 트랜잭션)
- 커밋 불가
- 세이브포인트로 롤백 불가
- 전체 트랜잭션 롤백 가능
- 변경 불가/읽기만 가능
XACT_STATE - 열려 있고 커밋 가능
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 - 열려 있고 커밋 가능
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
| 14 | Mark | Davis | [email protected] | 555909090 |
XACT_STATE - 열려 있으나 커밋 불가(무효)
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 - 열려 있으나 커밋 불가(무효)
SELECT * FROM customers WHERE first_name = 'Mark';
| customer_id | first_name | last_name | email | phone |
|-------------|------------|-----------|----------------------|-----------|
Ayo berlatih!
SQL Server에서의 트랜잭션과 오류 처리
