교착상태 - DEAD LOCK

교착 상태(deadlock)이란 두 개 이상의 프로세스가 서로를 차단하고 있는 상태를 말한다.

 

위의 그림은 일반적인 교착상태의 예제이다.

 

• 작업 T1은 리소스 R1에 대한 잠금을 가지며(R1 -> T1) 리소스 R2에 대한 잠금을 요청했다.(R2 -> T2)
  
• 작업 T2는 자원 R2에 대한 잠금을 가지며 자원 R1에 대한 잠금을 요청했다.
  
  
• 두 작업은 자원이 사용 가능할 때까지 계속 수행 할 수 없으며 Task가 계속 될 때까지 자원을 해제
  할  수 없기 때문에 교착상태가 존재한다.

 

SQL Server  DBMS 는 SQL Server 내에서 교착 상태 사이클을 자동으로 감지한다.

특별한 설정을 하지 않는다면 데이터 베이스 엔진이 가장 일을 적게 한 프로세스를 선택해서 중지 시키는데,

그 프로세스의 트랜잭션에서작업한 내용을 ROLLBACK하는 것이 가장 적은 비용을 발생 시키기 때문이다.

SQL Server 에서는 DEADLOCK_PRIORITY 라는 세션 옵션을 통해 교착 상태에 대한 우선순위를 조절할 수 있다.

 

이 값은 -10 부터 10 까지 21개의 값 중 하나로 지정된다.

 

우선순위를 정해주면 얼마나 많은 작업을 한 것과는 상관없이 가장 낮은 우선순위를

 

가지는 프로세스가 교착상태의 "희생자"로 선택된다.

 

우선순위가 동일할 경우에는 작업한 양에 따라 희생자가 선택된다.

 

 

쿼리창1(process 1)

BEGIN TRAN 
UPDATE dbo.MANUFACTURER_INC 
SET staff_count = 150000 
WHERE comp_seq = 1

 

쿼리창1에서 MANUFACTURER_INC 테이블에 comp_seq = 1 인 행에  X-LOCK 을 걸어본다.

그럼 현재는 트랜잭션이 닫히지 않았으므로 계속 X-LOCK이 걸려있는 상태일 것이다.

 

 

쿼리창2(process 2)

BEGIN TRAN 
UPDATE dbo.USER_REAL 
SET user_gender = 'F' 
WHERE user_seq = 1

 

쿼리창 2에서는 USER_REAL 테이블에 user_seq 가 1인 행에 X-LOCK을 걸어본다.

 

역시 현재 트랜잭션이 닫히지 않았으므로 계속 X-LOCK이 걸려있는 상태이다.

 

즉, 현재의 상태는 아래와 같다고 할 수 있다.

 

 

 

위와 같은 상태에서 쿼리창1에서 아래와 같은 쿼리를 날려준다.

 

쿼리창1

SELECT * FROM dbo.USER_REAL 
WHERE user_seq = 1


COMMIT TRAN

 

PROCESS1에서 USER_REAL 테이블에 대해 S-LOCK을 요청할것이고,

 

현재 해당 테이블은 PROCESS2의 작업으로 인해

X-LOCK 이 걸려있으므로 PROCESS2  의 트랜잭션이 끝나길 대기하고 있을것이다.

(아직까지는 블로킹 상황이지 교착상태가 아니다.)

 

 

근데 이런 상황중에 쿼리창2에서 아래의 쿼리문을 날려줘보자.

 

 

쿼리창2

SELECT * FROM dbo.MANUFACTURER_INC 
WHERE comp_seq = 1

 

쿼리2에서 MANUFACTURER_INC  에대한 데이터를 읽어오려고 한다.

 

또한 PROCESS1에서 현재 해당 테이블에 대해

 

X-LOCK을 걸어놓은 상태이기 때문에, 쿼리창2에서 실행한 쿼리문은 S-LOCK을 대기시켜야 한다.

 

즉, 위의 그림과 같이 교착되는 상태를 보여준다.

위와 같은 상황을 "교착상태"에 빠졌다라고 하고 SQL Server가 두 프로세스중 하나를 교착상태의 희생자로

 

선택한 다음 다음과 같은 오류를 발생시키며 해당 프로세스를 강제로 종료시키게 된다.

 

 

 

