了解並解決封鎖問題

發行項
12/18/2024

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

目標

在本文中，「連線 (connection)」一詞是指資料庫的單一登入工作階段。每個連線都會顯示為會話ID（SPID），或在許多 DMV 中顯示為 session_id。這些 SPID 通常稱為程序，雖然照理來說，此並非個別的程序內容。相反地，每個 SPID 是由服務所需的伺服器資源和資源結構組成，該服務會要求來自指定用戶端的單一連線。單一用戶端應用程式可能有一或多個連線。從 Azure SQL 資料庫的觀點而言，單一用戶端電腦上來自單一用戶端應用程式的多個連線與多個用戶端電腦上來自多個用戶端應用程式的多個連線之間並沒有差異，兩者皆不可部分完成。無論來源用戶端為何，一個連線可以封鎖另一個連線。

如需解決死鎖問題的資訊，請參閱 Azure SQL Database 和 Fabric SQL Database 中的死鎖分析及預防。

注意

本內容著重於 Azure SQL 資料庫。 Azure SQL 資料庫是以最新穩定版本的 Microsoft SQL Server 資料庫引擎為基礎，因此多數內容相似，但是疑難排解選項和工具可能有所不同。如需 SQL Server 封鎖的詳細資訊，請參閱了解並解決 SQL Server 封鎖問題。 Fabric SQL 資料庫會與 Azure SQL 資料庫共用許多功能。如需效能監視的詳細資訊，請參閱在 Microsoft Fabric中監視 SQL 資料庫。

了解封鎖

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

Azure SQL Database 中的各個新資料庫預設都會啟用讀取認可快照集 (RCSI) 資料庫設定。在讀取資料和寫入資料間工作階段的封鎖會在 RCSI 下最小化，因為 RCSI 使用資料列版本控制來增加並行。不過，封鎖和鎖死仍可能會發生在 Azure SQL 資料庫的資料庫中，因為：

修改資料的查詢可能會彼此封鎖。
查詢可能在增加封鎖的隔離等級下執行。隔離等級可以在 Transact-SQL 的應用程式連接字串、查詢提示或 SET 陳述式中指定。
RCSI 可能會停用，導致資料庫使用共用 (S) 鎖定來保護在讀取認可隔離等級下執行的 SELECT 陳述式。這可能會增加封鎖與鎖死。

依預設，Azure SQL Database 中的新資料庫也會啟用快照集隔離等級。快照集隔離是額外的資料列型隔離等級，可提供資料的交易層級一致性，並使用資料列版本來選取要更新的資料列。若要使用快照集隔離，查詢或連線必須明確地將其交易隔離等級設定為 SNAPSHOT。此動作僅能在資料庫啟用快照集隔離時完成。

您可以使用 Transact-SQL 來識別 RCSI 和/或快照集隔離是否已啟用。連線至您 Azure SQL Database 中的資料庫，並執行下列查詢：

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

如果已啟用 RCSI，is_read_committed_snapshot_on 資料行會傳回值 1。如果已啟用快照集隔離，snapshot_isolation_state_desc 資料行會傳回值 ON。

查詢的持續時間和交易內容會決定鎖定持續時間，以及其對其他查詢的影響。在 RCSI 下執行的 SELECT 陳述式不會對正在讀取的資料取得共用 (S) 鎖定，因此請勿封鎖修改資料的交易。針對 INSERT、UPDATE 和 DELETE 陳述式，會在查詢期間保留鎖定，以確保資料一致性並視需要允許復原查詢。

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

當鎖定或封鎖持續保留並對系統效能有不利的影響時，則可能是因為下列其中一個原因：

SPID 在釋出一組資源前，針對該組資源上的鎖定會保留一段時間。這種類型的封鎖會在一段時間內自行解決，但可能會導致效能降低。
SPID 會保留一組資源上的鎖定並永不釋放。這種類型的封鎖無法自行解決，並可無限期防止存取受影響的資源。

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

最佳化鎖定

最佳化鎖定是新的資料庫引擎功能，大幅減少鎖定記憶體，以及同時需要寫入的鎖定數目。最佳化鎖定使用兩個主要元件：交易識別碼 (TID) 鎖定 (也用於其他資料列版本設定功能)，以及合格後鎖定 (LAQ)。它不需要任何額外的設定。

本文目前適用於沒有最佳化鎖定的資料庫引擎行為。

如需詳細資訊並了解最佳化鎖定的適用範圍，請參閱最佳化鎖定 (機器翻譯)。

應用程式和封鎖

