SQL Server :查找死鎖的 T-SQL

概述

在工作中，我遇到過對表執行 dml 語句時出現持續長時間死鎖的情況。在這種情況下，我使用輕量級 T-SQL 查詢來查找死鎖，即SQL 連接配接的阻塞和阻塞會話 ID。根據該語句傳回的詳細資訊，我能夠找到執行阻塞會話的應用程式或使用者，并幫助我終止特定的 SQL 連接配接。它還幫助我們識别并修複頻繁阻塞的 SQL 語句。

死鎖産生的原因

多個并發事務同時通路資料庫資源，而這些事務需要通路的資源（如表、行、頁等）互相沖突，進而導緻彼此互相等待，形成死鎖。具體來說，當一個事務正在通路某些資源時，會對這些資源進行加鎖以保證資料的一緻性。如果另一個事務也要通路這些資源，但是由于鎖的存在而無法通路，那麼它就會被阻塞并等待鎖釋放。而如果兩個或多個事務都互相持有對方需要的鎖，那麼它們就會陷入互相等待的狀态，無法向前執行，形成死鎖。

SQL Server死鎖産生的場景包括但不限于以下幾種情況：

• 并發事務更新相同的資料行或頁
• 并發事務以不同的順序擷取鎖
• 并發事務在執行過程中出現了阻塞或逾時等異常情況
• 并發事務使用不同的隔離級别，例如一個事務使用了“讀已送出”隔離級别，而另一個事務使用了“可重複讀”隔離級别

如何發現死鎖

為了避免死鎖的産生，可以采用以下幾種方法：

• 盡可能縮短事務的執行時間
• 減少事務中對資源的鎖定時間
• 使用較低的隔離級别
• 對并發通路頻繁的資源進行分區
• 監控并發事務的運作情況，及時發現并解決死鎖問題。

監控并發事務的運作情況，是及時發現死鎖的重要的手段，也是我們工作中最常用的手段。

下面是我用來快速查找死鎖的查詢。該語句基于SYS.DM_EXEC_REQUESTS動态管理視圖。在此語句中，blocking_session_id列為您提供阻塞連接配接的 session_id，wait_type 列為您提供導緻死鎖的等待類型。擷取blocking_session_id後，您可以使用另一個dmv SYS.DM_EXEC_SESSIONS來擷取有關會話或連接配接的更多詳細資訊。

SELECT
    session_id,
    start_time,
    [status],
    command,
    blocking_session_id,
    wait_type,
    wait_time,
    open_transaction_count,
    transaction_id,
    total_elapsed_time,
    Definition = CAST(text AS VARCHAR(MAX))
FROM
    SYS.DM_EXEC_REQUESTS
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id != 0