안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 14-2. 트랜잭션과 잠금처리 - 트랜잭션 종류를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 트랜잭션과 잠금처리 - 트랜잭션 종류입니다.
TL;DR
자동 커밋, 명시적, 암시적 트랜잭션 분류를 살펴보고, 여러 SQL Server 인스턴스에서 수행 가능한 분산 트랜잭션과, 명시적 트랜잭션에서 SET XACT_ABORT 설정의 중요성을 다룹니다.
SQL Server는 내부적으로 작업의 최소 단위인 이 트랜잭션을 조금 더 세분화해 두었습니다. 이 강좌에서는 트랜잭션의 종류와 명시적으로 트랜잭션 작업을 수행할 경우, 아주 중요한 SET XACT_ABORT 설정에 대해서도 살펴봅니다.
트랜잭션 종류
SQL Server에서는 아래와 같이 세 가지 종류로 트랜잭션 작업을 분류합니다.
- Autocommit transactions (자동 커밋 트랜잭션)
- Explicit transactions (명시적 트랜잭션)
- Implicit transactions (암시적 트랜잭션)
Autocommit (자동 커밋 트랜잭션)
아래와 같은 SQL 구문이 있을 경우,
UPDATE 계좌 SET 금액 = 금액 - 100 WHERE ID LIKE '김대우' UPDATE 계좌 SET 금액 = 금액 + 100 WHERE ID LIKE '손석구'
Autocommit은 위의 작업을 자동으로 다음처럼 변경합니다.
BEGIN TRAN UPDATE 계좌 SET 금액 = 금액 - 100 WHERE ID LIKE '김대우' COMMIT TRAN BEGIN TRAN UPDATE 계좌 SET 금액 = 금액 + 100 WHERE ID LIKE '손석구' COMMIT TRAN
이렇게 자동으로 변환하고 수행되는 트랜잭션을 Autocommit이라고 합니다.
Explicit transactions (명시적 트랜잭션)
Explicit 트랜잭션은 아래의 SQL 구문처럼, 명시적으로 사용자가 트랜잭션을 정의하고 수행하는 처리입니다.
BEGIN TRAN UPDATE 계좌 SET 금액 = 금액 - 100 WHERE ID LIKE '김대우' UPDATE 계좌 SET 금액 = 금액 + 100 WHERE ID LIKE '손석구' COMMIT TRAN
이렇게 사용자가 직접 트랜잭션 범위를 정의하고 실행하는 것을 의미합니다.
Implicit transactions (암시적 트랜잭션)
SQL Server만 사용하셨다면 약간 특이한 방식입니다. 참고로, Oracle 데이터베이스는 기본적으로 Implicit 트랜잭션 방식을 사용합니다.
SQL Server에서 Implicit 트랜잭션이 활성화되면 모든 데이터 변경 작업 후, 반드시 Commit 문을 실행해야만 데이터 변경이 적용됩니다.
SQLER의 질문 게시판에 가끔 들어오는 문의로,
Q. SQL Server 버그인가요?
DBMS 관리자가 휴가 중이라 잠시 회사 내 관리 도구로 SQL서버를 관리하는 중입니다. 제품 테이블에서 특정 제품 가격을 2배로 변경하는 작업을 수행했고, 결과도 확인했는데, 잠시 후 누군가 데이터를 바꿔 두는 것인지 알 수 없으나 다른 사용자가 로그인해 확인하면 가격이 다시 원상태로 돌아와 있습니다. 버그인가요?
Implicit transactions 설정이 ON으로 되어 있을 경우 발생합니다. 위의 경우는 자체 관리 도구에서 해당하는 커넥션 - 연결된 세션에만 설정한 것일 수 있으며, 회사 내 관리 도구를 이용한다면, 관리자가 연결에 설정을 추가해 제어할 수 있습니다.
USE AdventureWorks; GO -- 암시적 트랜잭션 설정 SET IMPLICIT_TRANSACTIONS ON; GO SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID = 999; GO -- 가격을 2배로 변경 UPDATE Production.Product SET ListPrice = ListPrice * 2 WHERE ProductID = 999; GO -- 조회하면 변경되어 있음. SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID = 999; GO -- "새 쿼리"를 실행해 새로운 쿼리창 세션을 열고 조회하면 가격이 그대로임. SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID = 999; GO
결론적으로, SET IMPLICIT TRANSACTION이 설정되어 있다면, 눈에 보이지 않지만 암시적으로 “BEGIN TRAN” 구문이 추가된 상태입니다. 따라서, 맨 마지막에 COMMIT TRAN을 실행하면 변경사항이 데이터베이스에 적용됩니다.
-- 가격을 2배로 변경 UPDATE Production.Product SET ListPrice = ListPrice * 2 WHERE ProductID = 999; GO COMMIT TRAN; -- "새 쿼리"를 실행해 새로운 세션을 열고 조회하면 트랜잭션 처리가 완료되어 변경된 가격임. SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ProductID = 999; GO
분산 트랜잭션 처리(Distributed Transaction)
다음은 분산 트랜잭션입니다. 예를 들어, 두대의 서로 다른 SQL Server 인스턴스가 있을 경우, 두 시스템에서 트랜잭션 처리를 수행할 때, 분산 트랜잭션을 사용합니다. 연결된 서버(Linked Server)를 활용해 4 파트 쿼리(서버명.DB명.스키마명.테이블명)나 오픈쿼리(OPENQUERY) 이용해 처리하며, MS-DTC(Distributed Transaction Coordinator)가 적절하게 구성되고 활성화되어 있어야만 합니다.
BEGIN DISTRIBUTED TRANSACTION UPDATE 시스템이름A.디비명.소유자명.테이블명 SET price= price*2 UPDATE 시스템이름B.디비명.소유자명.테이블명 SET price= price*2 COMMIT TRAN --또는 ROLLBACK TRAN
중요한 부분으로, MS-DTC 활성화가 잘 되어있는지를 조사해야 하며, 연결된 서버에 적절한 권한으로 로그인된 후 객체를 핸들 할 수 있는지 역시 잘 체크해야 합니다. 대부분의 경우 이 두 가지 문제로 분산 트랜잭션 처리가 안될 수 있습니다.
SET XACT_ABORT ON
마지막으로, 트랜잭션 처리에서 가장 중요한 설정인 SET XACT_ABORT ON입니다.
BEGIN TRAN 구문을 이용하는 처리는 항상 SET XACT_ABORT ON이 같이 따라온다고 생각하면 편합니다. 그럼, SQL 쿼리 예제로 살펴보도록 하겠습니다.
--SET XACT_ABORT를 이용한 런타임시의 오류라도 에러 시 롤백 USE AdventureWorks; GO -- 만약 테이블이 존재하면 삭제 IF OBJECT_ID(N'dbo.TR_Test', N'U') IS NOT NULL DROP TABLE dbo.TR_Test; GO -- 테이블 생성 CREATE TABLE TR_Test( c1 INT NOT NULL PRIMARY KEY , c2 INT ); GO --데이터 삽입 insert into TR_Test(c1,c2) VALUES(1, 1); GO -- Syntax로 INSERT 문 오류 insertT into TR_Test(c1,c2) VALUES(1, 1); GO -- 테이블명 - OBJECT 명 오류 insert into TR_TestZZZ(c1,c2) VALUES(1, 1); GO --기본키 제약 위반 insert into TR_Test(c1,c2) VALUES(1, 1); GO --데이터 조회 select * from TR_Test; GO -- INSERT를 수행합니다. 기본키 제약 오류가 런타임에서 발생합니다. BEGIN TRAN; insert into TR_Test(c1,c2) VALUES(2, 2); insert into TR_Test(c1,c2) VALUES(2, 2); -- 기본키 제약 오류 insert into TR_Test(c1,c2) VALUES(3, 3); COMMIT TRAN; GO --데이터 조회 SELECT * FROM TR_Test; GO -- 트랜잭션 작업 중에서 하나라도 작업이 실패하면, 에러와 함께 모두 ROLLBACK 해야 함. -- 테이블 데이터 삭제 TRUNCATE TABLE TR_Test; GO -- XACT_ABORT 설정 SET XACT_ABORT ON; -- INSERT를 수행합니다. 기본키 제약 오류가 런타임에서 발생합니다. BEGIN TRAN; insert into TR_Test(c1,c2) VALUES(2, 2); insert into TR_Test(c1,c2) VALUES(2, 2); -- 기본키 제약 오류 insert into TR_Test(c1,c2) VALUES(3, 3); COMMIT TRAN; GO -- 모두 롤백된 것을 확인. SELECT * FROM TR_Test; GO
애플리케이션이나 저장 프로시저에서 개발할 경우, 트랜잭션 처리를 한다면 항상 SET XACT_ABORT ON을 선언하고 작업하세요. 런타임 오류가 발생해도 이렇게 롤백시키고 완료합니다.
XACT_ABORT ON 방식 외에 @@ERROR로 트랜잭션 롤백을 수행하는 방안이 있으나, 권장하지 않습니다. 항상 XACT_ABORT ON 방식을 이용하세요.
@@ERROR로 트랜잭션 롤백을 수행하는 방안 - 권장하지 않습니다.
-- 새 쿼리 수행 - 만약 테이블이 존재하면 삭제 IF OBJECT_ID(N'dbo.TR_Test', N'U') IS NOT NULL DROP TABLE dbo.TR_Test; GO -- 테이블 생성 CREATE TABLE TR_Test( c1 INT NOT NULL PRIMARY KEY , c2 INT ); GO --추천하지 않지만, @@ERROR로 트랜잭션 롤백 방안 BEGIN TRAN; insert into TR_Test(c1,c2) VALUES(2, 2); IF @@ERROR <> 0 GOTO ERROR_TRANSACTION; insert into TR_Test(c1,c2) VALUES(2, 2); --에러 발생 IF @@ERROR <> 0 GOTO ERROR_TRANSACTION; insert into TR_Test(c1,c2) VALUES(3, 3); IF @@ERROR <> 0 GOTO ERROR_TRANSACTION; --모든 작업 성공 print N'삽입이 성공했습니다.'; COMMIT TRAN; GOTO END_BATCH; -- 하나라도 실패 ERROR_TRANSACTION: print N'삽입 실패'; ROLLBACK TRAN; END_BATCH: print N'BATCH가 끝났습니다.'; GO
다음으로 트랜잭션과 체크포인트 프로세스를 리뷰하면서, 장애 상황에서 트랜잭션이 어떻게 처리되는지, REDO와 UNDO에 대해서 상세히 알아보도록 하겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
