교착상태 (DeadLock)

교착상태(DeadLock)은 서로 다른 두개의 작업(트랜잭션)이 리소스 잠금을 교차해서 획득하려 할 때 발생합니다.

 

가장 보편적인 DeadLock그림입니다. T1작업은 R1(리소스)의 잠금을 획득하고 R2를 액세스 하려 합니다.
동시에 T2 작업은 R2(리소스)의 잠금을 획득하고 R1을 액세스 하려 합니다.
이처럼 둘중 하나가 중지되지 않는 한 빼도박도 못하는 대치 상황이 바로 교착상태입니다.
이 교착상태는 데이터외에 다른 리소스들에서도 발생할수 있는데요. 자주 일어날수 있는 상황은 아니지만
충분히 가능성은 있어 보입니다. 간단히 몇가지만 설명드리자면

- 스레드 할당을 기다리는 대기작업이 어떤 리소스를 소유하고 있습니다. 불행히도 현재 모든 스레드들의
   작업들이 대기하고 있는 그 리소스를 사용해야 하는 경우입니다.
   예를들면 세션S1이 R1(리소스)에 공유잠금(S)을 획득하고 sleep에 빠졌습니다.
   이때 실행가능한 모든 세션들이 R1에 단독잠금을 획득하려 하게 되는 상황입니다.
   S1은 스레드를 할당받기 위해 대기하고 있고, 사용가능한 스레드들은 전부 R1의 잠금을 획득하려 하니
   교착상태가 발생하게 됩니다.

-  메모리관련 교착상태입니다.
   동시에 두개의 쿼리 Q1과 Q2가 각각 10MB, 20MB, 합해서 30MB가 필요한 사용자 정의 함수로 실행됩니다.
   하지만 현재 사용가능한 메모리가 20MB밖에 없다고 하면 교착상태가 발생한 다고 합니다.
   솔직히 위내용은 문서를 보고 왜 그런지 궁금했습니다. 아직도 확인중에 있지만
   아마도 쿼리 내에서 사용자 정의함수가 각 행마다 실행되고, 사용자정의 함수는 행마다 리소스를 소비하게돼 

   수행 중간쯤에서 이 같은 상황이 발생되는 것 같습니다.

 

이외에도 참고링크에 보면 몇가지 더 나와있습니다.
http://msdn.microsoft.com/en-us/library/ms178104.aspx 

교착상태에 대한 간단한 테스트와 분석방법에 대해 확인해 보겠습니다.

/* 테스트테이블 및 데이터를 적재합니다. */

CREATE TABLE TBL_Temp1(

       Col1 BIGINT NOT NULL, Col2 CHAR(5000))

GO

CREATE TABLE TBL_Temp2(

       Col11 BIGINT NOT NULL, Col22 INT, Col33 CHAR(5000))

GO

 

SET NOCOUNT ON

BEGIN TRANSACTION

DECLARE @i INT

SET @i = 1

WHILE(1=1)

BEGIN

       INSERT INTO TBL_Temp1 VALUES(@i,REPLICATE('A',1000))

       INSERT INTO TBL_Temp2 VALUES(@i,@i,REPLICATE('A',1000))

 

       IF(@i >= 10000) BREAK;

       SET @i += 1;

END

COMMIT TRANSACTION

 

ALTER TABLE TBL_Temp1

ADD CONSTRAINT PK_TBL_Temp1 PRIMARY KEY (Col1)

GO

ALTER TABLE TBL_Temp2

ADD CONSTRAINT PK_TBL_Temp2 PRIMARY KEY (Col11)

GO

 

/* 테스트프로시저를생성합니다. */

CREATE PROC PROC_A

AS

BEGIN TRANSACTION

UPDATE TBL_Temp1 SET Col2 = 'aa'

WHERE Col1 = 1

 

WAITFOR DELAY '00:00:10'

 

UPDATE TBL_Temp2 SET Col33 = 'bb'

WHERE Col11 = 1

 

COMMIT TRANSACTION

GO

 

 

CREATE PROC PROC_B

AS

BEGIN TRANSACTION

 

UPDATE dbo.TBL_Temp2 SET Col33 = 'cc'

WHERE Col11 = 1

 

WAITFOR DELAY '00:00:10'

 

UPDATE dbo.TBL_Temp1 SET Col2 = 'dd'