在發生封鎖問題時，您通常會著重於伺服器端微調和平台問題。然而，僅專注於資料庫可能無法解決問題並會耗費時間和精力，因此建議檢查用戶端應用程式並提交查詢。無論應用程式公開關於所呼叫資料庫呼叫的可見性層級為何，封鎖問題通常都需要檢查應用程式提交的確切 SQL 語句，以及應用程式有關查詢取消、連接管理、擷取所有結果數據列等的確切行為。如果開發工具不允許對連線管理、查詢取消、查詢逾時和結果擷取等的明確控制，則可能無法解決封鎖問題。在針對 Azure SQL Database 選取應用程式開發工具前，您必須先密切檢查潛在影響，特別是針對重視效能的 OLTP 環境。

請在設計期間及資料庫和應用程式建構階段中，注意資料庫效能。請特別針對每個查詢，評估資源使用量、隔離等級和交易路徑長度。每個查詢和交易應盡可能輕量。必須執行良好的連接管理紀律。如果沒有它，應用程式在用戶數量低時可能會有可接受的效能，但隨著用戶數目向上調整，效能可能會大幅降低。

透過適當的應用程式和查詢設計，Azure SQL Database 可在單一伺服器上支援數千位同時上線的使用者，並幾乎不會封鎖。

注意

如需更多應用程式開發指引，請參閱針對連線問題和其他錯誤進行疑難解答，和暫時性錯誤處理。

疑難排解封鎖

無論處於何種封鎖情況，疑難排解封鎖的方法皆相同。這些邏輯區分決定了本文其餘內容的構成。概念是找出前端封鎖程式，並識別該查詢的執行項目和封鎖原因。一旦識別問題的查詢 (意即，長期保留鎖定的項目)，下一個步驟是分析和判斷封鎖發生的原因。在瞭解原因之後，我們可以藉由重新設計查詢和交易來進行變更。

疑難排解的步驟：

識別主要封鎖工作階段 (前端封鎖程式)
尋找造成鎖定的查詢和交易 (長期保留鎖定的項目)
分析/了解長時間封鎖發生的原因
藉由重新設計查詢和設計以解決封鎖問題

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

收集封鎖資訊

為了應對疑難排解封鎖問題的困難，資料庫管理員可使用 SQL 指令碼以持續監視 Azure SQL Database 中資料庫鎖定和封鎖的狀態。若要收集此資料，基本上有兩種方法。

第一種方法為查詢動態管理物件 (DMO)，並儲存結果以供於一段時間進行比較。本文所參考的部分物件為動態管理檢視 (DMV)，有些是動態管理函式 (DMF)。第二種方法是使用 XEvents 來擷取正在執行的項目。

收集 DMV 的資訊

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

請記得在目標 Azure SQL Database 的資料庫中執行每一個指令碼。

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

請參閱中 blocking_session_id 的 sys.dm_exec_requests 資料行。當 blocking_session_id = 0 時，表示未封鎖工作階段。雖然 sys.dm_exec_requests 僅列出目前正在執行的要求，但任何連線 (作用中與否) 皆會列在 sys.dm_exec_sessions 中。在下一個查詢中，請建立 sys.dm_exec_requests 和 sys.dm_exec_sessions 之間的通用聯結。
執行此範例查詢，以使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV，尋找正在執行的查詢和其目前 SQL 批次文字和輸入緩衝區文字。如果 sys.dm_exec_sql_texttext 欄位傳回的數據為 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;

若要擷取長時間執行或未認可的交易，請使用其他組的 DMV (包含 sys.dm_tran_database_transactions、sys.dm_tran_session_transactions、sys.dm_exec_connections 和 sys.dm_exec_sql_text) 以檢視目前未結案交易。有幾個與追蹤交易相關的 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 執行序/工作層的 sys.dm_os_waiting_tasks。這會傳回要求目前遇到的 SQL 等候類型相關資訊。例如 sys.dm_exec_requests，sys.dm_os_waiting_tasks 僅會傳回作用中的要求。

注意

如需等候類型的詳細資訊 (包括一段時間的彙總等候統計資料)，請參閱 DMV sys.dm_db_wait_stats。此 DMV 僅會傳回目前資料庫的彙總等候統計資料。

使用 Sys.dm_tran_locks DMV，取得查詢所放置鎖定項目的更細微資訊。此 DMV 可在實際執行的資料庫上傳回大量資料，並適用於診斷目前保留鎖定的項目。

由於 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 時，將查詢結果隨時間儲存可以提供數據點，讓您在指定的時間間隔內檢視阻塞情況，以識別持續的阻塞或趨勢。

收集擴充事件的資訊

