了解並解決 Azure SQL 資料庫封鎖問題

發行項
08/16/2024

本文說明 Azure SQL Database 的封鎖問題，並示範如何疑難排解和解決封鎖問題。

目標

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

如需疑難排解鎖死資訊，請參閱分析並防止 Azure SQL Database 中的鎖死。

注意

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

了解封鎖

針對任何具備鎖定式並行處理功能的關聯式資料庫管理系統 (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 可在單一伺服器上支援數千位同時上線的使用者，並幾乎不會封鎖。

注意

如需更多應用程式開發指導方針，請參閱疑難排解連線問題和其他 Azure SQL Database 和 Azure SQL 受控執行個體的錯誤和暫時性錯誤處理。

疑難排解封鎖

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

疑難排解的步驟：

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

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

收集封鎖資訊

為了應對疑難排解封鎖問題的困難，資料庫管理員可使用 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_text 的 text 欄位所傳回資料為 NULL，則目前未執行查詢。在這種情況下，sys.dm_exec_input_buffer 的 event_info 欄位將包含傳送至 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 執行序/工作層的 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 中的鎖死。

識別並解決常見封鎖案例

藉由檢查上述的資訊，您可以判斷大部分封鎖問題的原因。本文的其餘部分將討論如何使用這項資訊來識別及解決一些常見的封鎖案例。本討論將假設您已使用封鎖指令碼 (參考前文) 擷取封鎖 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 資料行值小於 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 範本資料庫，物件識別碼 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
, 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)、在 sys.dm_exec_sessions 中 SPID 所提交最後批次的 last_request_start_time 為何、在 sys.dm_exec_requests 中使用 start_time 的要求執行時間為何，以此類推。

常見的封鎖案例

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

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

案例	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，而 SPID 保留鎖定且交易隔離等級為預設 (READ COMMMITTED)，則這可能是原因。
4	不定	>= 0	可執行的	否。在用戶端取消查詢或關閉連線之前，將無法解決。可以終止 SPID，但可能最多需要 30 秒。	針對位於封鎖鏈前端的 SPID，`sys.dm_exec_sessions` 中的 `hostname` 資料行會與其封鎖的其中一個 SPID 相同。
5	NULL	>0	復原	是。	此 SPID 的擴充事件工作階段中可能出現注意訊號，表示查詢逾時或已發生取消，又或者是僅表示已發行 ROLLBACK 陳述式。
6	NULL	>0	睡眠中	最終，當 Windows NT 判斷工作階段已不再處於作用中狀態時，Azure SQL 資料庫連線將會中斷。	`sys.dm_exec_sessions` 中的 `last_request_start_time` 值會早於目前時間。

詳細的封鎖案例

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

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

強烈建議使用 SSMS 中查詢存放區的報告，其為實用工具以用於識別成本最高的查詢、效能不佳的執行計畫。另請參閱 Azure SQL Database 中 Azure 入口網站的智慧型效能一節，包含查詢效能深入解析。

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

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

此類型的封鎖通常可由睡眠中或等待命令的 SPID 識別，但其交易巢狀層級 (@@TRANCOUNT、自 sys.dm_exec_requests 的 open_transaction_count) 大於零。如果應用程式發生查詢逾時，或發行取消時未同時發行必要的 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 和終止交易，也不會在此類案例中執行此復原程式碼。
  - 如果開啟連線的應用程式正在使用連線共用 (Connection Pooling)，並在將連線釋回集區之前執行少量的查詢 (例如 Web 應用程式)，則請暫時停用連線共用可能有助於緩解問題，直到將用戶端應用程式修改為可適當處理錯誤為止。透過停用連線共用，釋出連線將會造成 Azure SQL 資料庫連線的實體中斷連線，進而導致伺服器復原任何未結案的交易。
  - 您可使用 SET XACT_ABORT ON 以供連線，或用於開始交易且未清除後續錯誤的任何預存程序。如果執行階段發生錯誤，則此設定將終止任何未結案的交易並將控制項傳回至用戶端。如需詳細資訊，請參閱 SET XACT_ABORT (Transact-SQL)。
注意

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

警告

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

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

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

注意

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

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

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

由於在 2019 年推出的加速資料庫復原功能，因此冗長復原應該極少發生。

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

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

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

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

共用方式為

了解並解決 Azure SQL 資料庫封鎖問題

目標

了解封鎖

最佳化鎖定

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

收集擴充事件的資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

意見反應

其他資源

共用方式為

了解並解決 Azure SQL 資料庫封鎖問題

目標

了解封鎖

最佳化鎖定

應用程式和封鎖

疑難排解封鎖

收集封鎖資訊

收集 DMV 的資訊

收集擴充事件的資訊

識別並解決常見封鎖案例

分析封鎖資料

常見的封鎖案例

詳細的封鎖案例

相關內容

意見反應

其他資源