檢測 SQL Server 中的阻塞

在 SQL Server 中，有多種方法可以識別和排除涉及阻塞的系統(tǒng)進程 ID (spid)。 它們可能包括：

• 系統(tǒng)存儲過程

您可以使用內(nèi)置sp_who2系統(tǒng)存儲過程來查看阻塞信息。要查看阻塞的實際操作，請運行檢查活動事務的查詢：

EXEC sp_who2;

該查詢返回服務器上所有活動的事務。狀態(tài) RUNNABLE 或 SUSPENDED 表示它們持有鎖。BlkBy列顯示阻塞會話。在我們的示例中，BlkBy列中的值53 指的是阻塞進程的會話 ID（SPID 列）。

如您所見，它執(zhí)行起來簡單快捷，無需額外設置。

• 動態(tài)管理視圖 (DMV)

DMV 可用于監(jiān)控工作負載性能并檢測被阻止或長時間運行的查詢。

例如，具有指定 WHERE 條件的 DMV 僅返回被阻止的進程。sys.dm_exec_requests

SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

DMV可讓您查看當前正在等待資源的進程。 請注意，運行此 DMV 需要用戶擁有管理員權(quán)限或?qū)嵗系?VIEW SERVER STATE 權(quán)限。sys.dm_os_waiting_tasks

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0
GO

• 活動監(jiān)視器

如果您更喜歡使用 SQL Server Management Studio (SSMS) 而不是執(zhí)行系統(tǒng)對象或存儲過程的查詢，則可以使用 SSMS 監(jiān)視工具 -活動監(jiān)視器，它允許您實時查看阻塞會話。

要查看服務器活動：

1. 在對象資源管理器中，右鍵單擊服務器實例并選擇活動監(jiān)視器。

2. 在儀表板上，展開“進程”窗格以查看所有活動會話。

3. 在暫停的會話旁邊，查看“阻止者”列中的值（它顯示導致阻止的會話 ID）。

屏幕截圖顯示了被阻止的（#58）會話和被阻止的（#62）會話。

報告

SSMS 還允許使用報告功能監(jiān)控阻塞事務。它可以生成顯示服務器實例上所有阻塞事務的報告。

要打開報告，請右鍵單擊要檢查阻止事務的實例名稱，然后選擇報告>標準報告>活動-所有阻止事務。

這將在新的 SQL 文檔中打開生成的報告。

我們已經(jīng)討論了阻塞的原因以及可能導致這些事件的情況。現(xiàn)在是時候探討如何最大限度地減少或避免 SQL Server 數(shù)據(jù)庫中的阻塞。

減少阻塞的策略

為了減少阻塞、提高并發(fā)性并增強整體性能，建議優(yōu)化查詢、使用適當?shù)乃饕⒈M量減少長時間運行的事務。以下是實現(xiàn)此目標的一些實際步驟：

• 僅檢索所需的列，而不是。SELECT *
• 在子句中使用帶有索引列的特定過濾器WHERE。
• 除非必要，否則請避免連接很多桌子。
• 將大型查詢（例如更新和刪除）分解為較小的批次。
• 在經(jīng)常查詢的列或僅包含查詢需要的列上創(chuàng)建索引。
• 定期重建或重新組織索引以保持其高效。
• 保持事務簡短以便快速釋放鎖。
• 在交易打開時避免用戶輸入。
• 使用READ COMMITTED或SNAPSHOT隔離級別來最小化鎖定。
• SERIALIZABLE除非必要，否則請避免使用高隔離級別，例如。
• 盡可能使用 ROWLOCK 或 PAGLOCK 來減少鎖的范圍。
• 除非批處理操作需要，否則請避免使用 TABLOCK。
• 使用監(jiān)控工具或存儲過程查詢（例如）。sys.dm_exec_requests
• 在必要時識別并終止長期運行或卡住的交易。

SQL Server 中的死鎖是什么？