사실 업무에서는 위와같은 형태에서 데드락이 걸리는 경우는 많이 없다.

 

실제 업무에서 데드락이 걸리는 경우는 쿼리 필터에서 적절한 인덱스가 없는경우 교착상태가 발생한다.

 

 

아래의 예제를 보면서 이해하자.

SP_HELP dbo.TBLINSA

현재 TBLINSA 테이블의 상태를보면 인덱스가 하나도 없는것을 볼 수 있다.

즉 어떤 필터링을 하던지 무조건 테이블 스캔이 발생한다는 뜻이된다.

 

쿼리창1 (process 1)

BEGIN TRAN 
UPDATE dbo.TBLINSA 
SET basicPay = 0 
WHERE num = 1001

 

쿼리창2 (process 2)

BEGIN TRAN 
UPDATE dbo.MANUFACTURER_INC 
SET staff_count = 150000 
WHERE comp_seq = 1

 

위의 두 구문을 각각 쿼리창에서 실행해주면 위와같은 그림의 형태가 된다.

 

TBLINSA 는 UPDATE를 해야하는데 인덱스가 마땅하게 없으므로 테이블 스캔을 한 뒤에

 

num = 1001인 행을 찾아 UPDATE 시켜준다. 

 

즉, 전체를 스캔해야 하므로 TBLINSA 의 모든 행을 지금 X-LOCK 으로 잠근것이다.

 

MANUFACTURER_INC 는 현재 comp_seq  를 기준으로 PK가 정해져있다. (그래서 전체 락이 걸리진 않는 상태)

 

현재상태에서 아래와 같이 쿼리를 날려보자.

 

 

쿼리창1 (process 1)

SELECT * FROM dbo.MANUFACTURER_INC 
WHERE comp_seq = 1

첫번째 쿼리창에서 MANUFACTURER_INC 테이블에 comp_seq  = 1 을 읽으려고 할것이다.(S-LOCK)

 

이미 comp_seq  = 1에는 X-LOCK이 걸려있으므로 트랜잭션이 끝날때까지 기다릴것이다.(BLOCKING)

 

 

쿼리창2 (process 2)

SELECT * FROM dbo.TBLINSA 
WHERE num = 1003

이번에는 쿼리창2에서 TBLINSA 테이블의 num = 1001 인 데이터를 읽으려고 하는데

 

해당 행도 X-LOCK 이 걸려있으므로  S-LOCK에 대한 승인을 얻으려면 해당

 

트랜잭션이 정상적으로 종료되어야 한다.

 

하지만 두개의 프로세스에서 블로킹이 걸려있으므로 예상한것처럼 교착상태가 발생한다.

 

이를 해결하려면 TBLINSA 테이블에 적절한 인덱스를 생성해서 교착상태를 막을 수 있다.

교착상태를 해결하는 방법

 

먼저 아래와 같이 인덱스를 생성해준다.

ALTER TABLE dbo.TBLINSA ADD CONSTRAINT PK__TBLINSA__NUM PRIMARY KEY (num)

 

 

쿼리창1 (process 1)

BEGIN TRAN 
UPDATE dbo.TBLINSA 
SET basicPay = 0 
WHERE num = 1001

 

쿼리창2 (process 2)

BEGIN TRAN
UPDATE dbo.MANUFACTURER_INC 
SET staff_count = 150000 
WHERE comp_seq = 1

쿼리창1 (process 1)

SELECT * FROM dbo.MANUFACTURER_INC 
WHERE comp_seq = 1

인덱스가 없을 때와 달리 인덱스가 존재할때는 테이블 스캔이 아닌 clustered index seek 방법으로 한번에 찾아가서

 

해당 행만 X-LOCK 을 걸어준다. (나머지 행들은 잠금이 걸리지 않는다.)

 

쿼리창2 (process 2)

SELECT * FROM dbo.TBLINSA 
WHERE num = 1003

 

즉, 적절한 인덱스가 존재하기 때문에 TBLINSA 테이블 모든 행에 XLOCK을 걸지 않고

 

인덱스를 잘 타서 1003  번째에 SLOCK의 승인을 얻어오는 모습을 볼 수 있다.