了解並解決 SQL Server 封鎖問題

適用於：SQL Server、Azure SQL 受控執行個體

原始 KB 編號： 224453

總結

阻塞在任何基於鎖的關聯式資料庫中都很正常，但持續性或長期執行的阻塞會降低吞吐量並導致應用程式逾時。本文說明 SQL Server 中的阻擋是什麼，如何從動態管理視圖（DMV）和擴展事件擷取阻擋資料，以及如何辨識頭部阻擋器。接著介紹最常見的阻擋情境及其解決方法。

在本文中，連線一詞指的是資料庫中單一登入的會話。每個連線都以會話編號（SPID）形式出現，或 session_id 出現在許多車輛管理局（DMV）中。每個 SPID 通常被稱為「程序」，但它並非獨立的作業系統程序。它是伺服器端的資源和資料結構，負責服務一個客戶端的連線。單一用戶端應用程式可以有一個或多個連線。從 SQL Server 的角度來看，來自多個客戶端的連線與來自同一客戶端的多連接會被視為相同。無論來源用戶端為何，一個連線可以封鎖另一個連線。

注意

本文聚焦於 SQL Server 實例，包括 Azure SQL 受控執行個體。 如需針對 Azure SQL 資料庫封鎖進行疑難排解的特定資訊，請參閱瞭解並解決 Azure SQL 資料庫封鎖問題。

何謂封鎖

針對任何具備鎖定式並行處理功能的關聯式資料庫管理系統 (RDBMS)，封鎖是有無法避免且依據設計的特性。如先前所述，在 SQL Server 中，當一個工作階段持有特定資源的鎖定，但另一個 SPID 試圖對同一個資源取得衝突的鎖定類型時，便會發生封鎖的現象。一般來說，第一個 SPID 鎖定資源的時間範圍很小。當擁有的工作階段釋放鎖定時，第二個連線便可取得資源上其所屬的鎖定並繼續處理。如這裡所述封鎖是常見行為，一天當中可能發生多次，不會對系統效能產生明顯影響。

查詢的持續時間和交易內容會決定其鎖定保留的時間，以及對其他查詢的影響。如果查詢未在交易時間內執行（且未使用任何鎖定提示），則只有在實際上被讀取時，SELECT 陳述式才會鎖定在一個資源上，而非整個查詢期間均能持有。針對 INSERT、UPDATE 和 DELETE 陳述式，會在查詢期間保留鎖定，以確保資料一致性並視需要允許復原查詢。

對於在交易中執行的查詢，查詢類型、交易隔離層級，以及查詢中是否使用鎖提示，決定了鎖被保留的時間長短。如需鎖定、鎖定提示和交易隔離等級的說明，請參閱下列文章：

當鎖定與阻擋持續到對系統效能造成不利影響的程度時，其原因通常如下：

某個 SPID 持有一組資源的鎖定相當久後才釋放資源。這種類型的封鎖會在一段時間內自行解決，但可能會導致效能降低。
某個 SPID 持有一組資源的鎖定且不釋放。這種類型的封鎖無法自行解決，並可無限期防止存取受影響的資源。

在第一個案例中，情況可能會有所不同，因為 SPID 會長時間造成不同資源的鎖定並建立移動目標。這些情況不容易進行疑難排解，請使用 SQL Server Management Studio 將問題縮小為個別查詢。相比之下，第二個情況是在一致狀態下所導致，可較容易診斷。

應用程式和封鎖

遇到阻擋問題時，你可以專注於伺服器端的調校和平台問題。然而，僅關注資料庫可能無法解決問題。它能節省時間與精力，而這些時間本可用於檢視客戶申請及其提交的查詢。無論應用程式對其資料庫呼叫的可視性程度如何，阻塞問題通常需要同時檢查應用程式所提交的精確 SQL 語句，以及應用程式在查詢取消、連線管理、擷取所有結果列等行為上的精確行為。如果開發工具不允許明確控制連線管理、查詢取消、查詢逾時、結果擷取等，你可能無法解決阻塞問題。在選擇 SQL Server 應用程式開發工具前，特別是針對效能敏感的 OLTP 環境，請仔細檢視此潛力。

