보통 데드락(Dead Lock) 시나리오에서 많이 사용하는 시나리오는 다음과 같다
세션 1>
BEGIN TRAN
UPDATE A SET ID = 2
WAITFOR DELAY '00:00:02'
UPDATE B SET ID = 2
COMMIT TRAN
세션 2>
BEGIN TRAN
UPDATE B SET ID = 3
WAITFOR DELAY '00:00:02'
UPDATE A SET ID = 3
COMMIT TRAN
이런 패턴은 순환교착(Cycle DeadLock)라 한다
이러한 순환 교착 시나리오 이외에 다음과 같은 쿼리도 데드락을 유발할 수 있다
일명 변환 교착(Conversion DeadLock) 이라 하는데, 샘플을 만들어 보자
테스트를 위해 Board라는 테이블을 생성하고 값을 입력하자
CREATE TABLE BOARD
(
IDX INT,
TITLE VARCHAR(100),
READCOUNT INT
)
INSERT INTO BOARD VALUES(1,'제목1',1)
INSERT INTO BOARD VALUES(2,'제목2',1)
게시판 테이블이라 가정하고 특정 글의 조회수를 증가하는 다음 쿼리를 가정하자
-------------------------------------------------------------------------------------------------------------------------------------
BEGIN TRAN
DECLARE @IDX INT
SET @IDX = 1
DECLARE @READCOUNT INT
SELECT @READCOUNT = READCOUNT + 1 FROM BOARD WITH(REPEATABLEREAD) WHERE IDX = @IDX
WAITFOR DELAY '00:00:02'
UPDATE BOARD SET READCOUNT = @READCOUNT WHERE IDX = @IDX
COMMIT TRAN
-------------------------------------------------------------------------------------------------------------------------------------
SELECT 된 값이 트랜잭션 내에서 변경되지 못하도록 REPEATABLEREAD 격리 수준을 지정하였다
이 쿼리를 세션 1, 세션 2에 동시에 실행해 보면 데드락이 발생하여 하나의 쿼리는 실패하게 된다
격리수준 중에서 반복읽기(REPEATABLEREAD) 는,
SELECT 한 값이 트랜잭션이 종료될 보장될 수 있도록 UPDATE/DELETE가 되지 않도록 설정하는 것이다
두 세션에서 UPDATE 하려고 할 때 해당 테이블이 락을 풀지 않아서 발생하는 데드락 시나리오이다
물론 조회 수 정도를 가지고 REPEATABLEREAD 수준을 걸지는 않겠지만,
이런 패턴의 쿼리는 종종 발생할 수 있다.