MSSQL - SET XACT_ABORT 사용법 (트랜잭션 오류 롤백)

 

• 트랜잭션에 대한 자세한 설명이 필요한 경우 아래의 포스팅 참조

https://luvris2.tistory.com/387

MSSQL - 트랜잭션의 개념 이해와 종류, 사용 방법

---

SET XACT_ABORT

옵션 : ON

• Transact-SQL문에서 런타임 오류가 발생 할 경우 전체 트랜잭션이 종료된 후 롤백되는 기능

---

옵션 : OFF

• 오류를 일으킨 Transact-SQL문만 롤백되고 처리 작업 계속 진행
• 단, 오류 심각도에 따라 전체 트랜잭션이 롤백 될 수 있음

---

기본 설정

• SQL문의 기본 설정은 OFF
• 트리거의 기본설정은 ON

---

사용 예시

테이블 존재 유무 확인

/* object_id(object_name, object_type) */
-- object_name : data type : nvarchar(N)
-- object_type : User-defined table (U)

-- 't1' 이름의 사용자 정의 테이블 값이 존재하면 해당 테이블 삭제 
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
    
IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;

• N : 유니코드의 문자열 (nvarchar data type)
• U : 사용자 정의 테이블 (User-defined Table)

---

테이블 생성

• t1 테이블
  • a : 정수형, Null 허용 안함, 기본 키
• t2 테이블
  • a: 정수형, Null 허용 안함, 외래 키 (t1의 a컬럼 참조)

/* Column Setting */
-- t1.a : int, NN, PK
-- t2.a : int, NN, FK(t1.a)

CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));

---

t1 테이블 컬럼 값 입력

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);

---

SET XACT_ABORT OFF

-- SET XACT_ABORT 옵션 OFF
-- OFF : 오류를 일으킨 부분만 롤백되고 다른 SQL문은 처리 진행
SET XACT_ABORT OFF;

-- 트랜잭션 값 입력 후 커밋
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;

 

• 수행결과
  • 외래 키 에러 난 부분을 제외하고는 SQL문이 그대로 처리 진행됨을 확인

SELECT * FROM T2

---

SET XACT_ABORT ON

-- SET XACT_ABORT 옵션 ON
-- ON : 오류 발생시 전체 트랜잭션을 종료하고 롤백 진행
SET XACT_ABORT ON;

-- 트랜잭션 값 입력 후 커밋
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;

 

• 수행 결과
  • 모든 값이 입력되지 않고 롤백
  • 아래의 결과 값 (1, 3)은 OFF 옵션 테스트 결과로 저장된 값임

SELECT * FROM T2

---

참고

• Microsoft - SQL 문서 - SET XACT_ABORT

https://learn.microsoft.com/ko-kr/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver16 

SET XACT_ABORT(Transact-SQL) - SQL Server

 

• Microsoft - SQL 문서 - OBJECT_ID

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/object-id-transact-sql?view=sql-server-ver16 

OBJECT_ID(Transact-SQL) - SQL Server

 

• Stackoverflow.com - What does N and U what are its meaning

https://stackoverflow.com/questions/26958673/what-does-n-and-u-stand-for-and-what-are-its-meaning-while-finding-an-object

What does N and U stand for and what are its meaning while finding an object