請在設計期間及資料庫和應用程式建構階段中，注意資料庫效能。特別是，評估每個查詢的資源消耗、隔離層級及交易路徑長度。每個查詢和交易應盡可能輕量。保持良好的人際關係管理紀律。若無此功能，應用程式在低用戶數下可能表現可接受，但隨著使用者數量增加，效能可能會大幅下降。

透過適當的應用程式與查詢設計，SQL Server 能在單一伺服器上同時支援數千名使用者，且幾乎沒有阻擋。

疑難排解封鎖

無論你處於哪種阻擋情況，排除鎖定問題的方法都是一樣的。這些邏輯上的分隔決定了本文其餘的寫作內容。概念是找出前端封鎖程式，並識別該查詢的執行項目和封鎖原因。一旦你找出有問題的查詢（也就是長期鎖住的原因），下一步就是分析並找出阻塞的原因。了解原因後，可以透過重新設計查詢和交易來做修改。

疑難排解的步驟：

找出主要的阻擋時段（頭部阻擋者）。
找出導致阻塞的查詢和交易（也就是那些能長時間鎖起來的）。
分析並理解為何會發生長時間阻塞。
透過重新設計查詢與交易來解決阻塞問題。

現在我們將深入探討如何使用適當的資料擷取指出主要封鎖工作階段。

收集封鎖資訊

為了消除疑難排解封鎖問題的困難性，資料庫管理員可以使用 SQL 指令碼，持續監視 SQL Server 上的鎖定和封鎖狀態。若要收集此資料，有兩個免費的方法。

第一種方法是查詢動態管理物件（DMO），並儲存結果以便隨時間比較。本文所參考的部分物件為動態管理檢視 (DMV)，有些是動態管理函式 (DMF)。

第二種方法是使用擴展事件（XEvents）或 SQL Profiler Traces 來擷取正在執行的內容。由於 SQL Trace 和 SQL Server Profiler 已經被棄用，本故障排除指南將聚焦於 XEvents。

收集 DMV 的資訊

要排除阻塞問題，請使用 DMV 來識別阻塞鏈頂端的 SPID（會話 ID）及 SQL 語句。尋找被封鎖的受害者SPID。如果一個 SPID 被另一個 SPID 阻擋，請調查擁有該資源的 SPID（阻擋 SPID）。那位擁有者的 SPID 也被封鎖了嗎？你可以沿著鏈條找到頭部阻擋器，然後調查它為何能保持鎖住。

若要解決此問題，請使用下列其中一種方法：

在 SQL Server Management Studio (SSMS) 物件總管中，以滑鼠右鍵按一下最上層伺服器物件，展開 [報告]，展開 [標準報告]，然後選取 [活動 – 所有封鎖交易]。此報表顯示封鎖前端封鎖鏈的目前交易。如果您展開交易，報表會顯示前端交易封鎖的交易。此報告也會顯示封鎖 SQL 聲明和封鎖 SQL 聲明。
在 SSMS 中開啟活動監控，並參考 「阻擋者」 欄位。欲了解更多資訊，請參閱活動監測器。

您也可以使用 DMVs 來使用更詳細的查詢方法：

sp_who and sp_who2 指令是較舊的指令，顯示所有目前的工作階段。 DMV sys.dm_exec_sessions 會回傳更多資料，結果集會比較容易查詢和篩選。您會在其他查詢的核心找到 sys.dm_exec_sessions。
如果你已經識別了某個特定會話，請用來 DBCC INPUTBUFFER(<session_id>) 查找該會話最後提交的語句。動態管理函數（DMF）也會回傳 sys.dm_exec_input_buffer 類似的結果，結果集更易查詢與篩選，提供session_id與request_id。例如，若要傳回 session_id 66 和 request_id 0 所提交的最近查詢：

SELECT * FROM sys.dm_exec_input_buffer (66,0);

請參閱 sys.dm_exec_requests 並參考 blocking_session_id 資料欄位。當 = 0 時 blocking_session_id ，該會話不會被阻塞。雖然 sys.dm_exec_requests 只列出目前正在執行的請求，但 sys.dm_exec_sessions 會列出任何連線（不論是否啟用）。在下一個查詢中，請建立 sys.dm_exec_requests 和 sys.dm_exec_sessions 之間的通用聯結。請記住，要被返回sys.dm_exec_requests，查詢必須在 SQL Server 中積極執行。
執行此範例查詢，以使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV，尋找正在執行的查詢和其目前 SQL 批次文字和輸入緩衝區文字。如果傳回 text 資料欄位 sys.dm_exec_sql_text 的資料為 NULL，則目前沒有在執行查詢。在此情況下， event_info 資料欄位 sys.dm_exec_input_buffer 會包含傳遞至 SQL 引擎的最後一個命令字串。此查詢也可用於識別封鎖其他工作階段的工作階段，包含每 session_id 封鎖的 session_id 清單。

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

