了解並解決 SQL Server 封鎖問題

發行項
07/30/2024

適用於：SQL Server（所有支援的版本），Azure SQL 受控執行個體

原始 KB 編號： 224453

目標

本文說明 SQL Server 中的封鎖，並示範如何針對封鎖進行疑難解答和解決。

在本文中，「連線 (connection)」一詞是指資料庫的單一登入工作階段。每個連線會在多個 DMV 中顯示為工作階段識別碼 (SPID) 或 session_id。每個SPID通常稱為進程，雖然它不是一般意義上的個別進程內容。相反地，每個 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 追蹤來擷取正在執行的內容。由於 SQL 追蹤和 SQL Server Profiler 已被取代，因此此疑難解答指南將著重於 XEvents。

收集 DMV 的資訊

參考 DMV 以疑難排解封鎖的目標在於識別封鎖鏈和 SQL 陳述式前端的 SPID (工作階段識別碼)。尋找遭封鎖的犧牲者 SPID。如果其他 SPID 封鎖任何 SPID，則請調查擁有資源的 SPID (封鎖的 SPID)。是否也會封鎖擁有者 SPID？您可以查核封鎖鏈以尋找前端封鎖程式，然後調查保留鎖定的原因。

若要這樣做，您可以使用下列其中一種方法：

在 SQL Server Management Studio （SSMS）物件總管中，以滑鼠右鍵按兩下最上層伺服器對象，展開 [報表]，展開 [標準報表]，然後選取 [活動 - 所有封鎖交易]。此報表會顯示封鎖鏈結前端的目前交易。如果您展開交易，報表會顯示前端交易封鎖的交易。此報表也會顯示 封鎖 SQL 語句 和 封鎖的 SQL 語句。
在 SSMS 中開啟活動監視器，並參考 [封鎖者] 數據行。在這裡尋找活動監視器的詳細資訊。

您也可以使用 DMV 來取得更詳細的查詢型方法：

sp_who和 sp_who2 命令是較舊的命令，可顯示所有目前的會話。 DMV sys.dm_exec_sessions 會在結果集中傳回更多資料，以更容易查詢和篩選。您會在其他查詢的核心找到 sys.dm_exec_sessions。
如果已識別特定的工作階段，則您可以使用 DBCC INPUTBUFFER(<session_id>) 尋找工作階段已提交的最後陳述式。您可以使用 sys.dm_exec_input_buffer 動態管理函式 (DMF) 在結果集中傳回類似的結果，以更容易查詢和篩選並提供 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行。當 blocking_session_id = 0 時，表示未封鎖工作階段。雖然 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 批次文字和輸入緩衝區文字。如果的數據行所textsys.dm_exec_sql_text傳回的數據是 NULL，則查詢目前不會執行。在此情況下，的數據event_infosys.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;

若要攔截長時間執行或未認可的交易，請使用另一組 DMV 來檢視目前的開啟交易，包括sys.dm_tran_database_transactions、sys.dm_tran_session_transactions 、sys.dm_exec_connections 和。sys.dm_exec_sql_text 下列為與追蹤交易相關聯的多個 DMV，如需更多，請在此處參閱交易的 DMV。

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

位於 SQL Server 線程/工作層的參考 sys.dm_os_waiting_tasks 。這會傳回目前遇到 SQL wait_type之 SQL 的相關信息。例如 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 移至工具是使用 PSSDiag 資料收集器。此工具會使用「SQL Server Perf Stats」從上面參考的 DMV 收集一段時間的結果集。隨著此工具不斷演進，請檢閱 GitHub 上最新公開版本的 DiagManager。

從擴充事件收集資訊

除了上述資訊之外，通常需要擷取伺服器上的活動追蹤，以徹底調查 SQL Server 中的封鎖問題。例如，若工作階段在交易內執行多個陳述式，僅會呈現最後提交的陳述式。然而，其中一個較早的陳述式可能是鎖定仍保留的原因。追蹤可讓您查看目前交易內所有工作階段執行的命令。

有兩種方式可以在 SQL Server 中擷取追蹤; 擴充事件（XEvents）和分析工具追蹤。不過，使用 SQL Server Profiler 的 SQL 追蹤已被取代。 XEvents 是較新的、更優越的追蹤平臺，可讓觀測系統更具多功能性且影響較小，且其介面已整合到 SSMS 中。

