您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server Session 阻塞診斷與解除完整指南

編輯：關於SqlServer

阻塞是資料庫高併發環境常見問題。當一個 Session 持有鎖定資源，導致其他 Session 無法存取同一資源時即發生阻塞。本文以實戰範例示範如何建立測試環境、查詢阻塞鏈、三種解除方案，並說明根本預防之道。

一、阻塞基礎概念

阻塞 vs 死鎖：阻塞為單向等待，死鎖為循環等待（SQL Server 會自動選擇犧牲者終止）。
常見原因：長時間交易未提交、缺少索引導致掃描鎖定大量資料列、隔離層級過高、應用程式邏輯設計不當。

二、實戰重現阻塞場景

1. 建立測試表與資料

CREATE TABLE Employee (
    Empid INT NOT NULL, Name NCHAR(10) NULL, City NCHAR(10) NULL
);
INSERT INTO Employee VALUES
(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'),
(1175,'Pete','Topeka'), (875,'Petron','Vienna'), (2311,'Kohli','Mumbai'),
(1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'),
(957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), (2954,'Ganeshan','Mcclean');

2. Session A：開啟交易插入資料，不提交（持有排他鎖）

BEGIN TRAN
INSERT INTO Employee VALUES (1245,'George','Jax');
-- 故意不執行 COMMIT/ROLLBACK，交易保持開啟

3. Session B：嘗試更新同一資料列，發生阻塞

UPDATE Employee SET Name = 'SHERAZ' WHERE Empid = 1245;
-- 此處會卡住等待 Session A 釋放鎖

三、查詢阻塞資訊

1. 查看目前執行中 Session

SELECT DISTINCT
    DB_NAME(dtl.resource_database_id) AS database_name,
    des.session_id, des.host_name, des.login_time, des.login_name,
    des.reads, des.writes
FROM sys.dm_exec_sessions des
LEFT OUTER JOIN sys.dm_tran_locks dtl ON des.session_id = dtl.request_session_id
INNER JOIN sys.databases sd ON dtl.resource_database_id = sd.database_id
WHERE dtl.resource_type <> 'DATABASE'
ORDER BY database_name;

2. 查詢阻塞鏈（誰阻塞誰）

SELECT
    der.session_id,
    der.blocking_session_id,
    dest.text AS sql_text
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest;

結果顯示 blocking_session_id = 54 阻塞 session_id = 58。

四、三種解除方案

方案 1：KILL 阻塞 Session（緊急止損）

KILL 54;  -- 強制終止 Session 54，釋放鎖定

⚠️ 會導致該 Session 交易回滾，可能造成資料不一致，需確認業務影響後再執行。

方案 2：設定 LOCK_TIMEOUT（應用層自我保護）

SET LOCK_TIMEOUT 1000;  -- 超過 1000ms 自動終止等待，回傳錯誤 1222

優點：避免無限期等待；缺點：需應用程式捕捉錯誤並重試。

方案 3：正常提交或回滾交易（根本解決）

在 Session A 執行 COMMIT TRAN 或 ROLLBACK TRAN，釋放鎖定，Session B 即可繼續。

五、完整測試腳本彙總

/**** 建立測試表 ****/
CREATE TABLE Employee (Empid INT NOT NULL, Name NCHAR(10) NULL, City NCHAR(10) NULL);
INSERT INTO Employee VALUES
(1245,'George','Jax'), (1045,'Peter','Anadale'), (1157,'John','Dallas'),
(1175,'Pete','Topeka'), (875,'Petron','Vienna'), (2311,'Kohli','Mumbai'),
(1547,'Peter','Kansas'), (3514,'Abian','KHI'), (4251,'Ghani','Alexandria'),
(957,'Ahmed','Vienna'), (1084,'Bhanu','Manderin'), (2954,'Ganeshan','Mcclean');

/**** Session A：開啟交易插入不提交 ****/
BEGIN TRAN
INSERT INTO Employee VALUES (1245,'George','Jax');

/**** 查詢目前 Session ****/
SELECT DISTINCT DB_NAME(dtl.resource_database_id) AS database_name, des.session_id,
       des.host_name, des.login_time, des.login_name, des.reads, des.writes
FROM sys.dm_exec_sessions des
LEFT OUTER JOIN sys.dm_tran_locks dtl ON des.session_id = dtl.request_session_id
INNER JOIN sys.databases sd ON dtl.resource_database_id = sd.database_id
WHERE dtl.resource_type <> 'DATABASE' ORDER BY database_name;

/**** Session B：更新同一資料列（將阻塞） ****/
UPDATE Employee SET Name = 'SHERAZ' WHERE Empid = 1245;

/**** 查詢阻塞鏈 ****/
SELECT der.session_id, der.blocking_session_id, dest.text
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest;

/**** 解除：KILL 阻塞者 ****/
KILL 54;