與阻塞相反，死鎖是指并發(fā)事務因每個事務都持有其他事務所需的資源的鎖并等待其他事務解鎖該資源而陷入停滯。因此，這會創(chuàng)建一個依賴循環(huán)，并且該過程可能需要無限長的時間。在這種情況下，所有事務都無法繼續(xù)，直到 SQL Server 因錯誤而中止一個事務，讓其他事務完成。

例如，交易試圖同時在兩個賬戶之間轉(zhuǎn)移資金。

• 交易 A正在將資金從帳戶 1轉(zhuǎn)移到帳戶 2。
• 交易 B正在將資金從Account2轉(zhuǎn)移到Account1。

如果兩筆交易都嘗試以不同的順序鎖定賬戶，則可能會發(fā)生死鎖。

死鎖的常見原因

如上所述，當兩個或多個事務在依賴循環(huán)中互相等待，導致任何事務都無法繼續(xù)執(zhí)行時，就會發(fā)生死鎖。死鎖最常見的原因是資源順序沖突和高爭用。

• 資源順序沖突

死鎖通常是因為對多個表或資源的查詢沒有遵循一致的鎖定順序而發(fā)生的。例如，事務 A鎖定資源 X，然后嘗試鎖定資源 Y。與此同時，事務 B鎖定資源 Y，然后嘗試鎖定資源 X。因此，每個事務都會等待對方釋放其鎖定，從而導致死鎖。

• 共享資源競爭激烈

另一個原因是，由于頻繁更新行、長時間運行的查詢或長時間持有鎖的事務可能會出現(xiàn)死鎖。此外，如果大量行級鎖轉(zhuǎn)換為單個表級鎖，也可能會出現(xiàn)死鎖。

僵局該如何解決？

SQL Server有一個內(nèi)置機制——鎖監(jiān)視線程——可以自動識別和解決死鎖以維持系統(tǒng)穩(wěn)定性。

SQL Server 數(shù)據(jù)庫引擎會定期在后臺搜索可能存在死鎖的事務。檢測到死鎖后，SQL Server 會根據(jù)事務成本或死鎖優(yōu)先級確定哪個事務是“受害者”，并可終止該事務。例如，成本最低的事務將被選為受害者，因為放棄該事務對系統(tǒng)性能的影響最小。

至于死鎖優(yōu)先級，默認情況下所有事務都具有相同的優(yōu)先級。但是，開發(fā)人員可以使用語句明確為事務分配死鎖優(yōu)先級，例如低、正常（默認狀態(tài)）或高SET DEADLOCK_PRIORITY。或者，開發(fā)人員可以將死鎖優(yōu)先級設置為范圍（-10 到 10）內(nèi)的任意整數(shù)值。

SET DEADLOCK_PRIORITY HIGH;

如果死鎖循環(huán)中的會話具有相同的死鎖優(yōu)先級和相同的成本，則 SQL Server 將隨機選擇一個犧牲者。如果沒有設置明確的優(yōu)先級，它將選擇成本最低的事務來終止。

死鎖“受害者”終止后，其事務將回滾。然后，SQL Server 釋放終止事務所持有的所有鎖，其他事務可以繼續(xù)進行。

防止死鎖的方法

到目前為止，我們已經(jīng)介紹了 SQL Server 中死鎖發(fā)生的原因和方式，以及如何解決死鎖。雖然無法完全防止死鎖，但我們至少可以盡量減少 SQL Server 中的死鎖。以下是一份簡短的清單，可能有助于在使用 SQL 數(shù)據(jù)庫時減少死鎖：

• 以一致的順序訪問資源。
• 將大型交易分解為較小的單位。
• 避免事務內(nèi)的用戶交互。
• 使用適當?shù)乃饕退璧淖畹透綦x級別。
• 避免使用過于嚴格的級別，例如 SERIALIZABLE，除非它們是必需的。
• 監(jiān)控并優(yōu)化查詢性能。

阻塞和死鎖之間的主要區(qū)別

