對 Fabric Data Warehouse 中的查詢封鎖問題進行疑難排解

適用於:Microsoft Fabric 中的✅ 資料庫

如果你在 Warehouse 中執行的查詢花費異常長的時間,或似乎卡住,其中一個可能原因是鎖定。 當某個工作階段持有鎖,導致其他查詢無法繼續執行時,就會發生鎖定。

本文將教你如何判斷鎖門是否影響你的工作量,以及你可以採取哪些行動。

Tip

Warehouse 使用 資料表層級鎖定。 任何 DML 操作都會鎖定整個資料表,不論受影響多少列。 此行為與支援列級與頁面級鎖的 SQL Server 不同。

先決條件

  • 一個有活躍工作負載的倉庫。
  • Viewer 工作區角色成員資格是查詢本文所述動態管理檢視(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;

定期監控未完成交易並在適當時介入,有助於降低阻塞鏈形成的可能性。