除了先前的資訊之外，通常必須在伺服器上擷取活動的追蹤以徹底地調查 Azure SQL 資料庫上的封鎖問題。例如，如果會話在交易中執行多個語句，則僅顯示最後送出的語句。然而，其中一個較早的陳述式可能是鎖定仍保留的原因。追蹤可讓您查看目前交易內所有工作階段執行的命令。

共有兩種方式可在 SQL Server 中擷取追蹤：擴充事件 (XEvents) 和 Profiler 追蹤。然而，SQL Server Profiler 是 Azure SQL Database 不支援的已取代追蹤技術。擴充事件是較新的追蹤技術，可讓觀察到的系統更具多功能性且影響較少，且其介面已整合到 SQL Server Management Studio （SSMS）中。

請參閱說明如何在 SSMS 中使用 [擴充事件新增工作階段精靈] 的文件。然而，針對 Azure SQL Server，SSMS 會在物件總管中的每個資料庫下提供擴充事件子資料夾。使用擴充事件工作階段精靈以擷取下列實用事件：

類別錯誤：
- Attention
- Error_reported
- Execution_warning
類別警告：
- Missing_join_predicate
類別執行：
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
類別 deadlock_monitor
- database_xml_deadlock_report
類別工作階段
- Existing_connection
- 登入
- Logout

注意

如需有關死結的詳細資訊，請參閱 Azure SQL Database 和 Fabric SQL Database 中的死結分析及防止。

識別並解決常見封鎖案例

藉由檢查上述的資訊，您可以判斷大部分封鎖問題的原因。本文的其餘部分將討論如何使用這項資訊來識別及解決一些常見的封鎖案例。本討論將假設您已使用封鎖指令碼 (參考前文) 擷取封鎖 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_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 與 sys.dm_exec_requests 之間的輸出來驗證，這些輸出會顯示要求正在等候的資源。
sys.dm_exec_requests.wait_resource 此欄位表示封鎖要求正在等候的資源。下列資料表列出常見 wait_resource 格式和其意義：

資源	[格式]	範例	說明
資料表	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	在此案例中，資料庫識別碼 5 是 Pubs 範本資料庫，物件識別碼 261575970 是標題資料表，而 1 是叢集索引。
頁面	DatabaseID:FileID:PageID	PAGE: 5:1:104	在此情況下，資料庫標識碼 5 `is pubs`、檔案標識碼 1 是主要數據檔，而第 104 頁是屬於 titles 數據表的頁面。若要識別頁面所屬 `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 （titles table）。使用 `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_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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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 中的其餘資料行也可以提供問題根源的深入解析。其資料行的實用性會視問題的情況而有所不同。例如，您可以判斷問題是否只發生於特定用戶端 (主機名稱)、特定網路程式庫 (net_library)、在 last_request_start_time 中 SPID 所提交最後批次的 sys.dm_exec_sessions 為何、在 start_time 中使用 sys.dm_exec_requests 的要求執行時間為何，以此類推。

常見的封鎖案例

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

Waittype、Open_Tran和 Status 數據行是指由 sys.dm_exec_request傳回的資訊。其他數據行可能會由 sys.dm_exec_sessions傳回。 “Resolves？” 欄位顯示封鎖是否會自動解決，或者是否應該通過 KILL 命令終止會話。如需詳細資訊，請參閱 KILL。

案例	Waittype	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 其 `hostname` 欄位在 `sys.dm_exec_sessions` 中與其封鎖的其中一個 SPID 相同。
5	NULL	>0	復原	是。	此 SPID 的擴充事件工作階段中可能出現注意訊號，表示查詢逾時或已發生取消，又或者是僅表示已發行 ROLLBACK 陳述式。
6	NULL	>0	睡眠中	最終，當 Windows 判斷會話不再作用中時，Azure SQL Database 聯機會中斷。	`last_request_start_time` 中的 `sys.dm_exec_sessions` 值會早於目前時間。

詳細的封鎖案例

若正常執行查詢的執行時間過長，便會造成封鎖

解決方式：此類型的封鎖問題解決方法便是尋找最佳化查詢的方式。此類別的封鎖問題實際上僅是效能問題，而您必須採取上述方式。如需針對特定執行緩慢查詢進行疑難排解的詳細資訊，請參閱如何針對 SQL Server 上的執行緩慢查詢進行疑難排解。如需詳細資訊，請參閱監視及調整效能。

強烈建議使用 SSMS 中查詢存放區的報告，其為實用工具以用於識別成本最高的查詢、效能不佳的執行計畫。另請檢閱查詢效能深入解析。