總而言之，關(guān)鍵的區(qū)別在于死鎖是一種惡性循環(huán)，其中兩個或多個進程通過持有其他進程所需的資源而相互阻塞，從而阻止所有進程繼續(xù)運行。相反，阻塞是指一個進程持有另一個進程所需的資源，導致被阻塞的進程等待，直到阻塞進程完成其操作。

• 概念差異

該表顯示了 SQL Server 中阻塞和死鎖之間的概念差異。

• 交易影響

雖然阻塞是 SQL Server 并發(fā)控制的標準操作，但當阻塞持續(xù)時間過長或頻繁發(fā)生時，它會極大地影響性能。等待資源的事務將保留在隊列中，這可能會延遲其完成并降低整體系統(tǒng)性能。此外，如果阻塞事務涉及長時間運行的查詢或打開的事務，則可能會導致級聯(lián)延遲，從而影響多個事務，并進一步降低數(shù)據(jù)庫性能。

另一方面，死鎖對性能的影響更為嚴重，因為它們會導致一個或多個事務失敗。發(fā)生死鎖時，SQL Server 會檢測循環(huán)依賴并終止其中一個事務（將其視為死鎖犧牲品），以允許其他事務繼續(xù)進行。這種回滾浪費了處理時間和資源，因為必須重試失敗的事務。因此，死鎖會延遲事務并影響系統(tǒng)可靠性和用戶體驗。

• 檢測和解決技術(shù)

下表總結(jié)了SQL Server中阻塞和死鎖之間的檢測和解決技術(shù)。

盡管如此，如果數(shù)據(jù)庫管理員使用正確的工具和策略進行檢測和解決，他們可以減少阻塞和死鎖的影響并提高性能。

使用 dbForge Studio for SQL Server 分析鎖定問題

dbForge Studio for SQL Server 是一款用于數(shù)據(jù)庫開發(fā)、管理和維護的終極SQL Server IDE 。這個功能豐富的工具集讓用戶可以從單個界面執(zhí)行不同的數(shù)據(jù)庫相關(guān)操作。除了數(shù)據(jù)庫設計器、SQL 編輯器、查詢生成器、模式/數(shù)據(jù)比較工具外，dbForge Studio 還提供用于跟蹤和檢測阻塞問題的高級監(jiān)視器、用于實時跟蹤事件和查詢的事件分析器以及用于優(yōu)化數(shù)據(jù)庫性能的查詢分析器。

Monitor是一款終極監(jiān)控工具，專注于 SQL Server 數(shù)據(jù)庫的實時監(jiān)控和性能分析。它可以幫助數(shù)據(jù)庫管理員和開發(fā)人員識別和解決問題，例如查詢速度慢、會話阻塞和資源使用效率低下等。

讓我們展示使用 SELECT 查詢和監(jiān)視器檢測死鎖的示例。

打開 Studio。在SQL工具欄上，選擇New SQL打開一個新的 SQL 文檔。然后執(zhí)行以下腳本創(chuàng)建一個測試表，在其中插入數(shù)據(jù)，開始事務并鎖定表行。

-- Create a test table
CREATE TABLE DeadlockTest (
    ID INT PRIMARY KEY,
    Value NVARCHAR(50)
); 

-- Populate the table with data
INSERT INTO DeadlockTest (ID, Value)
VALUES (1, 'A'), (2, 'B');

-- Begin transaction
BEGIN TRANSACTION;

-- Lock the #1 row
UPDATE DeadlockTest SET Value = 'X' WHERE ID = 1;

-- Enable delay
WAITFOR DELAY '00:00:05';

-- Lock the #2 row
UPDATE DeadlockTest SET Value = 'Y' WHERE ID = 2;

要繼續(xù)，請打開另一個 SQL 文檔并執(zhí)行以下腳本。它將運行顯式事務，這意味著所有后續(xù)操作都是單個事務的一部分。請注意，在執(zhí)行COMMIT或之前，所做的更改不會提交或?qū)ζ渌麜捒梢姟OLLBACK

BEGIN TRANSACTION;

-- Lock the #2 row
UPDATE DeadlockTest
SET Value = 'Z'
WHERE ID = 2;