WHERE Col1 = 1

 

COMMIT TRANSACTION

GO

DBCC TRACEON( 1204 , 1222, - 1 )

GO

 

DeadLock Graph (실행전 프로필러에서 DeadLock Graph를 선택하고 실행합니다.)

 

 

간단히 DeadLock Graph를 확인해 보면

spid 52번과 53번이 리소스 획득중 52번이 희생자로 선정됐고,
52번은 인덱스 PK_TBL_Temp2의 키잠금(단독잠금)을 획득했고, 인덱스 PK_TBL_Temp1 의 키잠금 획득을 시도하고 있습니다.
53번은 인덱스 PK_TBL_Temp1의 키잠금(단독잠금)을 획득했고, 인덱스 PK_TBL_Temp2 의 키잠금 획득을 시도하고 있습니다.
이같은 리소스에 대한 교차 엑세스로 DeadLock 이 발생했는데요.

2005부터인가요. 추적 플래그 1204, 1222 를 통해 ErrorLog로 DeadLock정보를 수집할수 있습니다.
DBCC TRACEON( 1204 , 1222, - 1 )

EXEC Sp_readerrorlog 를 통해 수집된 DeadLock 정보를 확인 할 수 있습니다.
수집정보의 항목별 정보는 http://msdn.microsoft.com/en-us/library/ms178104.aspx 를 통해 확인 할 수 있습니다.
Case) Trace 1222

deadlock-list
 deadlock victim=process682e38
  process-list
(DeadLock 발생시 희생자 정보를 보여줍니다. deadlock victime processid와 동일한 process id가 희생자겠죠.)
   process id=process682e38 taskpriority=0 logused=0 waitresource=KEY: 8:72057594049855488 (010008207756) waittime=4679 ownerId=175701 transactionname=user_transaction lasttranstarted=2010-11-12T16:45:52.950 XDES=0x84ccc10 lockMode=X schedulerid=4 kpid=3044 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-11-12T16:45:52.950 lastbatchcompleted=2010-11-12T16:45:46.803 lastattention=2010-11-12T16:45:45.537 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=HEESUGO hostpid=5152 loginname=NEXON\heesugo isolationlevel=read committed (2) xactid=175701 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=SSD_Test.dbo.PROC_B line=11 stmtstart=266 stmtend=376 sqlhandle=0x030008008b606438a9b913012c9e00000100000000000000
(프로시저 PROC_B 에 포함된 아래 UPDATE 구문이 KEY: 8:72057594049921024 (010008207756) 잠금 획득을 대기하고 있었습니다.)
UPDATE dbo.TBL_Temp1 SET Col2 = 'dd'
WHERE Col1 = 1    
     frame procname=adhoc line=1 sqlhandle=0x01000800c97af83a50073a05000000000000000000000000
EXEC PROC_B    
    inputbuf
EXEC PROC_B

(DeadLock발생시 실행됐었던(영향을 준) Processid 에 대한 정보를 보여줍니다.)
   process id=process3aeaa8 taskpriority=0 logused=252 waitresource=KEY: 8:72057594049921024 (010008207756) waittime=6661 ownerId=175528 transactionname=user_transaction lasttranstarted=2010-11-12T16:45:50.970 XDES=0x8518280 lockMode=X schedulerid=2 kpid=3036 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-11-12T16:45:50.970 lastbatchcompleted=2010-11-12T16:45:39.720 clientapp=Microsoft SQL Server Management Studio - 쿼리 hostname=HEESUGO hostpid=5152 loginname=NEXON\heesugo isolationlevel=read committed (2) xactid=175528 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=SSD_Test.dbo.PROC_A line=10 stmtstart=250 stmtend=356 sqlhandle=0x03000800523c703717c50a012c9e00000100000000000000
UPDATE TBL_Temp2 SET Col33 = 'bb'
WHERE Col11 = 1    
     frame procname=adhoc line=1 sqlhandle=0x0100080030e5550010fce114000000000000000000000000
EXEC PROC_A    
    inputbuf
EXEC PROC_A