執行此更詳盡的範本查詢 (由 Microsoft 支援服務提供)，識別多個工作階段封鎖鏈的前端，包含涉及封鎖鏈的工作階段查詢文字。

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

若要攔截長時間執行或未認可的交易，請使用另一組 DMVs 來檢視目前開啟的交易，包括 sys.dm_tran_database_transactions、 sys.dm_tran_session_transactions、 sys.dm_exec_connections，和 sys.dm_exec_sql_text。想了解更多與交易追蹤相關的 DMV，請參見 DMV on Transaction。

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

參考sys.dm_os_waiting_tasks，那是SQL Server的執行緒/任務層。該 DMV 會回傳請求目前遇到的 SQL wait_type 資訊。例如 sys.dm_exec_requests，sys.dm_os_waiting_tasks 僅會傳回作用中的要求。

注意

欲了解更多等待類型資訊，包括隨時間累積的等待統計，請參閱車輛管理局（DMV） sys.dm_db_wait_stats。

使用 Sys.dm_tran_locks DMV，取得查詢所放置鎖定項目的更細微資訊。此 DMV 可以從生產型 SQL Server 執行個體傳回大量資料，有助針對目前保留的鎖定進行診斷。

由於 sys.dm_os_waiting_tasks 上有 INNER JOIN，下列查詢僅會將 sys.dm_tran_locks 的輸出限制為目前封鎖的要求、其等候狀態和鎖定：

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

藉由 DMV，隨時間儲存查詢結果將提供資料點，讓您檢閱特定時間間隔的封鎖以識別持續性的封鎖或趨勢。 CSS 針對這類問題進行疑難排解的 go-to tool 是使用 PSSDiag 資料收集器。此工具會使用「SQL Server Perf Stats」來收集上述 DMV 中經過一段時間的結果集。隨著此工具不斷演進，請檢閱 GitHub 上最新公開版本的DiagManager。

從擴充事件收集資訊

除了前述資訊外，你通常還需要擷取伺服器上活動的痕跡，以徹底調查 SQL Server 中的阻塞問題。例如，若一個會話在交易中執行多個語句，則僅表示該工作階段最後提交的語句。然而，早期的說法之一可能是鎖仍被保留的原因。追蹤讓你可以看到該會話在當前交易中執行的所有指令。

你可以透過兩種方法在 SQL Server 中擷取追蹤：擴展事件（XEvents）和分析器追蹤。然而，使用 SQL Server Profiler 的 SQL 軌跡已被棄用。 XEvents 是更新且性能優越的追蹤平台，提供更多彈性且對系統的影響較小。其介面整合於 SSMS。

在物件總管的 XEvent Profiler 選單下，你可以找到預先製作好的 Extended Event session，這些會話已經準備好在 SSMS 中開始。如需詳細資訊，請參閱 XEvent Profiler。你也可以在 SSMS 中建立自訂的延伸事件會話。欲了解更多資訊，請參閱擴展事件新會議向導。在排解阻塞問題時，通常會擷取：

類別錯誤：
- 注意
- Blocked_process_report**
- Error_reported (通道管理員)
- Exchange_spill
- Execution_warning

**若要設定產生封鎖進程報告的臨界值和頻率，請使用 sp_configure 命令來設定可在幾秒內設定的封鎖進程臨界值選項。預設不會針對已封鎖的處理序產生任何報告。

類別警告：
- Hash_warning
- Missing_column_statistics
- Missing_join_predicate
- Sort_warning
類別執行：
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
類別鎖定
- Lock_deadlock
類別工作階段
- Existing_connection
- 登入
- Logout

識別並解決常見封鎖案例

