SQL SERVER 查看近期死锁

在项目运行的过程中，死锁不可能完全避免，但要尽可能减少死锁的出现，

产生死锁的原因主要是：
1，系统资源不足。
2，进程运行推进的顺序不合适。
3，资源分配不当等。

产生死锁的四个必要条件：
- 互斥条件：一个资源每次只能被一个进程使用，即在一段时间内某资源仅为一个进程所占有。此时若有其他进程请求该资源，则请求进程只能等待。
- 请求与保持条件：进程已经保持了至少一个资源，但又提出了新的资源请求时，该资源已被其他进程占有，此时请求进程被阻塞，但对自己已获得的资源保持不放。
- 不可剥夺条件:已经分配的资源不能从相应的进程中被强制地剥夺。
- 循环等待条件: 系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

 

排查死锁是有哪个sql导致的，死锁产生后即消失，很难让用户重现死锁问题，虽然可以从日志中分析死锁，但非常繁琐，可以利用下面的sql server 扩展事件，查询历史死锁，查询原因：

/****************************************************************
* 检查近期死锁；定位到具体的对象。方便排查问题；
********************************************************************/

declare @sessionname sysname 

select @sessionname = 'system_health'


if object_id('tempdb..#events') is not null begin
    drop table #events
end

declare @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_address
where 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'

--keep this as a separate table because it's called twice in the next query.  you don't want this running twice.
select deadlockgraph = cast(event_data as xml)
    , deadlockid = row_number() over(order by file_name, file_offset)
into #events
from sys.fn_xe_file_target_read_file(@target_file_wildcard, null, null, null) as f
where 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
order by deadlockid desc

 

利用此脚本排查历史死锁很方便。