概述

在工作中，我遇到過(guò)對(duì)表執(zhí)行 dml 語(yǔ)句時(shí)出現(xiàn)持續(xù)長(zhǎng)時(shí)間死鎖的情況。在這種情況下，我使用輕量級(jí) T-SQL 查詢來(lái)查找死鎖，即SQL 連接的阻塞和阻塞會(huì)話 ID。根據(jù)該語(yǔ)句返回的詳細(xì)信息，我能夠找到執(zhí)行阻塞會(huì)話的應(yīng)用程序或用戶，并幫助我終止特定的 SQL 連接。它還幫助我們識(shí)別并修復(fù)頻繁阻塞的 SQL 語(yǔ)句。

死鎖產(chǎn)生的原因

多個(gè)并發(fā)事務(wù)同時(shí)訪問(wèn)數(shù)據(jù)庫(kù)資源，而這些事務(wù)需要訪問(wèn)的資源（如表、行、頁(yè)等）相互沖突，從而導(dǎo)致彼此互相等待，形成死鎖。具體來(lái)說(shuō)，當(dāng)一個(gè)事務(wù)正在訪問(wèn)某些資源時(shí)，會(huì)對(duì)這些資源進(jìn)行加鎖以保證數(shù)據(jù)的一致性。如果另一個(gè)事務(wù)也要訪問(wèn)這些資源，但是由于鎖的存在而無(wú)法訪問(wèn)，那么它就會(huì)被阻塞并等待鎖釋放。而如果兩個(gè)或多個(gè)事務(wù)都互相持有對(duì)方需要的鎖，那么它們就會(huì)陷入相互等待的狀態(tài)，無(wú)法向前執(zhí)行，形成死鎖。

SQL Server死鎖產(chǎn)生的場(chǎng)景包括但不限于以下幾種情況：

• 并發(fā)事務(wù)更新相同的數(shù)據(jù)行或頁(yè)
• 并發(fā)事務(wù)以不同的順序獲取鎖
• 并發(fā)事務(wù)在執(zhí)行過(guò)程中出現(xiàn)了阻塞或超時(shí)等異常情況
• 并發(fā)事務(wù)使用不同的隔離級(jí)別，例如一個(gè)事務(wù)使用了“讀已提交”隔離級(jí)別，而另一個(gè)事務(wù)使用了“可重復(fù)讀”隔離級(jí)別

如何發(fā)現(xiàn)死鎖

為了避免死鎖的產(chǎn)生，可以采用以下幾種方法：

• 盡可能縮短事務(wù)的執(zhí)行時(shí)間
• 減少事務(wù)中對(duì)資源的鎖定時(shí)間
• 使用較低的隔離級(jí)別
• 對(duì)并發(fā)訪問(wèn)頻繁的資源進(jìn)行分區(qū)
• 監(jiān)控并發(fā)事務(wù)的運(yùn)行情況，及時(shí)發(fā)現(xiàn)并解決死鎖問(wèn)題。

監(jiān)控并發(fā)事務(wù)的運(yùn)行情況，是及時(shí)發(fā)現(xiàn)死鎖的重要的手段，也是我們工作中最常用的手段。

下面是我用來(lái)快速查找死鎖的查詢。該語(yǔ)句基于SYS.DM_EXEC_REQUESTS動(dòng)態(tài)管理視圖。在此語(yǔ)句中，blocking_session_id列為您提供阻塞連接的 session_id，wait_type 列為您提供導(dǎo)致死鎖的等待類型。獲取blocking_session_id后，您可以使用另一個(gè)dmv SYS.DM_EXEC_SESSIONS來(lái)獲取有關(guān)會(huì)話或連接的更多詳細(xì)信息。

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