透過檢視上述資訊，你可以判斷大多數阻塞問題的原因。本文其餘部分將討論如何利用這些資訊來識別並解決一些常見的阻塞情境。本討論假設你使用前述的阻塞腳本來擷取阻塞 SPID 的資訊，並透過 XEvent 會話捕捉應用程式活動。

分析封鎖資料

檢查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的輸出，以使用 blocking_these 和 session_id 判斷封鎖鏈的前端。此輸出清楚標示哪些請求被阻擋，哪些請求被阻擋。進一步了解已封鎖和封鎖中的工作階段。封鎖鏈是否有通用項目或根？兩者可能共用通用的資料表，且涉及封鎖鏈的一或多個工作階段正在執行寫入作業。

檢查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的輸出，以取得位於封鎖鏈前端的 SPID 資訊。請尋找下列欄：

sys.dm_exec_requests.status

此資料行顯示特定要求的狀態。通常，睡眠狀態表示 SPID 已完成執行，正在等待應用程式提交另一個查詢或批次。可執行或正在執行的狀態表示 SPID 目前正在處理查詢。下表提供各種狀態值的簡短說明。

狀態	意義
背景	SPID 正在執行背景工作，例如鎖死偵測、記錄檔寫入器或檢查點。
休眠中	SPID 目前未執行。這通常表示 SPID 正在等候應用程式的命令。
執行中	SPID 目前正在排程器上執行。
可執行的	SPID 位於排程器的可執行佇列中，並正在等候取得排程器時間。
暫止	SPID 正在等候資源，例如鎖定或閂鎖。

sys.dm_exec_sessions.open_transaction_count

此欄顯示本會話中未完成交易的數量。若此值大於 0，表示 SPID 屬於未完成交易，可能持有交易中任何語句所取得的鎖。未開啟的交易可能是由目前有效的對帳單產生，或是過去執行過但現在不再活躍的對帳單請求所建立。
sys.dm_exec_requests.open_transaction_count

同樣地，此欄位顯示此請求中未完成的交易數量。若此值大於 0，表示 SPID 屬於未完成交易，可能持有交易中任何活躍語句所取得的鎖。與 sys.dm_exec_sessions.open_transaction_count不同，若沒有啟用請求，該欄顯示 0。
sys.dm_exec_requests.wait_type、wait_time 和 last_wait_type

如果是 sys.dm_exec_requests.wait_type NULL，表示請求目前沒有等待任何東西。該 last_wait_type 值表示請求最後 wait_type 遇到的幾點。如需 sys.dm_os_wait_stats 和最常見等候類型說明的詳細資訊，請參閱 sys.dm_os_wait_stats。 wait_time 值可用於判斷要求的進展程度。當對 sys.dm_exec_requests 資料表的查詢回傳的欄位 wait_time 值小 wait_time 於先前查詢 sys.dm_exec_requests的值時，此條件表示先前鎖定已被取得並釋放，現在正等待新的鎖（假設非零 wait_time）。你可以透過比較 wait_resource between sys.dm_exec_requests 輸出來驗證這個條件，該輸出顯示請求正在等待的資源。

sys.dm_exec_requests.wait_resource

此數據行指出封鎖要求正在等候的資源。下列資料表列出常見 wait_resource 格式和其意義：

資源	[格式]	範例	說明
資料表	DatabaseID:ObjectID:IndexID	TAB：5：261575970：1	在此情況下，資料庫識別碼 5 是 pubs 範例資料庫，而 `object_id` 261575970 是標題資料表，而 1 是叢集索引。
頁面	DatabaseID:FileID:PageID	頁數：5：1：104	在此案例中，資料庫識別碼 5 是 Pubs，檔案識別碼 1 是主要資料檔案，而頁面 104 是屬於標題資料表的頁面。若要識別頁面所屬的 object_id，則請使用動態管理函式 sys.dm_db_page_info 以傳入 `wait_resource` 的 DatabaseID、FileId、PageId。
答案	DatabaseID:Hobt_id (索引鍵的雜湊值)	鑰匙：5：72057594044284928（3300a4f361aa）	在此案例中，資料庫識別碼 5 是 Pubs，Hobt_ID 72057594044284928 對應至 object_id 261575970 (標題資料表) 的 index_id 2。使用 `sys.partitions` 目錄檢視將 `hobt_id` 與特定 `index_id` 和 `object_id` 建立關聯。您無法將索引鍵雜湊值雜湊回特定的索引鍵值。
資料列	DatabaseID:FileID:PageID:Slot(row)	RID：5：1：104：3	在此案例中，資料庫識別碼 5 是 Pubs，檔案識別碼 1 是主要資料檔案，頁面 104 是屬於標題資料表的頁面，而資料列 3 表示資料列在頁面上的位置。