預先製作的擴充事件會話已準備好在 SSMS 中啟動，列在 XEvent Profiler 功能表下的物件總管。如需詳細資訊，請參閱 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 資料行值小於 sys.dm_exec_requests 先前查詢的 wait_time 值，這表示已取得並釋放先前的鎖定，而現在正在等候新的鎖定 (假設非零 wait_time)。這可以藉由比較 wait_resource 與 sys.dm_exec_requests 之間的輸出來驗證，這些輸出會顯示要求正在等候的資源。

sys.dm_exec_requests.wait_resource

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

資源	[格式]	範例	說明
資料表	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	在此情況下，資料庫標識碼 5 是 pubs 範例資料庫， `object_id` 261575970是 titles 數據表，1 是叢集索引。
頁面	DatabaseID:FileID:PageID	PAGE: 5:1:104	在此案例中，資料庫識別碼 5 是 Pubs，檔案識別碼 1 是主要資料檔案，而頁面 104 是屬於標題資料表的頁面。若要識別頁面所屬的 object_id，則請使用動態管理函式 sys.dm_db_page_info 以傳入 `wait_resource` 的 DatabaseID、FileId、PageId。
答案	DatabaseID:Hobt_id (索引鍵的雜湊值)	KEY: 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 表示資料列在頁面上的位置。
編譯	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	在此案例中，資料庫識別碼 5 是 Pubs，檔案識別碼 1 是主要資料檔案，頁面 104 是屬於標題資料表的頁面，而資料列 3 表示資料列在頁面上的位置。

sys.dm_tran_active_transactionssys.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 中的其餘資料行也可以提供問題根源的深入解析。其資料行的實用性會視問題的情況而有所不同。例如，您可以判斷問題是否只發生在特定網路連結庫上的特定用戶端（hostnameclient_interface_name），當SPID提交的最後一個批次位於 sys.dm_exec_sessionslast_request_start_time 、要求在sys.dm_exec_requests中執行start_time的時間長度等等。

常見的封鎖案例

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

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

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

詳細的封鎖案例

案例 1：封鎖由運行時間很長的正常執行查詢所造成

在此案例中，主動執行的查詢已取得鎖定，且不會釋放鎖定（它受到交易隔離等級的影響）。因此，其他會話會等到鎖定釋放為止。

解決方法：

此封鎖問題的解決方案是尋找優化查詢的方法。此類別的封鎖問題可能是效能問題，因此您必須將其視為這類問題。如需針對特定執行緩慢查詢進行疑難排解的詳細資訊，請參閱如何針對 SQL Server 上的執行緩慢查詢進行疑難排解。如需詳細資訊，請參閱監視及調整效能。

從查詢存放區（SQL Server 2016 中引進）的 SSMS 內建報告也是一個強烈建議且有價值的工具，可用來識別成本最高的查詢和次佳執行計劃。

如果您有長時間執行的查詢封鎖其他使用者且無法優化，請考慮將它從 OLTP 環境移至專用報告系統。您也可以使用 AlwaysOn 可用性群組來同步處理資料庫的唯讀複本。

注意

查詢執行期間封鎖的原因可能是查詢呈報、數據列或頁面鎖定呈報至數據表鎖定的情況。 Microsoft SQL Server 會動態判斷何時執行鎖定擴大。防止鎖定擴大的最簡單且最安全的方式是讓交易保持短，並減少昂貴查詢的鎖定使用量，以免超過鎖定擴大閾值。如需偵測和防止過度鎖定擴大的詳細資訊，請參閱解決鎖定擴大所造成的封鎖問題。

案例 2：封鎖造成睡眠 SPID 且交易未認可

