deadlock waitresource = object
데드락 발생시 extended events 로 캡처 후 내용을 xdl로 저장하면 아래와 같은 그래프로 볼 수 있다.
잠금 리소스가 "개체 잠금"이라는 특이한 현상 발생
waitresource = OBJECT: 5:1829893886:8 이거라고 한다. (databaseid, objectid, lockPartition)
clustered type primary key만 있는 테이블이며, 형 변환 없이 정확히 쿼리하는데 deadlock 이 발생함
윈인은 테이블 생성시 allow_row_locks 와 allow_page_locks 를 disable 해두었음.
해결 방법
ALTER INDEX PK_XX ON XX
REBUILD WITH (ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
Deadlock XDL
<deadlock>
<victim-list>
<victimProcess id="process19727d3bc28" />
</victim-list>
<process-list>
<process id="process19727d3bc28" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:8 " waittime="616" ownerId="132371886315" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x27c1e900460" lockMode="X" schedulerid="20" kpid="1476" status="suspended" spid="9848" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi031.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886315" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
WHERE xxid = @P0
AND startDate = CAST(@P1 AS CHAR(8))
AND endDate = CAST(@P2 AS CHAR(8))
AND memberId = CAST(@P3 AS VARCHAR(20)) </inputbuf>
</process>
<process id="process1972770f848" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:0 " waittime="616" ownerId="132371886316" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x1938d11c460" lockMode="X" schedulerid="9" kpid="10696" status="suspended" spid="10300" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi019.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886316" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
WHERE xxid = @P0
AND startDate = CAST(@P1 AS CHAR(8))
AND endDate = CAST(@P2 AS CHAR(8))
AND memberId = CAST(@P3 AS VARCHAR(20)) </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="8" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1905d4c0680" mode="IX" associatedObjectId="1829893886">
<owner-list>
<owner id="process1972770f848" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="process19727d3bc28" mode="X" requestType="wait" />
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1054afcec00" mode="X" associatedObjectId="1829893886">
<owner-list>
<owner id="process19727d3bc28" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process1972770f848" mode="X" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
확인용 쿼리
SELECT name, allow_row_locks, allow_page_locks
FROM sys.indexes
AND allow_row_locks = 0
AND allow_page_locks = 0
RID 잠금이 유지되거나 요청되는 테이블 내의 단일 행을 식별합니다. RID는 RID: db_id:file_id:page_no:row_no 표시됩니다. 예들 들어 RID: 6:1:20789:0입니다.
OBJECT 잠금이 유지되거나 요청되는 테이블을 식별합니다. OBJECT은 OBJECT: db_id:object_id(으)로 표시됩니다. 예들 들어 TAB: 6:2009058193입니다.
KEY 잠금이 유지되거나 요청되는 인덱스 내의 키 범위를 식별합니다. KEY는 KEY: db_id:hobt_id(index key hash value) 표시됩니다. 예들 들어 KEY: 6:72057594057457664 (350007a4d329)입니다. %%lockres%% 로 쿼리 가능
PAG 잠금이 보유 또는 요청된 페이지 리소스를 식별합니다. PAG는 PAG: db_id:file_id:page_no 표시됩니다. 예들 들어 PAG: 6:1:20789입니다.
EXT 익스텐트 구조를 식별합니다. EXT는 EXT: db_id:file_id:extent_no 표시됩니다. 예들 들어 EXT: 6:1:9입니다.
DB 데이터베이스 잠금을 식별합니다. DB는 다음 방법 중 하나로 표시됩니다.
DB: db_id
DB: db_id[BULK-OP-DB]백업 데이터베이스에서 수행한 데이터베이스 잠금을 식별합니다.
DB: db_id[BULK-OP-LOG]특정 데이터베이스에 대한 백업 로그가 취한 데이터베이스 잠금을 식별합니다.
APP 애플리케이션 리소스에서 수행한 잠금을 식별합니다. APP은 APP: lock_resource 표시됩니다. 예들 들어 APP: Formf370f478입니다.
METADATA 교착 상태에 관련된 메타데이터 리소스를 나타냅니다. METADATA에는 많은 하위 리소스가 있으므로 반환되는 값은 교착 상태가 있는 하위 리소스에 따라 달라집니다. 예를 들어 METADATA.USER_TYPE는 user_type_id = *integer_value*를 반환합니다. METADATA 리소스 및 하위 리소스에 대한 자세한 내용은 sys.dm_tran_locks를 참조하세요.
HOBT 교착 상태와 관련된 힙 또는 B-트리를 나타냅니다.
WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
FROM sys.dm_xe_sessions AS xs
INNER JOIN sys.dm_xe_session_targets AS xst
ON xs.[address] = xst.event_session_address
WHERE xs.[name] = 'system_health'
AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
FROM cteDeadLocks AS c
CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;