sys.dm_tran_active_transactions sys.dm_tran_active_transactions DMV 包含可聯結至其他 DMV 的未結案交易相關資料，用於全面了解等候認可或復原的交易。使用下列查詢，傳回已聯結至其他 DMV (包含 sys.dm_tran_session_transactions) 的未結案交易相關資訊。請考量交易的目前狀態、transaction_begin_time 和其他情境資料，以評估交易是否為封鎖的來源。

SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

其他資料行

Sys.dm_exec_sessions 和 sys.dm_exec_request 中的其餘資料行也可以提供問題根源的深入解析。其資料行的實用性會視問題的情況而有所不同。例如，您可以判斷問題是否只發生在特定用戶端 (hostname)、特定網路程式庫 (client_interface_name)、SPID 提交的最後一個批次是在 last_request_start_time 中的 sys.dm_exec_sessions、要求在 start_time 中執行 sys.dm_exec_requests 的時間長度等等。

常見的封鎖案例

下表將常見徵狀對應至可能的原因。

wait_type、open_transaction_count 與 status 資料欄代表 sys.dm_exec_request 所傳回的資訊，其他資料欄可由 sys.dm_exec_sessions傳回。「解決？」資料行表示封鎖是否自行解決，或是否應透過 KILL 命令來終止工作階段。如需詳細資訊，請參閱 KILL (Transact-SQL)。

案例	Wait_type	Open_Tran	狀態	解決？	其他徵狀
1	不可為空	>= 0	可執行的	是，當查詢完成時。	在 `sys.dm_exec_sessions`、`reads`、`cpu_time` 和/或 `memory_usage` 中，資料行會隨時間而增加。查詢的持續時間會在完成後變高。
2	NULL	>0	睡眠中	否，但可以終止 SPID。	在此 SPID 的擴充事件工作階段中可能會出現注意訊號，指出發生查詢逾時或取消情形。
3	NULL	>= 0	可執行的	否。在用戶端擷取所有資料列或關閉連線之前，將無法解決。可以終止 SPID，但可能最多需要 30 秒。	如果 open_transaction_count = 0，且在交易隔離等級為預設值時 (READ COMMITTED)，SPID 持有鎖定，則這可能是原因。
4	不定	>= 0	可執行的	否。在用戶端取消查詢或關閉連線之前，將無法解決。可以終止 SPID，但可能最多需要 30 秒。	針對位於封鎖鏈前端的 SPID，`hostname` 中的 `sys.dm_exec_sessions` 資料行會與其封鎖的其中一個 SPID 相同。
5	NULL	>0	回復	是。	在此 SPID 的擴充事件工作階段可能會顯示注意訊號，指出查詢逾時、取消或已發出復原陳述式。
6	NULL	>0	睡眠中	最終，當 Windows NT 判斷工作階段不再為使用中狀態，連線將會中斷。	`last_request_start_time` 中的 `sys.dm_exec_sessions` 值會早於目前時間。

詳細的封鎖案例

案例 1：查詢正常執行，但執行時間太長所造成的封鎖

在此案例中，主動執行的查詢已取得鎖定，且鎖定不會 (在受到交易隔離等級影響) 釋放。因此，其他工作階段會等候鎖定，直到釋放鎖定為止。

解決方法：

為了解決這個阻塞問題，請優化查詢。這種阻塞問題可能是效能問題。就這樣對待它。如需針對特定執行緩慢查詢進行疑難排解的詳細資訊，請參閱如何針對 SQL Server 上的執行緩慢查詢進行疑難排解。如需詳細資訊，請參閱監視及調整效能。

查詢存放區內建的 SSMS 報告（2016 SQL Server引入）也是高度推薦且有價值的工具，用以識別最昂貴的查詢與次優執行計畫。

如果你有一個長期執行的查詢阻擋其他使用者且無法優化，考慮將其從 OLTP 環境移到專用的報告系統。您也可以使用 Always On 可用性群組來同步處理資料庫的唯讀複本。

