有时候系统提示会话被牺牲或者牺牲品的关键字信息,说明数据库端发生了死锁。对于已经发生的死锁,在SQL Server中是否可以查询到死锁对应信息呢?当然是可以的,SQL Server提供多种方式来获取。
基于通过视图来获取死锁,针对不同的SQL Server版本,会有所不同。某些情况下语句可能无法执行出结果,这种情况需要杀掉会话,采用其他方式查询。
SQL Server 2008
SELECT XEventData.XEvent.value('@timestamp', 'datetime2(3)'), cast(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) AS DeadlockGraph FROM (SELECT CAST (target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE [name] = 'system_health' AND st.target_name = 'ring_buffer') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report' option(maxdop 1,RECOMPILE);
SQL Server 2012
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime FROM ( SELECT XEvent.query('.') AS XEvent FROM ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st 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 src ;
类似结果
但是上面的脚本,在SQL Server 后续的版本上有时候并不可靠,查询不到结果,这个时候,我们可以通过下面的语句在SQL Server服务器上执行,通过分析system_health*文件来获取死锁信息。
DECLARE @SessionName SysName SELECT @SessionName = 'system_health'IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN DROP TABLE #EventsENDDECLARE @Target_File NVarChar(1000) , @Target_Dir NVarChar(1000) , @Target_File_WildCard NVarChar(1000)SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_addressWHERE s.name = @SessionName AND t.target_name = 'event_file'SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SELECT @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel'SELECT DeadlockGraph = CAST(event_data AS XML) , DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)INTO #EventsFROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS FWHERE event_data like '<event name="xml_deadlock_report%';WITH Victims AS( SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)') , e.DeadlockID FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)), DeadlockObjects AS( SELECT DISTINCT e.DeadlockID , ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources))SELECT *FROM( SELECT e.DeadlockID , TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime') , DeadlockGraph , DeadlockObjects = substring((SELECT (', ' + o.ObjectName) FROM DeadlockObjects o WHERE o.DeadlockID = e.DeadlockID ORDER BY o.ObjectName FOR XML PATH ('') ), 3, 4000) , Victim = CASE WHEN v.VictimID IS NOT NULL THEN 1 ELSE 0 END , SPID = Deadlock.Process.value('@spid', 'int') , ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)') , LockMode = Deadlock.Process.value('@lockMode', 'char(1)') , Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)') , ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29) WHEN 'SQLAgent - TSQL JobStep (Job ' THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67) ELSE Deadlock.Process.value('@clientapp', 'varchar(100)') END , HostName = Deadlock.Process.value('@hostname', 'varchar(20)') , LoginName = Deadlock.Process.value('@loginname', 'varchar(20)') , InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)') FROM #Events e CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process) LEFT JOIN Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')) X --In a subquery to make filtering easier (use column names, not XML parsing), no other reasonORDER BY DeadlockID DESC
类似的结果如下:
点击DeadlockGraph字段下的超链接就能获取死锁信息
这样就可以看到对应的SQL语句了。
开启跟踪标记来获取
如果通过上面的方式,如果还是无法查询到,那么可以开启1204,1222跟踪标记。在发生死锁的时候,由SQL Server将死锁信息写入到SQL Server错误日志中,然后通过SP_READERRORLOG查找死锁信息。
脚本:
dbcc traceon(1204,1222,3605,-1)
类似查询结果如下:
创建Extended Events
上面为SQL Server死锁信息获取的方式。如果碰到有死锁时,并且需要提单分析的时候,最好能附上抓到的死锁信息,更加快速的分析问题。
注意:本文为死锁信息抓取,而非阻塞,阻塞和死锁并不相同,抓取的方式也不一样。
推荐阅读