확장 이벤트를 통한 Dead Lock 캡처

 

1)   Dead Lock 발생 테스트

A.     2개의 Session에서 다음과 같은 순서로 진행한다.

Session01	Session02
DROP TABLE IF EXISTS TBL_TEST2 GO CREATE TABLE TBL_TEST2 (            A INT ,          B INT ); INSERT TBL_TEST2 VALUES (1,1); GO   BEGIN TRAN            UPDATE TBL_TEST2 SET B=2 GO
	DROP TABLE IF EXISTS TBL_TEST3 GO CREATE TABLE TBL_TEST3 (            A INT ,          B INT ); INSERT TBL_TEST3 VALUES (1,1); GO   BEGIN TRAN            UPDATE TBL_TEST3 SET B=2   GO
SELECT * FROM TBL_TEST3 GO
	SELECT * FROM TBL_TEST2 GO

 

B.      Session02에서 다음과 같은 오류를 확인되면서 Rollback 된다.

2)   확장이벤트를 통한 Deadlock 캡쳐

A.     확장이벤트

                                  i.         xEvent라고 불리는 SQL Server Engine 관리 Event

                                 ii.         최소한의 성능 리소스를 사용하여 간단한 성능 모니터링을 한다.

B.      확장 이벤트 생성

                                  i.         SQL Server 2012 이후로 SSMS에서 GUI로 생성이 가능하다.

                                 ii.         GUI를 통한 이벤트 등록

가)    새 세션마법사 실행

나)    세션 이름 설정

(ㄱ)  세션 이름 : 저장될 xEvent Session 이름

(ㄴ)  일정 : SQL Server 시작 시 해당 Session 자동 시작 여부

다)    템플릿 사용

(ㄱ)  다음 이벤트 세션 템플릿 사용 : MS에서 기본 제공하는 템플릿을 통해 생성 가능

(ㄴ)  템플릿 사용 안 함 : 사용자 정의로 이벤트 생성

(ㄷ)  Dead Lock 관련 템플릿은 제공되지 않으므로 사용자 정의로 생성

라)    캡쳐할 이벤트를 선택 : deadlock으로 검색 나오는 xml_deadlock_report 이벤트를 선택

마)    모든 이벤트에 공통적으로 사용되는 전역 값을 기호에 맞게 선택한다.

바)    원하는 조건으로 필터링 할 수 있다.

사)    특정 파일로 남길 수 있고 버퍼에 쌓을 수 있다. 현재는 테스트로 진행함으로 버퍼에 쌓아서 확인하도록 한다.

아)    스크린트 버튼으로 스크립트를 생성할 수 있고, 마침을 눌러 시작할 수 있다.

자)    아래는 스크립트 생성으로 생성된 스크립트

CREATE EVENT SESSION [GUI_DEADLOCK_REPORT] ON SERVER ADD EVENT sqlserver.xml_deadlock_report(     ACTION(sqlserver.session_id,sqlserver.username)) ADD TARGET package0.ring_buffer WITH (STARTUP_STATE=ON) GO

차)    마침을 눌러 시작

 

                                iii.         쿼리를 통한 생성

가)    xEvent는 위의 GUI 마찬가지의 쿼리로 생성

CREATE EVENT SESSION [QUERY_DEADLOCK_REPORT] ON SERVER ADD EVENT sqlserver.xml_deadlock_report(     ACTION(sqlserver.session_id,sqlserver.username)) ADD TARGET package0.ring_buffer WITH (STARTUP_STATE=ON) GO

나)    xEvent 캡처 시작

ALTER EVENT SESSION [QUERY_DEADLOCK_REPORT] ON SERVER STATE = START; GO

C.      쿼리를 통한 이벤트 확인

SELECT  CAST(XST.target_data AS XML) AS TargetData FROM   sys.dm_xe_session_targets AS XST INNER   JOIN sys.dm_xe_sessions AS XS ON                 XST.event_session_address = XS.address WHERE  xs.name = 'GUI_DEADLOCK_REPORT'   SELECT  CAST(XST.target_data AS XML) AS TargetData FROM   sys.dm_xe_session_targets AS XST INNER   JOIN sys.dm_xe_sessions AS XS ON                 XST.event_session_address = XS.address WHERE  xs.name = 'QUERY_DEADLOCK_REPORT'

3)   Dead Lock 캡처 확인

A.     위의 방법으로 Dead Lock을 재 발생 시켜 캡처를 한다.

B.      다음의 결과를 누를 시 xml을 확인할 수 있다.

<RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="6088">   <event name="xml_deadlock_report" package="sqlserver" timestamp="2021-04-21T08:47:23.839Z">     <data name="xml_report">       <type name="xml" package="package0" />       <value>         <deadlock>           <victim-list>             <victimProcess id="process16493afa8c8" />           </victim-list>           <process-list>             <process id="process16493afa8c8" taskpriority="0" logused="248" waitresource="RID: 8:1:16576:0" waittime="3703" ownerId="1167563" transactionname="user_transaction" lasttranstarted="2021-04-21T17:47:15.113" XDES="0x1648f64c428" lockMode="S" schedulerid="6" kpid="7192" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-04-21T17:47:20.133" lastbatchcompleted="2021-04-21T17:47:20.130" lastattention="1900-01-01T00:00:00.130" clientapp="Microsoft SQL Server Management Studio - 쿼리" hostname="UBASE-YHJIN" hostpid="8376" loginname="UBASE-YHJIN\yhjin_ubase" isolationlevel="read committed (2)" xactid="1167563" currentdb="8" currentdbname="test" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">               <executionStack>                 <frame procname="adhoc" line="2" stmtstart="4" stmtend="48" sqlhandle="0x020000003c2c6326af155a59271a767daeed4cdb255af2e10000000000000000000000000000000000000000"> unknown    </frame>               </executionStack>               <inputbuf>   SELECT * FROM TBL_TEST2    </inputbuf>             </process>             <process id="process16493afaca8" taskpriority="0" logused="248" waitresource="RID: 8:1:24680:0" waittime="5877" ownerId="1167528" transactionname="user_transaction" lasttranstarted="2021-04-21T17:47:12.620" XDES="0x1648f67c428" lockMode="S" schedulerid="6" kpid="9992" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-04-21T17:47:17.960" lastbatchcompleted="2021-04-21T17:47:17.957" lastattention="1900-01-01T00:00:00.957" clientapp="Microsoft SQL Server Management Studio - 쿼리" hostname="UBASE-YHJIN" hostpid="8376" loginname="UBASE-YHJIN\yhjin_ubase" isolationlevel="read committed (2)" xactid="1167528" currentdb="8" currentdbname="test" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">               <executionStack>                 <frame procname="adhoc" line="1" stmtend="44" sqlhandle="0x02000000a86e28318b45ec09291f4ebecd3df42882978ad40000000000000000000000000000000000000000"> unknown    </frame>               </executionStack>               <inputbuf> SELECT * FROM TBL_TEST3    </inputbuf>             </process>           </process-list>           <resource-list>             <ridlock fileid="1" pageid="16576" dbid="8" objectname="test.dbo.TBL_TEST2" id="lock1649318cb80" mode="X" associatedObjectId="72057594044940288">               <owner-list>                 <owner id="process16493afaca8" mode="X" />               </owner-list>               <waiter-list>                 <waiter id="process16493afa8c8" mode="S" requestType="wait" />               </waiter-list>             </ridlock>             <ridlock fileid="1" pageid="24680" dbid="8" objectname="test.dbo.TBL_TEST3" id="lock16493180600" mode="X" associatedObjectId="72057594045005824">               <owner-list>                 <owner id="process16493afa8c8" mode="X" />               </owner-list>               <waiter-list>                 <waiter id="process16493afaca8" mode="S" requestType="wait" />               </waiter-list>             </ridlock>           </resource-list>         </deadlock>       </value>     </data>     <action name="username" package="sqlserver">       <type name="unicode_string" package="package0" />       <value>sa</value>     </action>     <action name="session_id" package="sqlserver">       <type name="uint16" package="package0" />       <value>32</value>     </action>   </event> </RingBufferTarget>

 

                                  i.         노란색 Dead Lock 정보를 xdl 파일로 만들면 ssms 에서 다음과 같은 그래프를 확인할 수 있다.

                                 ii.         위의 Xml의 내용을 정리하면 다음과 같다.

가)    test.dbo.TBL_TEST2 객체에 대해 process16493afaca8 프로세스 베타 Lock이 걸린 상태에서 process16493afa8c8 프로세스가 공유 Lock을 요청하여 wait 중임

나)    test.dbo.TBL_TEST3 객체에 대해 process16493afa8c8 프로세스가 베타 Lock이 걸린 상태에서 process16493afaca8 프로세스가 공유 Lock을 요청하여 wait 됨

다)    양쪽이 처리할 수 없이 wait되면서 Dead Lock이 발생

 

3)   출처

               A.  https://jwprogramming.tistory.com/12

               B.  https://docs.microsoft.com/en-us/dynamic...

               C.  https://docs.microsoft.com/ko-kr/sql/t-sql/functions...

               D.  https://docs.microsoft.com/ko-kr/sql/relat...

               E.  https://docs.microsoft.com/ko-kr/sql/t-sql/sta...

               F.  https://blog.naver.com/jevida/140207526376

               G.  https://docs.microsoft.com/ko-kr/sql...