注意

查詢執行期間的封鎖可能是因為查詢擴大造成，在此情況下，資料列或頁面鎖定擴大為資料表鎖定。 Microsoft SQL Server 會依狀況動態判斷何時執行鎖定擴大。防止鎖定擴大最簡單且最安全的方式是保持交易簡短，並減少耗費大量資源的查詢之鎖定使用量，以免超過鎖定擴大閾值。如需詳細資訊以偵測及防止過度鎖定擴大，請參閱解決鎖定擴大所造成的封鎖問題。

案例 2：休眠中 SPID 有未認可的交易而造成的封鎖

你通常可以透過 SPID 在休眠或等待指令時，將交易巢狀層級（@@TRANCOUNT， open_transaction_count 從 sys.dm_exec_requests）大於零來識別這種阻塞。此狀況發生於下列情形：當應用程式遭遇查詢逾時，或提出取消但未提供必要的復原與 / 或認可陳述式號碼。當 SPID 收到查詢逾時或取消時，會終止目前的查詢和批次，但不會自動回滾或提交該交易。應用程式負責此操作，因為 SQL Server 不能假設單一查詢被取消就必須回滾整個交易。查詢逾時或取消會以 SPID 在擴展事件會話中的 ATTENTION 訊號事件形式出現。

若要示範未認可的明確交易，請發行下列查詢：

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

然後，在同一個視窗中執行此查詢：

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

第二筆查詢的輸出表示交易數目為一。在交易認可或復原之前，交易中取得的所有鎖定會持續保留。如果應用程式明確開啟並認可交易，則通訊或其他錯誤可能會讓工作階段和其交易處於未結案狀態。

請根據 sys.dm_tran_active_transactions 使用本文前述的指令碼，識別執行個體中目前未認可的交易。

解決方式：

這類阻擋問題也可能是效能問題。如果你能縮短查詢執行時間，查詢逾時或取消可能就不會發生。應用程式能夠處理逾時或取消情境很重要，但你也可以從檢視查詢的效能中受益。
應用程式必須妥善管理交易巢狀層級，否則在查詢取消後可能會造成阻塞問題。請考慮下列事項：
- 在用戶端應用程式的錯誤處理常式中，執行 IF @@TRANCOUNT > 0 ROLLBACK TRAN 以查看是否有任何錯誤，即使用戶端應用程式不認為交易處於未結案。請務必檢查交易是否開啟，因為批次期間呼叫的預存程序可能在用戶端應用程式不知情的情況下啟動了交易。特定條件 (例如取消查詢) 會讓程序無法超出執行目前陳述式，即使程序具有邏輯以檢查 IF @@ERROR <> 0 和終止交易，也不會在此類案例中執行此復原程式碼。
- 如果你在應用程式中使用連線池，該應用程式會先開啟連線並執行幾個查詢，然後再將連線釋放回池，暫時關閉連線池可能有助於緩解問題，直到用戶端應用程式被修改以適當處理錯誤。透過停用連線池，釋放連線會導致 SQL Server 連線物理斷開，導致伺服器回滾所有未完成的交易。
- 利用 SET XACT_ABORT ON 連線，或使用於任何開始交易且發生錯誤後未清除的預存程式。若發生執行錯誤，此設定會中止所有未完成的交易，並將控制權還給用戶端。如需詳細資訊，請參閱 SET XACT_ABORT (Transact-SQL)。

注意

在連線集區重新使用之前，連線不會重設，因此使用者可以開啟交易，然後將連線釋放到連線集區，但可能需等待數秒方可重新使用，在此期間交易會保持開啟狀態。如果連線未重新使用，當連線逾時且從連線集區移除後，交易便會中止。因此，最好由用戶端應用程式在其錯誤處理常式中止交易，或使用 SET XACT_ABORT ON 來避免潛在延遲。

警告

在 SET XACT_ABORT ON 之後，造成錯誤的陳述式之後的 T-SQL 陳述式將不會執行。這可能會影響現有程式碼的預期流程。

情境三：由 SPID 導致阻塞，該 SPID 對應的用戶端應用程式未能完整取得所有結果列