這種類型的封鎖通常可由正在睡眠的SPID來識別，或等候交易巢狀層級（@@TRANCOUNT， open_transaction_count from sys.dm_exec_requests）大於零的命令。如果應用程式遇到查詢逾時或發出取消，而不發出所需的 ROLLBACK 和/或 COMMIT 語句數目，就可能發生這種情況。當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 和終止交易，也不會在此類案例中執行此復原程式碼。
- 如果在開啟連線的應用程式中使用連線共用，並在將連線放回集區之前執行一些查詢，例如 Web 應用程式，暫時停用連線共用可能有助於緩解問題，直到用戶端應用程式修改為適當地處理錯誤為止。藉由停用連線共用，釋放聯機會導致 SQL Server 連線的實體中斷連線，導致伺服器回復任何開啟的交易。
- 用於 SET XACT_ABORT ON 連線，或用於任何開始交易的預存程式中，且不會在發生錯誤後清除。如果執行階段發生錯誤，則此設定將終止任何未結案的交易並將控制項傳回至用戶端。如需詳細資訊，請參閱 SET XACT_ABORT (Transact-SQL)。

注意

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

警告

在之後 SET XACT_ABORT ON，不會執行導致錯誤的語句之後的 T-SQL 語句。這可能會影響現有程式碼的預期流程。

案例 3：由 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語句，目的是執行一次數據列處理，並將每個數據列透過緩衝區交給相同數據表上的 dbproc1INSERTUPDATEDELETE。最後，SPID1 （執行 INSERT、 UPDATE或 DELETE）會封鎖 SPID2 所持有的鎖定（執行 SELECT。 SPID2 會將結果數據列寫入用戶端 dbproc2。 Dbproc2 接著會嘗試將緩衝區中的數據列傳遞至 dbproc1，但發現 dbproc1 忙碌中（它封鎖在 SPID1 上等候 SPID1 完成目前的 INSERT，而 SPID2 會封鎖它）。此時，dbproc2 會在應用層遭到 dbproc1 封鎖，其 SPID （SPID1）被 SPID2 封鎖在資料庫層級。同樣地，這會導致 SQL Server 無法偵測或解析的死結，因為只涉及其中一個資源是 SQL Server 資源。

A 和 B 範例都是應用程式開發人員必須注意的基本問題。它們必須撰寫應用程式的程序代碼，才能適當地處理這些案例。

解決方法：

提供查詢逾時時，如果分散式死結發生，就會在逾時發生時中斷。如需使用查詢逾時的詳細資訊，請參閱連線提供者檔。

案例 5：因會話處於回復狀態而造成封鎖

將會復原終止或取消使用者定義交易以外的數據修改查詢。這也可能是在用戶端網路工作階段中斷連線或將要求選擇作為鎖死犧牲者時，所發生的副作用。這通常可藉由觀察的 sys.dm_exec_requests輸出來識別，這可能表示 ROLLBACK command，而且數據 percent_complete 行可能會顯示進度。

將會復原終止或取消使用者定義交易以外的數據修改查詢。這也可能是客戶端電腦重新啟動及其網路會話中斷連線的副作用。同樣地，選取為死結受害者的查詢將會回復。數據修改查詢通常無法回復比一開始套用的變更更快。例如，如果 DELETE、 INSERT或 UPDATE 語句已執行一小時，可能需要至少一個小時才能復原。這是預期的行為，因為必須回復所做的變更，否則資料庫中的交易式和實體完整性將會遭到入侵。由於這種情況必須發生，SQL Server 會將 SPID 標示為黃金或回復狀態（這表示無法終止或選取為死結受害者）。這通常可藉由觀察的 sp_who輸出來識別，這可能表示 ROLLBACK 命令。的數據 status 行 sys.dm_exec_sessions 會指出 ROLLBACK 狀態。

注意

啟用加速資料庫復原功能時，冗長的回復很少見。此功能已在 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)。
若要解決已中斷連線且未適當清除其資源的用戶端應用程式孤立連線，您可以使用命令終止SPID KILL 。如需參考，請參閱 KILL （Transact-SQL）。

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

KILL 99

注意

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

共用方式為

了解並解決 SQL Server 封鎖問題

目標

何謂封鎖

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

從擴充事件收集資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

案例 1：封鎖由運行時間很長的正常執行查詢所造成

案例 2：封鎖造成睡眠 SPID 且交易未認可

案例 3：由 SPID 造成封鎖，其對應的用戶端應用程式未擷取所有結果數據列完成

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

範例 A：具有單一用戶端線程的用戶端/伺服器分散式死結

範例 B：每個連線具有線程的用戶端/伺服器分散式死結

案例 5：因會話處於回復狀態而造成封鎖

案例 6：孤立交易所造成的封鎖

另請參閱

意見反應

其他資源