如果查詢只執行 SELECT 作業，且如果快照集隔離已在您的資料庫中啟用，請在快照集隔離下執行該陳述式，特別是如果 RCSI 已停用的狀況下。如同啟用 RCSI 時，讀取資料的查詢在快照隔離等級下無需共用 (S) 鎖定。此外，快照集隔離可針對明確多重陳述式交易中的所有陳述式提供交易層級一致性。您的資料庫中可能已啟用快照集隔離。快照集隔離也可用於執行修改的查詢，但您必須處理更新衝突。

如果您的查詢執行因時間過長而造成封鎖其他使用者並無法最佳化時，請考量將其從 OLTP 環境移至專用的報告系統，意即資料庫的同步唯讀複本。
若睡眠中 SPID 具有未認可的交易，便會造成封鎖

此類型的封鎖通常可由睡眠中或等待命令的 SPID 識別，但其交易巢狀層級 (@@TRANCOUNT、自 open_transaction_count 的 sys.dm_exec_requests) 大於零。如果應用程式發生查詢逾時，或發行取消時未同時發行必要的 ROLLBACK 和/或 COMMIT 陳述式數目，則便會發生此問題。當 SPID 收到查詢逾時或取消時，便會終止目前的查詢和批次，但不會自動復原或認可交易。應用程式會負責這項工作，由於 Azure SQL Database 無法假設是否必須因單一查詢取消而復原整個交易。查詢超時或取消會在擴展事件會話中顯示為 SPID 的 ATTENTION 訊號事件。

若要示範未認可的明確交易，請發行下列查詢：
```
CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
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 應用程式），開啟連線後執行一些查詢再將連線放回集區，那麼暫時停用連線集區可能有助於減輕問題，直到客戶端應用程式被修改以適當處理錯誤為止。透過停用連線共用，釋出連線將會造成 Azure SQL 資料庫連線的實體中斷連線，進而導致伺服器復原任何未結案的交易。
  - 您可使用 SET XACT_ABORT ON 以供連線，或用於開始交易且未清除後續錯誤的任何預存程序。如果執行階段發生錯誤，則此設定將終止任何未結案的交易並將控制項傳回至用戶端。如需詳細資訊，請檢閱SET XACT_ABORT。
注意

在連線集區重複使用連線之前將不會重設連線，因此使用者可能會開啟連線，然後將連線釋放至連線集區，但在幾秒內可能不會重複使用，而在這段期間交易仍會保持未結案。如果未重複使用連線，當連線逾時且從連接池中移除時，交易就會中止。因此，建議用戶端應用程式終止發生錯誤處理常式的交易，或使用 SET XACT_ABORT ON 避免此潛在延遲。

警告

在 SET XACT_ABORT ON之後，不會執行導致錯誤的語句之後的 T-SQL 語句。這可能會影響現有程式碼的預期流程。
如果 SPID 的對應用戶端應用程式未完整擷取所有結果資料列，便會造成封鎖

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

在資料庫上啟用讀取認可快照集時，對於案例的影響會降低，因為這是 Azure SQL Database 中的預設組態。若要深入了解，請參閱本文的了解封鎖一節。

注意

請參閱重試邏輯的指引，了解連線至 Azure SQL Database 的應用程式。

解決方法：應用程式必須重寫以完整擷取所有結果資料列。這並不會排除查詢使用 ORDER BY 子句中 OFFSET 和 FETCH 以執行伺服器端分頁。
若工作階段處於復原狀態，便會造成封鎖

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

由於 2019 年引進的加速資料庫復原，冗長的復原應該很少見。

解決方法：等候 SPID 完成復原所進行的變更。

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

如果用戶端應用程式截獲錯誤或重新啟動用戶端工作站，則在某些條件下可能不會立即取消伺服器的網路工作階段。從 Azure SQL 資料庫的觀點而言，用戶端仍會存在且可能仍會保留任何取得的鎖定。如需詳細資訊，請參閱如何疑難排解 SQL Server 中的孤立連接。

解決方法：若用戶端應用程式已中斷連線且未適當清理其資源，則您可以使用 KILL 命令終止 SPID。 KILL 命令會將 SPID 值視為輸出。例如，若要終止 SPID 99，請發行下列命令：
```
KILL 99
```

共用方式為

了解並解決封鎖問題

目標

了解封鎖

最佳化鎖定

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

收集擴充事件的資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

意見反應

其他資源

共用方式為

了解並解決封鎖問題

目標

了解封鎖

最佳化鎖定

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

收集擴充事件的資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

相關內容

意見反應

其他資源