在將查詢傳送至伺服器之後，所有應用程式必須完整擷取所有結果資料列。如果應用程式沒有取得所有結果列，它可能會在資料表上留下鎖，封鎖其他使用者。如果您正在使用的應用程式明確將 SQL 陳述式提交至伺服器，則應用程式必須擷取所有結果資料列。如果它不工作 (且無法設定以運行)，您可能無法解決阻塞問題。為避免此問題，應將行為不良的應用程式限制在報告或決策支援資料庫中，與主 OLTP 資料庫分開。

解決方法：

重寫應用程式，將所有結果列都擷取到完成。這並不會排除查詢使用 ORDER BY 子句中 OFFSET 和 FETCH 以執行伺服器端分頁。

案例 4：分散式用戶端/伺服器鎖死造成的封鎖

與傳統鎖死不同，使用 RDBMS 鎖定管理員無法偵測到分散式鎖死。這是因為與鎖死相關的資源中，只有其中一個是 SQL Server 鎖定。鎖死的另一端位於用戶端應用程式層級，SQL Server 無法控制。下列兩節說明了這種情況如何發生，以及為避免這種情況，應用程式可能採取的方式。

範例 A：單一用戶端執行緒的用戶端/伺服器分散式死結

若用戶端有多個開啟連線且執行執行緒僅有一個執行緒，則可能發生以下分散式死結。請注意，此處使用的 dbproc 一詞指的是用戶端連線結構。

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

在前述情況下，單一客戶端應用程式執行緒有兩個開啟連線。它會以非同步方式在 dbproc1 提交 SQL 作業。這表示它不會等待呼叫傳回之後，再繼續進行。應用程式接著會在 dbproc2 提交另一個 SQL 作業，並等候結果以開始處理傳回的資料。當資料開始回傳（不管是哪個 dbproc 先回應——假設是 dbproc1），它會處理該 dbproc 上回傳的所有資料。它會從 dbproc1 擷取結果，直到 SPID2 持有的鎖定封鎖 SPID1 為止 (因為在伺服器上，這兩個查詢以非同步方式執行)。此時，dbproc1 會無限期等待更多資料。 SPID2 不會在鎖定時遭到封鎖，但會嘗試傳送資料至用戶端 dbproc2。不過，實際上在應用程式層級 dbproc2 會遭到 dbproc1 封鎖，因為 dbproc1 正在使用該應用程式的單一執行緒。這種情況會導致死結，SQL Server 無法偵測或解決，因為涉及的資源中只有一個是 SQL Server 資源。

範例 B：用戶端/伺服器分散式鎖死，每個連線具有一個執行緒

即使每個連線在用戶端都有獨立執行緒，這種分散式死結仍可能發生變化，如下範例所示。

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

此案例與範例 A 類似，但 dbproc2 和 SPID2 執行 SELECT 陳述式，目的是針對資料列進行一次一列的處理，並透過緩衝區，依相同資料表上的 INSERT、 UPDATE 或 DELETE 陳述式，將每個資料列交給dbproc1。最後，SPID2 (執行 INSERT) 所持有的鎖定會封鎖 SPID1 (執行 UPDATE、DELETE 或 SELECT)。 SPID2 會將結果資料列寫入用戶端 dbproc2。 Dbproc2 接著會嘗試將緩衝區中的資料列傳送至 dbproc1，但會發現 dbproc1 忙碌中 (正在等候 SPID1 完成目前的 INSERT ，而這遭到 SPID2 封鎖)。此時，在應用程式層級 dbproc2 會遭到 dbproc1 封鎖，而後者的 SPID (SPID1) 會在資料庫層級遭到 SPID2 封鎖。這同樣會導致鎖死，並且 SQL 伺服器無法偵測或解決，因為其中只有一個資源是 SQL Server 資源。

範例 A 及 B 都是應用程式開發人員必須注意的基本問題。他們必須撰寫應用程式程式碼，以適當處理這些案例。

解決方法：

當你提供查詢逾時時，如果發生分散式死結，逾時就會打破僵局。欲了解更多查詢逾時的使用資訊，請參閱您的連線提供者文件。

案例 5：復原狀態工作階段所造成的封鎖

