DB－如何排查数据库死锁问题？
问题描述：
如何排查数据库死锁问题？
哪个进程、具体到哪个ＳＱＬ，导致了当前进程发生了死锁昵？
解决方案：
在数据库中执行下列SQL，即可轻松获取相关死锁信息：
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
SELECT XEvent.query('.') AS XEvent
FROM (
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.NAME = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;
<deadlock> <victim-list><victimProcess id="process288b088" /></victim-list> <process-list> <process id="process288b088" taskpriority="0" logused="1208" waitresource="KEY: 7:72060118349512704 (b0d8839b8b98)" waittime="1134" ownerId="239211796" transactionname="user_transaction" lasttranstarted="2019-03-11T14:29:46.510" XDES="0x41b8aaf20" lockMode="U" schedulerid="13" kpid="5024" status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-03-11T14:29:46.627" lastbatchcompleted="2019-03-11T14:29:46.627" lastattention="2019-03-11T14:29:46.033" clientapp=".Net SqlClient Data Provider" hostname="K3C-APP-021-A" hostpid="7196" loginname="hbisnma" isolationlevel="read committed (2)" xactid="239211796" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><executionStack><frame procname="adhoc" line="1" stmtstart="118" stmtend="360" sqlhandle="0x02000000015f882369aea49"> unknown </frame><frame procname="adhoc" line="1" stmtstart="8" stmtend="304" sqlhandle="0x02000000acfc8a0caef209004053c"> unknown </frame></executionStack> <inputbuf> update PAEZ_BarCodeScanDCL set FBILLSTATUS='1',F_PAEZ_FBillNo='PLCK2019031132203' where F_PAEZ_BarCode='QDTG021903070467' and F_PAEZ_OrgId=100012 </inputbuf> </process>
<process id="process3a8bc28" taskpriority="0" logused="12892" waitresource="KEY: 7:72060118349512704 (fb6755511587)" waittime="1166" ownerId="239210657" transactionname="user_transaction" lasttranstarted="2019-03-11T14:29:45.907" XDES="0x12060343b0" lockMode="U" schedulerid="12" kpid="4260" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-03-11T14:29:46.593" lastbatchcompleted="2019-03-11T14:29:46.593" lastattention="2019-03-11T14:29:46.557" clientapp=".Net SqlClient Data Provider" hostname="K3C-APP-021-A" hostpid="7196" loginname="hbisnma" isolationlevel="read committed (2)" xactid="239210657" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><executionStack><frame procname="adhoc" line="1" stmtstart="118" stmtend="360" sqlhandle="0x02000000015f882369aea49"> unknown </frame><frame procname="adhoc" line="1" stmtstart="8" stmtend="294" sqlhandle="0x02000000d9a20a095442cd2f256db54107ce42"> unknown </frame></executionStack> <inputbuf> update PAEZ_BarCodeScanDCL set FBILLSTATUS='1',F_PAEZ_FBillNo='SCRK00178705' where F_PAEZ_BarCode='HFTG021903070375' and F_PAEZ_OrgId=100013 </inputbuf> </process> </process-list>
<resource-list> <keylock hobtid="72060118349512704" dbid="7" objectname="AIS_K3CLOUD_V6_HBISNMA.dbo.PAEZ_BarCodeScanDCL" indexname="pk_PAEZ_BarCodeScanDCL" id="lock49d4b1c00" mode="X" associatedObjectId="72060118349512704"> <owner-list><owner id="process3a8bc28" mode="X" /></owner-list> <waiter-list><waiter id="process288b088" mode="U" requestType="wait" /></waiter-list> </keylock>
<keylock hobtid="72060118349512704" dbid="7" objectname="AIS_K3CLOUD_V6_HBISNMA.dbo.PAEZ_BarCodeScanDCL" indexname="pk_PAEZ_BarCodeScanDCL" id="lock531ae1000" mode="X" associatedObjectId="72060118349512704"> <owner-list><owner id="process288b088" mode="X" /></owner-list> <waiter-list><waiter id="process3a8bc28" mode="U" requestType="wait" /> </waiter-list> </keylock> </resource-list> </deadlock> |
DB－如何排查数据库死锁问题？
本文2024-09-23 04:24:16发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-165078.html
