共用方式為

了解並解決封鎖問題

  • 發行項

適用於：Azure SQL 資料庫Fabric 中的 SQL 資料庫

本文說明 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 可在單一伺服器上支援數千位同時上線的使用者，並幾乎不會封鎖。

備註

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

排除封鎖

無論處於何種封鎖情況，疑難排解封鎖的方法皆相同。 這些邏輯區分決定了本文其餘內容的構成。 概念是找出前端封鎖程式，並識別該查詢的執行項目和封鎖原因。 一旦識別出問題的查詢（即，長時間鎖住系統的原因），下一步就是分析和確定為什麼會發生阻塞。 在瞭解原因之後，我們可以藉由重新設計查詢和交易來進行變更。

疑難排解的步驟：

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

  2. 尋找導致封鎖的查詢和交易（長時間持有鎖定的原因）

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

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

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

收集封鎖資訊

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

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

收集「車輛管理局」(DMV)的資訊

參考 DMV 以疑難排解封鎖的目標在於識別在封鎖鏈開頭的 SPID (工作階段識別碼) 和 SQL 陳述式。 尋找被封鎖的目標 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 批次文本或輸入緩衝區文本。 如果 textsys.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;

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 會在物件總管中的每個資料庫下提供擴充事件子資料夾。 使用擴充事件工作階段精靈以擷取下列實用事件：

  • 類別錯誤：

    • 注意
    • 報錯
    • 執行警告

  • 類別警告：

    • 缺少連接條件

  • 類別執行：

    • 執行遠端程序調用完成
    • Rpc_starting
    • SQL批次完成
    • SQL 批次開始 (Sql_batch_starting)

  • 類別 deadlock_monitor

    • 資料庫_XML_死鎖報告

  • 類別工作階段

    • 現有連接
    • 登入
    • 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 會對應至 index_id 261575970 的 object_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) 內，由 SPID 提交的最後批次在 last_request_start_time 中的 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。

情境 等待類型 Open_Tran 狀態 解決？ 其他徵狀
1 不可為空 >= 0 可運行的 是，當查詢完成時。 在 sys.dm_exec_sessions中，reads、cpu_time和/或 memory_usage 數據行會隨著時間而增加。 完成時，查詢的持續時間很高。
2 無 >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 無 >0 復原 是。 此 SPID 的擴充事件工作階段中可能出現注意訊號，表示查詢逾時、取消操作，或僅是已執行 ROLLBACK 陳述式。
6 NULL >0 睡覺 最終， 當 Windows 判斷會話不再作用中時，Azure SQL Database 聯機會中斷。 last_request_start_time 中的 sys.dm_exec_sessions 值比目前時間早得多。

詳細的封鎖案例

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

    解決方式：此類型的封鎖問題解決方法便是尋找最佳化查詢的方式。 實際上，這類封鎖問題可能只是效能問題，因此您需要以這個角度來處理它。 如需針對特定執行緩慢查詢進行疑難排解的詳細資訊，請參閱如何針對 SQL Server 上的執行緩慢查詢進行疑難排解。 如需詳細資訊，請參閱監視及調整效能。

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

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

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

  2. 若睡眠中 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 語句。 這可能會影響現有程式碼的預期流程。

  3. 如果 SPID 的對應用戶端應用程式未完整擷取所有結果資料列，便會造成封鎖

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

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

    注意

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

    解決方法：應用程式必須重寫以完整擷取所有結果資料列。 這並不會排除在查詢中的 ORDER BY 子句使用 OFFSET 和 FETCH 來執行伺服器端分頁。

  4. 因會話處於復原狀態而導致封鎖

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

    由於 2019 年引進的 加速資料庫復原，冗長的回復操作應該很少發生。

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

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

  5. 孤立連線所造成的封鎖

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

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

    KILL 99

相關內容