[MSSQL] 데드락(교착상태) 테스트 예제

DeadLock이란 둘 이상의 세션이 서로 맞물려 차단된 상태를 말한다.

DeadLock이 발생하면 영원히 지속되기 때문에 SQL 서버가 자동으로 찾아내어 해제시켜 준다.

DeadLock에는 두가지 종류가 있다.

---

1. 순환 교착 (Cycle Deadlock)

교착상태를 설명할 때 보통 예로 드는 것이 이 "Cycle DeadLock"이다.

두 세션이 필요한 리소스를 얻기 위해 서로 상대방이 Lock을 풀기를 기다리는 상태라고 설명할 수 있다.

예를 들면 다음과 같다.

 

테스트를 위한 테이블 생성 및 데이터 입력

create table TAB_A(
seq int NOT NULL
,name varchar(10)
)
go

ALTER TABLE TAB_A ADD CONSTRAINT PK_TAB_A PRIMARY KEY CLUSTERED(Seq ASC) create table TAB_B(
seq int NOT NULL
,name varchar(10)
)
go

ALTER TABLE TAB_B ADD CONSTRAINT PK_TAB_B PRIMARY KEY CLUSTERED(Seq ASC)

 

insert into TAB_A values (1,'가1')
insert into TAB_A values (2,'가2')
insert into TAB_A values (3,'가3')
insert into TAB_A vaues (4,'가4')
insert into TAB_A values (5,'가5')

insert into TAB_B values (1,'가1')
insert into TAB_B values (2,'가2')
insert into TAB_B values (3,'가3')
insert into TAB_B values (4,'가4')
insert into TAB_B values (5,'가5')

 

테이블 생성후 세션 1번이랑 2번을 동시에 실행한다.

[세션1 실행]

BEGIN TRAN

UPDATE TAB_B SET name = name+'1' WHERE seq = 1 WAITFOR DELAY '0:0:5'

UPDATE TAB_A SET name = name+'2' WHERE seq = 1

 

[세션2 실행]

BEGIN TRAN

UPDATE TAB_A SET name = name+'1' WHERE seq = 1 WAITFOR DELAY '0:0:5'

UPDATE TAB_B SET name = name+'2' WHERE seq = 1

 

위와 같이 실행시키면 한쪽 세션에서는 다음과 같은 결과를 볼 수 있다.

메시지 1205, 수준 13, 상태 51, 줄 9 트랜잭션(프로세스 ID 54)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.

 

---

2. 변환 교착 (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이다.

 

[세션1 실행]

DECLARE @NUM varchar(10)

BEGIN TRAN

SELECT @NUM = name + '1' FROM TAB_A
WITH (REPEATABLEREAD)--트랜잭션이 시작되기 전(쿼리가 시작되기 전이 아닌) commit 된 결과를 참조합니다
WHERE seq = 1

WAITFOR DELAY '0:0:5'

UPDATE TAB_A SET name = @NUM WHERE seq = 1

 

[세션2 실행]

DECLARE @NUM varchar(10)
BEGIN TRAN
SELECT @NUM = name + '1' FROM TAB_A WITH (REPEATABLEREAD)--트랜잭션이 시작되기 전(쿼리가 시작되기 전이 아닌) commit 된 결과를 참조합니다
WHERE seq = 1

WAITFOR DELAY '0:0:5'

UPDATE TAB_A SET name = @NUM WHERE seq = 1

위와 같이 실행시키면 한쪽 세션에서는 다음과 같은 결과를 볼 수 있다.

 

메시지 1205, 수준 13, 상태 51, 줄 22 트랜잭션(프로세스 ID 54)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.

 

이상으로 데드락 테스트를 마친다.