了解並解決 SQL Server 封鎖問題

發行項
11/20/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 中開啟活動監視器，並參閱已封鎖資料欄位。如需活動監視器的詳細資訊請參閱這裡。

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

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 批次文字和輸入緩衝區文字。如果傳回 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。

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 的資訊。例如 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。

有預先建立的擴充事件工作階段可從 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

識別並解決常見封鎖案例

藉由檢查上述資訊，您可以判斷大部分封鎖問題的原因。本文的其餘部分將討論如何使用這項資訊來識別及解決一些常見的封鎖案例。本討論假設 (參考先前) 您已使用的封鎖指令碼擷取封鎖 SPIDs 的相關資訊，並已使用 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 是標題資料表，而 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 中的其餘資料行也可以提供問題根源的深入解析。其資料行的實用性會視問題的情況而有所不同。例如，您可以判斷問題是否只發生在特定用戶端 (hostname)、特定網路程式庫 (client_interface_name)、SPID 提交的最後一個批次是在 sys.dm_exec_sessions 中的 last_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，且在交易隔離等級為預設值時 (READ COMMITTED)，SPID 持有鎖定，則這可能是原因。
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 上的執行緩慢查詢進行疑難排解。如需詳細資訊，請參閱監視及調整效能。

查詢存放區 SSMS 的內建報表 (SQL Server 2016 引入)，也是高度推議而實用的工具，可用來識別耗用大量資源的查詢以及未達最佳標準的執行計畫。

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

注意

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

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

此類封鎖通常可透過休眠中或等候命令的 SPID 來識別，但其交易巢狀層級 (來自 sys.dm_exec_requests 的 @@TRANCOUNT 與 open_transaction_count) 大於零。此狀況發生於下列情形：當應用程式遭遇查詢逾時，或提出取消但未提供必要的復原與 / 或認可陳述式號碼。當 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 陳述式，目的是針對資料列進行一次一列的處理，並透過緩衝區，依相同資料表上的 INSERT、 UPDATE 或 DELETE 陳述式，將每個資料列交給dbproc1。最後，SPID2 (執行 SELECT) 所持有的鎖定會封鎖 SPID1 (執行 INSERT、UPDATE 或 DELETE)。 SPID2 會將結果資料列寫入用戶端 dbproc2。 Dbproc2 接著會嘗試將緩衝區中的資料列傳送至 dbproc1，但會發現 dbproc1 忙碌中 (正在等候 SPID1 完成目前的 INSERT ，而這遭到 SPID2 封鎖)。此時，在應用程式層級 dbproc2 會遭到 dbproc1 封鎖，而後者的 SPID (SPID1) 會在資料庫層級遭到 SPID2 封鎖。這同樣會導致鎖死，並且 SQL 伺服器無法偵測或解決，因為其中只有一個資源是 SQL Server 資源。

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

解決方法：

提供查詢逾時時，如果發生分散式鎖死，該鎖死會在逾時發生時解除。如需詳細資訊使用查詢逾時，請參閱您連線提供者的文件。

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

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

在使用者定義的交易之外，資料修改查詢若遭刪除或取消，會進行復原。當用戶端電腦重新開機及其網路工作階段連線中斷時，這也是可能發生的副作用。如果遭到選取成為鎖死目標，查詢同樣會復原。復原資料修改查詢的速度通常無法像最初套用變更時那麼快。例如，如果 DELETE、INSERT 或 UPDATE 陳述式已執行一小時，可能需要至少一小時才能復原。這是預期的行為，因為必須復原已執行的變更，否則資料庫的交易與實體完整性會遭到損害。因為這必須發生，SQL Server 會標示 SPID 為金黃色或復原狀態 (這表示它無法刪除或遭選取為鎖死目標)。這通常可以藉由觀察 sp_who 的輸出來識別，它可能顯示復原命令。 sys.dm_exec_sessions 的 status 資料欄會顯示復原狀態。

注意

啟用加速資料庫復原功能時，長時間的復原很少見。 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 秒才能完成。

共用方式為

了解並解決 SQL Server 封鎖問題

目標

何謂封鎖

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

從擴充事件收集資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

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

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

案例 3：封鎖原因為 SPID 對應的用戶端應用程式未完成擷取所有結果資料列

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

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

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

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

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

另請參閱

意見反應

其他資源