當你在使用者定義交易之外終止或取消資料修改查詢時，查詢會回滾。這種回滾也可能因為用戶端網路會話斷線、用戶端電腦重啟，或請求被選為死結受害者而產生。你通常無法比最初套用變更時更快回滾資料修改查詢。例如，如果一個 DELETE、 INSERT或 UPDATE 陳述句執行一小時，回滾至少需要一小時。這種行為是預期中的，因為如果不回滾這些變更，資料庫的交易和實體完整性將會受到損害。因為這個回滾必須完成，SQL Server 會將會話標記為 KILLED/ROLLBACK 狀態，你無法再次終止該會話或將其選為死結受害者。你通常可以透過觀察的sys.dm_exec_requests輸出來辨識這個狀態，欄位commandKILLED/ROLLBACK報告，欄位percent_complete顯示進度。

注意

啟用加速資料庫復原功能時，長時間的復原很少見。 SQL Server 2019 中已新增此功能。

解決方法：

等會話回滾變更後再說。

如果你在操作中途關閉實例，重新啟動時資料庫會處於恢復模式，直到所有未完成的交易才能存取。每次交易的啟動復原與執行階段復原所需時間大致相同，而且在此期間無法存取資料庫。因此，強迫伺服器在回滾狀態下修復 SPID 往往適得其反。在啟用加速資料庫復原的 SQL Server 2019 中，這種情況不應該發生。

若要避免這種情況，請不要在 OLTP 系統忙碌期間執行大型批次寫入作業、索引建立或維護作業。請盡可能在低活動的期間執行這類作業。

案例 6：孤立連線造成的封鎖

這是常見的問題案例，部分與案例 2重疊。如果用戶端應用程式停止、用戶端工作站重新開機，或發生批次中止錯誤，這些都可能會讓交易保持開啟。如果應用程式未復原應用程式 CATCH 或 FINALLY 區塊中的交易，或未以其他方式處理這種情況，就會發生這種情況。

在此案例中，雖然 SQL 批次已取消執行，但應用程式會讓 SQL 保持開啟。就 SQL Server 執行個體的觀點而言，用戶端仍然顯示為存在，而且可能會保留任何取得的鎖定。

若要示範孤立的交易，請執行下列查詢，藉由將資料插入不存在的資料表來模擬批次中止錯誤：

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

然後，在同一個視窗中執行此查詢：

SELECT @@TRANCOUNT;

第二筆查詢的輸出表示交易數目為一。在交易認可或復原之前，交易中取得的所有鎖定會持續保留。由於查詢已中止批次，因此執行該批次的應用程式可能會繼續在相同的工作階段上執行其他查詢，而不會清除仍然開啟的交易。鎖定會保留到工作階段終止或重新開機 SQL Server 執行個體為止。

解決方式：

預防此狀況的最佳方法是改善應用程式錯誤與例外處理，特別是針對意外終止。請確定您在應用程式程式碼中使用 Try-Catch-Finally 區塊，並在發生例外狀況時復原交易。
考慮將 SET XACT_ABORT ON 用於任何開始交易且發生錯誤後未清除的預存程式。若執行時錯誤導致批次中止，此設定會自動回滾所有未完成的交易，並將控制權還給用戶端。如需詳細資訊，請參閱 SET XACT_ABORT (Transact-SQL)。
當用戶端應用程式的孤立連線已中斷連線但未適當清除其資源時，您可以利用 KILL 命令來終止 SPID 以解決問題。如需參考，請參閱 KILL (Transact-SQL)。

KILL 命令會將 SPID 值視為輸出。例如，若要終止 SPID 9，請執行下列命令：

KILL 99

注意

因為檢查 KILL 命令的時間間隔，KILL 命令可能需要多達 30 秒才能完成。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-08-05

了解並解決 SQL Server 封鎖問題

總結

何謂封鎖

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

從擴充事件收集資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

案例 1：查詢正常執行，但執行時間太長所造成的封鎖

案例 2：休眠中 SPID 有未認可的交易而造成的封鎖

情境三：由 SPID 導致阻塞，該 SPID 對應的用戶端應用程式未能完整取得所有結果列

案例 4：分散式用戶端/伺服器鎖死造成的封鎖

範例 A：單一用戶端執行緒的用戶端/伺服器分散式死結

範例 B：用戶端/伺服器分散式鎖死，每個連線具有一個執行緒

案例 5：復原狀態工作階段所造成的封鎖

案例 6：孤立連線造成的封鎖

相關內容

意見反應

其他資源