(리소스 정보를 확인할 수 있습니다.) 
resource-list
   keylock hobtid=72057594049855488 dbid=8 objectname=SSD_Test.dbo.TBL_Temp1 indexname=PK_TBL_Temp1 id=lock69f1000 mode=X associatedObjectId=72057594049855488
    owner-list
     owner id=process3aeaa8 mode=X
    waiter-list
     waiter id=process682e38 mode=X requestType=wait
   keylock hobtid=72057594049921024 dbid=8 objectname=SSD_Test.dbo.TBL_Temp2 indexname=PK_TBL_Temp2 id=lock1496c6c0 mode=X associatedObjectId=72057594049921024
    owner-list
     owner id=process682e38 mode=X
    waiter-list
     waiter id=process3aeaa8 mode=X requestType=wait 


Case) Trace 1204
Deadlock encountered .... Printing deadlock information
Wait-for graph
2010-11-12 16:14:42.530 spid6s NULL
2010-11-12 16:14:42.530 spid6s Node:1 
2010-11-12 16:14:42.530 spid6s KEY: 8:72057594049855488 (010008207756) CleanCnt:2 Mode:X Flags: 0x1
2010-11-12 16:14:42.530 spid6s  Grant List 1:
2010-11-12 16:14:42.530 spid6s    Owner:0x14956D80 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x0593F280
2010-11-12 16:14:42.530 spid6s    SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 10
2010-11-12 16:14:42.530 spid6s    Input Buf: Language Event:   EXEC PROC_A 
2010-11-12 16:14:42.530 spid6s Requested by:
2010-11-12 16:14:42.530 spid6s   ResType:LockOwner Stype:'OR'Xdes:0x084CCC10 Mode: X SPID:52 BatchID:0 ECID:0 TaskProxy:(0x084CA354) Value:0x69eb420 Cost:(0/0)
2010-11-12 16:14:42.530 spid6s NULL
2010-11-12 16:14:42.530 spid6s Node:2 
2010-11-12 16:14:42.530 spid6s KEY: 8:72057594049921024 (010008207756) CleanCnt:2 Mode:X Flags: 0x1
2010-11-12 16:14:42.530 spid6s  Grant List 3:
2010-11-12 16:14:42.530 spid6s    Owner:0x0541B360 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x084CCC38
2010-11-12 16:14:42.530 spid6s    SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 13
2010-11-12 16:14:42.530 spid6s    Input Buf: Language Event: EXEC PROC_B 
2010-11-12 16:14:42.530 spid6s Requested by:
2010-11-12 16:14:42.530 spid6s   ResType:LockOwner Stype:'OR'Xdes:0x0593F258 Mode: X SPID:53 BatchID:0 ECID:0 TaskProxy:(0x08520354) Value:0x14956de0 Cost:(0/0)
2010-11-12 16:14:42.530 spid6s NULL
2010-11-12 16:14:42.530 spid6s Victim Resource Owner:
2010-11-12 16:14:42.530 spid6s  ResType:LockOwner Stype:'OR'Xdes:0x084CCC10 Mode: X SPID:52 BatchID:0 ECID:0 TaskProxy:(0x084CA354) Value:0x69eb420 Cost:(0/0)

추가로 대부분 교착상태는 리소스에 대한 액세스패턴이 교차해 발생하기때문에

이를 통일시켜 주면 해결이 가능한데요. 액세스패턴이 교차하지 않는 상황에서도 교착상태는

발생합니다. 바로 인덱스누락된 테이블에서의 단독잠금(XLOCK)을 요청하는 경우와

아래와 같이

TBL_Temp2 테이블에 Col11필드는 클러스터드(CL) 인덱스가, Col22는 넌클러스터드(NC) 인덱스를 가진경우,
그림을 보면서 설명하면

 

   

왼쪽 쿼리는 Col11을 CL로 검색하고 다시 Col22를 변경해야 하므로 NC를 액세스하게 되는 상황

동시 오른쪽 쿼리는 Col22를 NC로 검색하고 Col33을 획득하기 위해 다시 CL을 액세스해야 하는

상황입니다. 서로 잠금을 획득하려 하는 리소스들을 잠그고 있으니 교차해 대치되는 상황입니다.

이 경우 해결책은 오른쪽 쿼리가 CL을 사용하지 않게, Covered 혹은 Include를 사용하면 됩니다.

 

참고: http://msdn.microsoft.com/en-us/library/ms178104.aspx 

참고: Inside Microsoft SQL Server2005 :T-SQL Programming