在數據庫性能優化方面，數據庫管理員或開發人員必須了解 SQL Server 中阻塞和死鎖之間的區別，因為這些概念通常需要澄清。阻塞和死鎖有助于在處理并發事務時處理對共享資源的訪問。但是，管理不當或對其行為缺乏了解可能會導致性能問題，例如處理事務失敗和延遲。

在本文中，我們將探討 SQL Server 中阻塞和死鎖的基礎知識以及這兩個概念之間的區別。我們還將討論防止阻塞問題和死鎖的場景和方法。最后，我們將研究如何使用 dbForge Studio for SQL Server（試用下載）監視和解決阻塞和死鎖問題。

阻塞和死鎖簡介

在任何數據庫管理系統中，鎖都可用于在并發訪問數據庫期間維護數據完整性。因此，控制并發事務至關重要。然而，鎖的使用不當可能會帶來諸如阻塞和死鎖等問題，這會極大地影響數據庫性能并導致數據庫操作失敗。

阻塞和死鎖是幫助協調對共享資源的訪問并確保事務一致性的鎖定策略。

因此，對于數據庫管理員和開發人員來說，深入了解這些機制非常重要。妥善處理阻塞和死鎖可以優化數據庫性能，同時保持數據的完整性和一致性。

SQL Server 中的阻塞是什么？

在 SQL Server 中，阻塞是指一個進程占用另一個進程所需的資源。在這種情況下，下一個進程必須等待資源可用。SQL Server 一次只允許一個進程使用資源，以保持數據的準確性和一致性。雖然阻塞是數據庫中的預期行為，但其較長的等待時間會降低性能并導致延遲。

例如，兩個事務（和）試圖訪問表中的同一行。想要更新一行但尚未提交，因此它對該行持有鎖定。 同時，嘗試讀取此行但必須等到釋放其鎖定。 因此，在提交或回滾后，鎖定將被解除，并且可以繼續并返回結果。

1Transaction 2AccountsTransaction 1Transaction 2Transaction 1Transaction 1Transaction 2

堵塞的常見原因

以下是一些可能導致 SQL Server 阻塞的情況：

• 資源爭用：多個事務同時以相互沖突的方式訪問同一資源。例如，事務 1更新一條記錄并持有 鎖，而事務 2嘗試讀取或更新同一條記錄，并且必須等到事務 1完成。
• 長時間運行的事務：當某個事務占用資源的時間過長，從而導致其他事務等待時。例如，您使用復雜的查詢或錯誤地將事務保持打開狀態（例如未使用COMMIT或 ）ROLLBACK。
• 缺乏適當的索引：當缺少適當的索引時，查詢可能需要全表掃描或頁面級鎖。
• 過度使用鎖：當事務影響大量數據時，它可能會從鎖定個別行或頁面轉變為鎖定整個表。
• 顯式鎖定提示：開發人員可能會使用顯式鎖定提示，例如HOLDLOCK或TABLOCK，這會導致一些鎖。
• 應用程序邏輯設計不良：由于事務管理不善或客戶端處理延遲，應用程序可能會不必要地打開事務，或者在事務保持活動狀態時使事務保持打開狀態的時間比需要的時間長。
• 事務隔離級別：使用高隔離級別（例如SERIALIZABLE）可以增加鎖定和阻塞，因為它強制執行更嚴格的訪問規則，例如防止幻讀。

檢測 SQL Server 中的阻塞

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

• 系統存儲過程

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

EXEC sp_who2;

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

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

• 動態管理視圖 (DMV)

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

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

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

DMV可讓您查看當前正在等待資源的進程。 請注意，運行此 DMV 需要用戶擁有管理員權限或實例上的 VIEW SERVER STATE 權限。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

• 活動監視器

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

要查看服務器活動：

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

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

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

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

報告

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

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

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

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

減少阻塞的策略

為了減少阻塞、提高并發性并增強整體性能，建議優化查詢、使用適當的索引并盡量減少長時間運行的事務。以下是實現此目標的一些實際步驟：

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

SQL Server 中的死鎖是什么？

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

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

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

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

死鎖的常見原因

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

• 資源順序沖突

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

• 共享資源競爭激烈

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

僵局該如何解決？

SQL Server有一個內置機制——鎖監視線程——可以自動識別和解決死鎖以維持系統穩定性。

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

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

SET DEADLOCK_PRIORITY HIGH;

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

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

防止死鎖的方法

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

• 以一致的順序訪問資源。
• 將大型交易分解為較小的單位。
• 避免事務內的用戶交互。
• 使用適當的索引和所需的最低隔離級別。
• 避免使用過于嚴格的級別，例如 SERIALIZABLE，除非它們是必需的。
• 監控并優化查詢性能。

阻塞和死鎖之間的主要區別

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

• 概念差異

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

• 交易影響

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

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

• 檢測和解決技術

下表總結了SQL Server中阻塞和死鎖之間的檢測和解決技術。

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

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

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

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

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

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

-- 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;

要繼續，請打開另一個 SQL 文檔并執行以下腳本。它將運行顯式事務，這意味著所有后續操作都是單個事務的一部分。請注意，在執行COMMIT或之前，所做的更改不會提交或對其他會話可見。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;

該事務包括以下操作：

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

現在，打開一個新的 SQL 文檔并執行以下 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是一個動態管理視圖，顯示有關在 SQL Server 中執行的所有活動請求的詳細信息。每個請求代表 SQL Server 正在處理的一個特定操作，例如查詢或存儲過程。
• blocking_session_id > 0表示所代表的會話被另一個會話阻止。r.session_id

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

請注意，我們通過SampleDB數據庫過濾了結果以提高可讀性。

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

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

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

您可以在“配置文件服務器事件”向導中設置服務器事件的配置文件。要打開它，請在數據庫資源管理器中右鍵單擊連接并選擇任務>配置文件服務器事件。

在向導的“要捕獲的事件”頁面上，選擇要捕獲的死鎖事件，然后選擇“執行”。

為了方便搜索，您可以在向導右上角的搜索欄中輸入deadlock 。

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

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

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

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

結論

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