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

利用此腳本排查歷史死鎖很方便。