適用於:Microsoft Fabric 中的✅ 資料庫
如果你在 Warehouse 中執行的查詢花費異常長的時間,或似乎卡住,其中一個可能原因是鎖定。 當某個工作階段持有鎖,導致其他查詢無法繼續執行時,就會發生鎖定。
本文將教你如何判斷鎖門是否影響你的工作量,以及你可以採取哪些行動。
Tip
Warehouse 使用 資料表層級鎖定。 任何 DML 操作都會鎖定整個資料表,不論受影響多少列。 此行為與支援列級與頁面級鎖的 SQL Server 不同。
先決條件
- 一個有活躍工作負載的倉庫。
- Viewer 工作區角色成員資格是查詢本文所述動態管理檢視(DMV)所需的最低必要權限。
- 如需更多有關使用 DMV 進行疑難排解的資訊,請參閱 使用 DMV 監視連線、工作階段和要求。
步驟 1:檢查查詢是否因鎖定而等待
先檢查是否有任何查詢目前正在等待鎖。
執行下列查詢:
SELECT
request_session_id,
resource_type,
resource_description,
request_mode,
request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';
若查詢有傳回資料列,表示某些工作階段正在等待其他工作階段所持有的資源。 每一列都代表目前無法授予的鎖定請求。
Tip
sys.dm_tran_locks視圖可回傳大量已確定鎖的列。 過濾 則 request_status = 'WAIT' 聚焦於被封鎖的會話。
步驟 2:識別被封鎖的查詢
接著,檢查哪些查詢被阻擋,以及是哪個會話阻擋了它們。
SELECT
session_id,
status,
blocking_session_id,
wait_type,
total_elapsed_time,
open_transaction_count
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
此查詢會傳回:
- 執行遭到封鎖之查詢的工作階段(
session_id) - 目前正在阻擋它的工作階段(
blocking_session_id) - 等待了多久(
total_elapsed_time以毫秒計) - 封鎖會話是否有未完成的交易(
open_transaction_count)
如果查詢顯示 blocking_session_id 不為零且 open_transaction_count > 0,表示它正在等待另一個持有鎖的工作階段。
步驟三:找到阻塞時段
要了解被鎖定的資源,請檢查阻塞會話目前持有的鎖。 將你先前識別出的 session_id 取代下列範例查詢中的 <blocking_session_id>:
SELECT
request_session_id,
resource_type,
resource_associated_entity_id,
request_mode,
request_status
FROM sys.dm_tran_locks
WHERE
request_status = 'GRANT'
AND request_session_id = <blocking_session_id>;
請使用此查詢來判斷:
- 阻擋會話目前鎖定哪些資源? 例如,你可以使用
sys.objects來識別其中resource_type = OBJECT的resource_associated_entity_id。 - 鎖定模式(例如,獨佔(
X)、Schema-Modification(Sch-M)) - 鎖定是否與 DDL 操作或統計更新
UPDSTATS相關()
Note
與統計相關的鎖(如來自 UPDSTATS的)也出現在 sys.dm_tran_locks中。 結構描述修改 (Sch-M) 鎖和獨佔 (X) 鎖是造成封鎖的最常見原因,但任何鎖定類型都可能封鎖彼此衝突的要求(例如,Sch-S 會封鎖 Sch-M)。
步驟 4:找到封鎖交易的擁有者
在許多情況下,你可能會希望要求封鎖交易的擁有者COMMIT或ROLLBACK其工作,而不是終止工作階段。 考慮使用 TRYCATCH 帶有 COMMIT 或 ROLLBACK的結構來處理錯誤。 如需詳細資訊,請參閱 TRY...CATCH。
你可以辨識出與阻塞會話相關的擁有者和查詢。 將你先前識別出的 session_id 取代下列範例查詢中的 <blocking_session_id>:
SELECT
r.session_id,
s.login_name,
s.program_name,
r.status,
r.blocking_session_id,
r.command,
r.total_elapsed_time,
s.last_request_start_time,
s.last_request_end_time
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
WHERE r.session_id = <blocking_session_id>;
-
login_name是阻塞會話的擁有者。 -
program_name是啟動該會話的應用程式。 該DMS_user值表示 Fabric 入口網站查詢編輯器。 -
command是目前執行中的指令。
接著你可以聯絡該交易的擁有者,請其視情況認可或回滾該交易。
步驟五:檢查封鎖工作是否處於閒置狀態或未進行中
封鎖工作階段可能看似處於活動狀態,但實際上並未真正取得進展。
閒置或停滯的工作階段跡象包括:
-
status = 'sleeping'表示沒有正在執行的查詢。 - 尋找明顯早於目前時間的
last_request_start_time,這表示有一個長時間維持開啟狀態的請求。 - 找出在兩次檢查之間未增加的
total_elapsed_time,這表示工作階段已停滯。
SELECT
session_id,
status,
last_request_start_time,
last_request_end_time,
open_transaction_count
FROM sys.dm_exec_sessions
WHERE session_id = <blocking_session_id>;
- 如果工作階段狀態為
sleeping和open_transaction_count > 0,則該工作階段有一個開啟的交易,但沒有作用中的查詢——也就是說,它持有鎖卻沒有執行任何工作。 - 如果該工作階段出現在
sys.dm_exec_requests中,且total_elapsed_time在兩次檢查之間持續增加,則表示該工作階段正在持續進行中。 或許等待交易完成會比終止交易並強制回滾更為可取。
步驟六:採取行動解決阻塞問題
Note
阻塞情況通常會在阻塞會話完成交易後自行解決。 如果你的工作量能承受延遲,等待是最安全的選擇。
如果你需要解除下游查詢的阻擋,請考慮阻塞會話是否具備以下特性:
- 有未完成的交易
- 看起來閒置或沒有進展
- 是持有阻塞鎖(例如,Exclusive(
X)或Sch-M)
若有,管理員工作區角色的成員可透過以下方式終止會話:
KILL <session_id>;
KILL 命令將會:
- 結束本次會議
- 復原該工作階段中作用中交易內已完成的所有工作
- 解開鎖
- 允許下游查詢繼續進行
注意事項
終止工作階段會回復該工作階段所執行的所有未提交作業。 此動作可能會撤銷使用者或應用程式所做的資料變更。 只有在確定終止交易不會對你的工作量造成負面影響時,才使用這個選項。
步驟七:預防未來鎖定問題
為了預防類似問題:
- 避免讓明確交易維持在開啟狀態(只有
BEGIN TRANSACTION而沒有對應的COMMIT或ROLLBACK)。 - 讓交易維持短暫。 只執行交易中必要的操作。
- 請務必在交易完成後
COMMIT或ROLLBACK。 - 將 DDL 作業(例如
ALTER TABLE)安排在低流量時段進行。
主動監控未完成的交易,方法包括:
SELECT
session_id,
login_name,
open_transaction_count,
program_name,
status,
blocking_session_id,
last_request_start_time
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0;
定期監控未完成交易並在適當時介入,有助於降低阻塞鏈形成的可能性。