檢測 SQL Server 中的阻塞

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

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

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

EXEC sp_who2;

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

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

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

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

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

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

DMV可讓您查看當(dāng)前正在等待資源的進(jìn)程。 請(qǐng)注意，運(yùn)行此 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

• 活動(dòng)監(jiān)視器

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

要查看服務(wù)器活動(dòng)：

1. 在對(duì)象資源管理器中，右鍵單擊服務(wù)器實(shí)例并選擇活動(dòng)監(jiān)視器。

2. 在儀表板上，展開“進(jìn)程”窗格以查看所有活動(dòng)會(huì)話。

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

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

報(bào)告

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

要打開報(bào)告，請(qǐng)右鍵單擊要檢查阻止事務(wù)的實(shí)例名稱，然后選擇報(bào)告>標(biāo)準(zhǔn)報(bào)告>活動(dòng)-所有阻止事務(wù)。

這將在新的 SQL 文檔中打開生成的報(bào)告。

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

減少阻塞的策略

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

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

SQL Server 中的死鎖是什么？

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

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

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

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

死鎖的常見原因

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

• 資源順序沖突

死鎖通常是因?yàn)閷?duì)多個(gè)表或資源的查詢沒有遵循一致的鎖定順序而發(fā)生的。例如，事務(wù) A鎖定資源 X，然后嘗試鎖定資源 Y。與此同時(shí)，事務(wù) B鎖定資源 Y，然后嘗試鎖定資源 X。因此，每個(gè)事務(wù)都會(huì)等待對(duì)方釋放其鎖定，從而導(dǎo)致死鎖。

• 共享資源競爭激烈

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

僵局該如何解決？

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

SQL Server 數(shù)據(jù)庫引擎會(huì)定期在后臺(tái)搜索可能存在死鎖的事務(wù)。檢測到死鎖后，SQL Server 會(huì)根據(jù)事務(wù)成本或死鎖優(yōu)先級(jí)確定哪個(gè)事務(wù)是“受害者”，并可終止該事務(wù)。例如，成本最低的事務(wù)將被選為受害者，因?yàn)榉艞壴撌聞?wù)對(duì)系統(tǒng)性能的影響最小。

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

SET DEADLOCK_PRIORITY HIGH;

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

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

防止死鎖的方法

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

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

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

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

• 概念差異

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

• 交易影響

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

另一方面，死鎖對(duì)性能的影響更為嚴(yán)重，因?yàn)樗鼈儠?huì)導(dǎo)致一個(gè)或多個(gè)事務(wù)失敗。發(fā)生死鎖時(shí)，SQL Server 會(huì)檢測循環(huán)依賴并終止其中一個(gè)事務(wù)（將其視為死鎖犧牲品），以允許其他事務(wù)繼續(xù)進(jìn)行。這種回滾浪費(fèi)了處理時(shí)間和資源，因?yàn)楸仨氈卦囀〉氖聞?wù)。因此，死鎖會(huì)延遲事務(wù)并影響系統(tǒng)可靠性和用戶體驗(yàn)。

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

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

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

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

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

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

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

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

-- 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ù)，請(qǐng)打開另一個(gè) SQL 文檔并執(zhí)行以下腳本。它將運(yùn)行顯式事務(wù)，這意味著所有后續(xù)操作都是單個(gè)事務(wù)的一部分。請(qǐng)注意，在執(zhí)行COMMIT或之前，所做的更改不會(huì)提交或?qū)ζ渌麜?huì)話可見。ROLLBACK

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;

該事務(wù)包括以下操作：

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

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

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是一個(gè)動(dòng)態(tài)管理視圖，顯示有關(guān)在 SQL Server 中執(zhí)行的所有活動(dòng)請(qǐng)求的詳細(xì)信息。每個(gè)請(qǐng)求代表 SQL Server 正在處理的一個(gè)特定操作，例如查詢或存儲(chǔ)過程。
• blocking_session_id > 0表示所代表的會(huì)話被另一個(gè)會(huì)話阻止。r.session_id

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

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

要解決鎖，請(qǐng)使用KILL LOCK或KILL LOCK SESSION查詢：

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

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

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

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

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

Event Profiler會(huì)根據(jù)配置的選項(xiàng)實(shí)時(shí)顯示信息。

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

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

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

結(jié)論

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