-- Enable delay
WAITFOR DELAY '00:00:05';

-- Lock the #1 row 
UPDATE DeadlockTest
SET Value = 'W'
WHERE ID = 1;

該事務包括以下操作：

• DeadlockTestUPDATE 語句更新表中的記錄。ID = 2
• WAITFOR DELAY '00:00:05';在交易中引入 5 秒的暫停，模擬延遲，在此期間記錄 ( ) 上的鎖保持活動狀態(tài)。ID = 2
• 第二條 UPDATE 語句嘗試更新記錄。ID = 1

現(xiàn)在，打開一個新的 SQL 文檔并執(zhí)行以下 SELECT 查詢，通過識別被其他會話阻止的活動請求來幫助檢測 SQL Server 中的阻止會話：

SELECT 
    r.session_id AS BlockingSessionID,
    r.blocking_session_id AS BlockedSessionID,
    t.text AS QueryText
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

在哪里：

• sys.dm_exec_requests是一個動態(tài)管理視圖，顯示有關(guān)在 SQL Server 中執(zhí)行的所有活動請求的詳細信息。每個請求代表 SQL Server 正在處理的一個特定操作，例如查詢或存儲過程。
• blocking_session_id > 0表示所代表的會話被另一個會話阻止。r.session_id

該查詢返回持有資源的阻塞會話的 ID 和被阻塞會話的 ID。該查詢還檢索被阻塞會話執(zhí)行的查詢的 SQL 文本。它有助于識別哪個查詢是阻塞查詢。

請注意，我們通過SampleDB數(shù)據(jù)庫過濾了結(jié)果以提高可讀性。

要解決鎖，請使用KILL LOCK或KILL LOCK SESSION查詢：

• 對于阻塞事務，執(zhí)行ROLLBACK命令
• 或者打開一個新的 SQL 文檔并執(zhí)行KILL id_number查詢，其中id_number是阻塞會話的 ID。

如果要查看與死鎖相關(guān)的事件，請使用dbForge Studio 的 SQL Server 分析功能。它旨在實時監(jiān)控和分析 SQL Server 事件和查詢。該工具有助于排除性能問題、優(yōu)化查詢并了解 SQL Server 引擎如何處理請求。

您可以在“配置文件服務器事件”向?qū)е性O置服務器事件的配置文件。要打開它，請在數(shù)據(jù)庫資源管理器中右鍵單擊連接并選擇任務>配置文件服務器事件。

在向?qū)У摹耙东@的事件”頁面上，選擇要捕獲的死鎖事件，然后選擇“執(zhí)行”。

為了方便搜索，您可以在向?qū)в疑辖堑乃阉鳈谥休斎雂eadlock 。

Event Profiler會根據(jù)配置的選項實時顯示信息。

如果你需要優(yōu)化查詢性能，那么最好的工具就是查詢分析器。它提供了查詢執(zhí)行計劃的詳細可視化表示，包括鎖定行為和資源使用情況。

例如，查詢分析器的“等待統(tǒng)計”選項卡可幫助您了解哪些事件導致了延遲以及查詢執(zhí)行期間資源被占用了多長時間。

對于長時間運行的查詢，Query Profiler 可讓用戶直觀地查看耗時操作，并識別導致性能不佳的 SQL 代碼的特定部分。此外，用戶可以比較查詢分析結(jié)果，以跟蹤一段時間內(nèi)的改進或退步。

結(jié)論

在本文中，我們探討了了解和管理阻塞和死鎖對于保持最佳數(shù)據(jù)庫性能和正確的事務處理的重要性。如果管理不當，阻塞可能會導致很大的延遲。然而，死鎖可能會產(chǎn)生嚴重的影響，并可能完全暫停事務，需要 SQL Server 來解決它們。我們還提供了檢測和解決技術(shù)，以使用查詢和 dbForge Studio for SQL Server 大限度地減少阻塞和死鎖對數(shù)據(jù)庫的影響。除了其他高級功能和工具外，后者是優(yōu)化查詢、監(jiān)控和分析鎖的不錯選擇。