瞭解並解決 SQL Server 封鎖問題

適用於： SQL Server (所有支援版本)、Azure SQL 託管執行個體

原始 KB 編號：224453

目標

本文說明 SQL Server 封鎖，並示範如何針對封鎖進行疑難排解並解決問題。

在本文中，「連線」一詞意指單一登入的資料庫工作階段。 在許多 DMV 中，每個連線都會以處理序識別碼 (SPID) 或 session_id 顯示。 儘管這裡所述的每個 SPID 不是一般觀念中所指的個別處理序內容，但在這裡通常是指「處理序」。 確切地說，每個 SPID 都含有伺服器資源，以及提供指定用戶端請求之單一連線服務時所需的資料結構。 單一用戶端應用程式可能有一或多個連線。 從 SQL Server 的觀點來看，以下情形沒有差異：來自單一用戶端電腦上單一用戶端應用程式的多個連線，以及來自多個用戶端應用程式或多部用戶端電腦的多個連線；它們都不可部分完成。 不論來源用戶端為何，一個連線都可以封鎖另一個連線。

什麼是封鎖

對於使用鎖定式同步存取的關聯式資料庫管理系統 (RDBMS) 來說，封鎖是一項無可避免並且按設計執行的特性。 如先前所述，在 SQL Server 中，當一個工作階段持有特定資源的鎖定，但另一個 SPID 試圖對同一個資源取得衝突的鎖定類型時，便會發生封鎖的現象。 一般而言，第一個 SPID 鎖定資源的時間範圍很短。 當持有工作階段解除鎖定時，第二個連線即可取得該資源的鎖定，以便繼續處理。 如這裡所述封鎖是常見行為，一天當中可能發生多次，不會對系統效能產生明顯影響。

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

對於在交易中執行的查詢，持有鎖定的持續時間取決於查詢類型、交易隔離等級，以及是否在查詢中使用鎖定提示。 如需鎖定、鎖定提示與交易隔離等級的描述，請參閱下列文章：

• 鎖定資料庫引擎
• 自訂鎖定和行版本
• 鎖定模式
• 鎖定相容性
• 資料庫引擎中基於版本的行隔離等級
• Transactions

當鎖定與封鎖持續以致對系統效能產生負面影響時，這是由下列其中一個原因造成：

• 某個 SPID 持有一組資源的鎖定相當久後才釋放資源。 此類封鎖會隨著時間自行解除，但可能會導致效能降低。

• 某個 SPID 持有一組資源的鎖定且不釋放。 此類封鎖無法自行解除，且會導致這些受影響的資源無限期無法存取。

在第一個案例中，情況可能隨時變化，因為不同的 SPID 可能對不同的資源產生封鎖，因此目標會一直變動。 這些情況很難使用 SQL Server Management Studio 將問題縮小為個別查詢以進行疑難排解。 反倒是第二種情況的狀態一致，因此較易診斷。

應用程式與封鎖

當遇到封鎖問題時，可能往往會專注於伺服器端的微調與平台問題。 不過，只注意資料庫可能無法解決問題，也會花費原本該用於檢查用戶端應用程式及其送出的查詢的時間和精力。 針對所進行的資料庫呼叫，無論應用程式公開的可見度層級為何，封鎖問題仍經常需要檢視下列兩者：應用程式所提交的確切 SQL 陳述式，以及應用程式在查詢取消、連線管理、擷取所有結果資料列等方面的確切行為。 如果開發工具不允許明確控制連線管理、查詢取消、查詢逾時、結果擷取等等，可能無法解決封鎖問題。 選取適用於 SQL Server 的應用程式開發工具之前，應該仔細檢查此可能性，特別是針對效能敏感的 OLTP 環境。

在資料庫與應用程式的設計及建構階段，請注意資料庫效能。 尤其應該針對每筆查詢評估資源耗用量、隔離等級及交易路徑長度。 每筆查詢與交易都應該盡可能輕量。 必須遵守良好的連線管理規範，若未遵守，在低使用者數時，應用程式的效能可能尚可接受，但是隨著使用者數目增加，其效能可能跟著大幅降低。

透過適當的應用程式與查詢設計，SQL Server 能夠同時支援單一伺服器上的數千位使用者，並且難得發生封鎖情況。

疑難排解封鎖問題

無論我們處於何種封鎖狀況，針對鎖定進行疑難排解的方法都相同。 這些邏輯分隔會決定本文的其餘部分。 概念是尋找前端封鎖程式，並識別正在執行的查詢動作，以及封鎖的原因。 一旦識別出有問題的查詢，也就是(長時間保持鎖定) ，下一個步驟是分析並判斷封鎖發生的原因。 在瞭解原因之後，就可以重新設計查詢和交易來進行變更。

疑難排解的步驟：

1. 識別主要封鎖工作階段 (前端封鎖程式)

2. 尋找造成封鎖的查詢和交易， (什麼是長時間保留鎖定)

3. 分析/瞭解長時間封鎖發生的原因

4. 重新設計查詢和交易來解決封鎖問題

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

收集封鎖資訊

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

第一種是查詢動態管理物件 (DMOs) ，並儲存結果以供一段時間進行比較。 本文參照的物件部份是動態管理檢視 (DMV)，部份則是動態管理功能 (DMF)。

第二種是使用 擴充事件(XEvents) 或SQL Profiler 追蹤來擷取正在執行的內容。 由於 SQL 追蹤和 SQL Server Profiler 已被取代，因此本疑難排解指南將著重于 XEvents。

從 DMVs 收集資訊。

