DeadLock 발생 원인 찾기

Deadlock 발생시 원인과 해결법.

 

1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다.

DBCC traceon(1204,-1)

DBCC Tracestatus(-1) -- 잘 실행되고 있는지 확인

 

2. Deadlock 발생되면 SQL의 에러로그에 로그가 남게 된다.

Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2
Grant List 1::
Grant List 2::
Owner:0x27c007e0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:84 ECID:0
SPID: 84 ECID: 0 Statement Type: UPDATE Line #: 11
Input Buf: RPC Event: dbo.Example_Stored_proc
Requested By: 0
ResType:LockOwner Stype:'OR' Mode: IX SPID:78 ECID:0 Ec:(0x44AA55F0) Value:0x3affcd00 Cost:(0/0)
Node:2 PAG: 9:1:18134 CleanCnt:2 Mode: SIU Flags: 0x2
Grant List 1::
Owner:0x28e6f060 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:78 ECID:0
SPID: 78 ECID: 0 Statement Type: UPDATE Line #: 11
Input Buf: RPC Event: dbo. Example_Stored_proc
Grant List 2::
Requested By:
ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost:(0/0)
Victim Resource Owner:
ResType:LockOwner

Stype:'OR' Mode: IX SPID:84 ECID:0 Ec:(0x239955F0) Value:0x3affc940 Cost

 

3.  에러로그를 확인해 보면 'Example_stored_proc'  프로시저에서 update 시 exclusive lock  이 발생하고 있다는 것을 확인 할 수 있다.

 

4. sp_helptext 'Example_stored_proc'  를 실행하여 프로시저의 내용을 확인 한다.

 

5. 이와 관련있는 프로시저를 확인하고 deadlock 이 발생하는 page를 확인할 수 있다.

Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

 

6. 해당 페이지를 조사해 본다. , DBCC Page 사용

MSDN : http://support.microsoft.com/kb/83065

 

DBCC page(9,1,18061,0)

PAGE: (1:18061)
---------------
BUFFER:
-------
BUF @0x01665900
---------------
bpage = 0x1DF58000 bhash = 0x00000000 bpageno = (1:18061)
bdbid = 9 breferences = 1 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1DF58000
----------------
m_pageId = (1:18061) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId = 1013578649 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (0:0) pminlen = 52 m_slotCnt = 82
m_freeCnt = 3075 m_freeData = 5009 m_reservedCnt = 0
m_lsn = (2689:87968:2) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
.................

object_id를 알아 낼 수 있다.

 

7.  object 확인

 

Use DBNAME
Select object_name(OBJECT_ID)

8. 프로시저에서 해당 object를 확인하고  update 문을 찾아 그대로 update 테스트 해 본다.

이때, 해당 컬럼에 인덱스가 있는지 확인 한다. update 시 인덱스가 없으면 table scan 을 하게되고 exclusive lock 이 발생한다.

 

9. Key 되는 컬럼에 인덱스를 생성 해 준다.

 

10. 후에 다시 실행해봐서 deadlock 이 발생하는지 확인 한다.