[MSSQL/SQL Server] 트랜잭션

업무 중 QA 서버에서 업데이트 작업에 대해 주기적으로 데드락이 잡히는 현상이 발생하는데 이를 해결하지 못하고있어서 트랜잭션에 대해 공부를 좀 더 자세히하고 기록해둘 필요성을 느꼇다.

 

트랜잭션 :  하나의 논리적 작업 단위로 수행되는 일련의 작업. 작업의 논리적 단위는 ACID(원자성, 일관성, 격리성,영속성) 속성이라고 하는 네 가지 속성을 통해 트랜잭션으로서의 자격을 부여.

 

 

ROLLBACK 단위 :

일괄 처리에서 제약 조건 위반 등 런타임 문 오류가 발생하면 SQL Server 데이터베이스 엔진에서는 기본적으로 오류를 발생시킨 문만 롤백 하지만

 

SET XACT_ABORT ON이 실행된 후에는 모든 런타임 문 오류 발생 시 자동으로 현재 트랜잭션이 롤백됩니다

 

컴파일 단위 오류 : 전체가 실행이 되지 않으므로 롤백이나 커밋이 되지 않음.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.  
GO  
SELECT * FROM TestBatch;  -- Returns no rows.  
GO  
--전체가 실행되지 않음

런타임 단위 오류 : 실행된 부분까지는 커밋이 되나 오류가 발생한 구문에서 롤백이 진행 됨.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error. -> 롤백 단위  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO

 

LOCK 관리 기본 사항

• 잠금
  • 각 트랜잭션은 해당 트랜잭션이 종속되는 행, 페이지 또는 테이블 등의 리소스에 대해 서로 다른 유형의 잠금을 요청합니다. 잠금은 다른 트랜잭션의 리소스 수정을 차단하여 잠금을 요청하는 트랜잭션에 문제가 발생하지 않도록 합니다. 각 트랜잭션은 잠긴 리소스에 더 이상 종속되지 않게 되면 잠금을 해제.

 

데드락

변환 교착 (Conversion DeadLock) : 데드락 유형 중 변환 교착에 대하여 정리된 내용이 있어서 참조하였다.

Conversion DeadLock 은 잠금모드가 SharedLock에서 UPD-Lock 혹은 X-Lock으로 전환될 때 발생하는 문제.
1) 세션 A가 트랜젝션을 건 후 어떤 Row에 공유잠금(S-Lock)을 걸었다
2) 세션 B도 트랜젝션을 건 후 그 Row에 공유잠금을 걸었다. 공유잠금끼리는 서로 호환 가능하므로 이슈 없음.
3) 이 상태에서 세션 A는 그 Row에 Update를 시도한다. 이 Row에는 세션 B에서 공유잠금을 걸었으므로 세션 A는 배타적잠금을 걸기 위해 세션 B가 공유잠금을 풀어주기를 기다린다.
4) 이때, 세션 B도 그 Row에 Update를 시도한다

과연 어떻게 될까? A는 B의 공유잠금때문에 Update를 진행하지 못하고, B는 A의 공유잠금때문에 Update를 진행하지 못하게 된다. 이것이 Conversion DeadLock이다.
실제 테스트를 해보면 다음과 같다.

-- 이 SQL을 세션 1, 2에서 연달아 실행한다.
DECLARE @NUM INT

BEGIN TRAN

SELECT @NUM = VAL + 1 FROM TAB1 
  WITH (REPEATABLEREAD) 
  WHERE NUMTYPE = 'TestApp'

WAITFOR DELAY '0:0:5'

UPDATE TAB1 SET VAL = @NUM WHERE NUMTYPE = 'TestApp'

위와 같은 SQL이 데드락이 발생하지 않도록 하려면 어떻게 해야 할까?
위의 교착상태는 SELECT시 공유잠금이 동시에 걸렸기 때문에 발생한 것이므로,
SELECT시에 다음과 같이 잠금 힌트를 주어 처음부터 명시적으로 UPDLOCK을 걸어주면 
교착상태를 방지할 수 있다.
SELECT @NUM = VAL + 1 FROM TAB1 WITH (UPDLOCK) WHERE NUMTYPE = 'TestApp'

UPDATE Character SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
위와 같은 SQL이 Conversion DeadLock이 발생하기 쉬우므로
UPDATE Character WITH (UPDLOCK) SET login_count = login_count + 1 , login_time = GETDATE() WHERE name = @IN_NAME;
이렇게 써야 한다는 글이 있었는데, 이것은 잘못된 내용이었다.
UPDATE문 실행시 WHERE 필터가 진행될 때는 해당 Row에 Update Lock이 걸리며,
실제로 WRITE가 진행될 때 Exclusive Lock으로 전환된다.
위의 업데이트문에서는 Shared Lock이 걸리지 않으며 따라서 위의 잠금 힌트는 주나마나 한 것이 된다.
※ 여러 테이블을 Join 하는 경우 WITH (UPDLOCK) 과 같은 힌트는 각 테이블 별로 지정해야 한다.

데드락이 발생한 경우 조치 방법.

데드락의 원인이 되는 구문을 확인한 후 이슈가 없을 경우 해당 세션을 KILL한다.

하지만 이는 일시적인 조치에 불가하므로 지속적인 데드락이 발생한 경우 내부 요소를 수정해야한다. 

이럴 경우 내가 진행했던 방안은 다음과 같다.

• ISOLATION LEVEL을 READ UNCOMMITTED 로 설정한다. S락을 막는 방안으로 설정을 한다.
• 데드락이 발생하는 구문에 BEGIN TRAN COMMIT TRAN을 명시적으로 묶어준다. 이를 통하여 해당 구문이 약간의 지연은 발생 하여도 DEAD LOCK이 아닌 순차적으로 처리되는 방안을 기대하였다. 하지만 동일한 KEY에 접근하여 UPDATE를 진행하는 상황이 발생하여 DEADLOCK이 잡힘
• LOCK TIMEOUT을 설정한다. 락이 잡힌 경우 특정 시간을 지정하여 해당 시간이 지난 경우 트랜잭션을 롤백하도록 하는 설정이다. 세션별로 설정이 가능하나 나의 경우에는 이슈가 있는 SP에 설정하여 처리하였다.

 

 

 

 

 

 

참조 : 

https://kuaaan.tistory.com/100 [달토끼 대박나라~!! ^^:티스토리]

https://learn.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16