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.59 |
DB－如何排查数据库死锁问题？
声明：除非特别标注，否则均为本站原创文章，转载时请以链接形式注明文章出处。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