參考 DMVs 來針對疑難排解封鎖問題的目標是要識別前端封鎖鏈的 SPID (工作階段 ID) 以及SQL 聲明。 尋找遭到封鎖的受害者 SPIDs。 如果有任何 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>) 來尋找工作階段提交的最後一個聲明。 您可以使用動態管理功能 (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 資料欄位。 當 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 DMVs 來尋找主動執行的查詢及目前 SQL 批次文字或輸入緩衝區文字。 如果傳回 text 資料欄位 sys.dm_exec_sql_text 的資料為 NULL，則目前沒有在執行查詢。 在此情況下， event_info 資料欄位 sys.dm_exec_input_buffer 會包含傳遞至 SQL 引擎的最後一個命令字串。 此查詢也可以用來識別封鎖其他工作階段的工作階段，包括每個 session_id 封鎖的 session_ids清單。

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。 有幾個 DMVs 與追蹤交易相關聯，請參閱這裡 有關 DMVs 的更多交易。

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 傳回。

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

由於 INNER JOIN 開啟 sys.dm_os_waiting_tasks，下列查詢會限制輸出 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>';

使用 DMVs 時，隨著時間儲存查詢結果會提供資料點，讓您查看在指定時間間隔內的封鎖，以識別持續性封鎖或趨勢。 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
  • 登入
  • 登出

識別並解決常見的封鎖案例

藉由檢查上述資訊，您可以判斷大部分封鎖問題的原因。 本文的其餘部分將討論如何利用這項資訊來識別並解決部份常見的封鎖案例。 本討論假設 (參考先前) 您已使用的封鎖指令碼擷取封鎖 SPIDs 的相關資訊，並已使用 XEvent 工作階段擷取應用程式活動。

分析封鎖資料

• 檢查 DMV 的輸出 sys.dm_exec_requests 和 sys.dm_exec_sessions，並使用 blocking_these 和 session_id來判斷前端封鎖鏈。 這會最清楚地識別哪些要求已封鎖，以及哪些要求正遭到封鎖。 進一步查看已封鎖和封鎖的工作階段。 有常見或根目錄的封鎖鏈嗎？ 它們可能會共用一個通用資料表，而一或多個牽涉到封鎖鏈的工作階段正在執行寫入作業。

• 檢查 DMVs 的輸出sys.dm_exec_requests和 sys.dm_exec_sessions 封鎖鏈頂端的 SPIDs 相關資訊。 請尋找下列欄：

  • 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_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)。 要驗證這種情形，可以比較 sys.dm_exec_requests 輸出之間的 wait_resource 值，這會顯示該要求正在等候的資源。

  • sys.dm_exec_requests.wait_resource 此欄位指出封鎖的要求正在等候的資源。 下表列出常見的 wait_resource 格式及其含義：

    資源	格式	範例	說明
    資料表	DatabaseID: ObjectID: IndexID	TAB: 5:261575970:1	在此情況下，資料庫識別碼 5 是 pubs 範例資料庫，而 object_id 261575970 是標題資料表，而 1 是叢集索引。
    頁面	DatabaseID: FileID: PageID	PAG: 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 對應於 index_id 2 的 object_id 261575970 (標題資料表)。 使用 sys.partitions 目錄檢視將 hobt_id 與特定 index_id 和 object_id 建立關聯。 索引鍵雜湊無法取消雜湊而成為特定索引鍵值。
    列	DatabaseID: FileID: PageID: Slot (列)	RID: 5:1:104:3	在此情況下，資料庫識別碼 5 是 pubs，檔案識別碼 1 是主要資料檔案，第 104 頁是屬於標題資料表的一個分頁，而位置 3 則表示資料列在頁面上的位置。
    編譯	DatabaseID: FileID: PageID: Slot (列)	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)。

詳細的封鎖案例

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

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

解決方案：

解決方法：此封鎖問題的解決方法是尋找優化查詢的方法。 此類封鎖問題可能只是效能問題，因此需要處理的是效能問題。 如需進一步資訊，針對速度過慢之特定查詢進行疑難排解，請參閱 如何針對 SQL Server 速度過慢的查詢進行疑難排解。 如需詳細資訊，請參閱 效能監視與調整。

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

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

案例 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)。

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

查詢傳送至伺服器後，所有應用程式都必須立即完成擷取所有結果資料列。 如果應用程式未擷取所有結果資料列，資料表可能保留鎖定，封鎖其他使用者。 如果您使用的應用程式會明確將 SQL 陳述式提交至伺服器，則應用程式必須擷取所有結果資料列。 如果不是 (而且無法如此設定) ，您可能無法解決封鎖問題。 若要避免此問題，您可以將表現不佳的應用程式限制在報表或決策支援資料庫之內，與主要 OLTP 資料庫分開。

解決方案：

應用程式必須重新改寫，才能完全擷取結果的所有資料列。 這並不會排除透過 排列順序子句中的抵消與擷取 提出查詢以執行伺服器端分頁。

案例 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 資料欄會顯示復原狀態。

解決方案：

您必須等候工作階段完成復原已執行的變更。

如果執行個體在此作業中關閉，當重新開機時，資料庫會處於復原模式，而且在處理完成所有開啟的交易之前將無法存取。 每次交易的啟動復原與執行階段復原所需時間大致相同，而且在此期間無法存取資料庫。 因此，強制伺服器關機以在復原模式中修復 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

另請參閱

• 使用查詢存放區以監控效能
• 解決 SQL Server 鎖定擴大造成的封鎖問題
• 交易鎖定與資料列版本控制指南
• 設定交易隔離層級
• 快速入門：SQL Server 的擴充事件