如何在 Amazon Redshift 中檢測和釋放鎖？

簡短描述

當您頻繁對使用者資料表或資料操作語言 (DML) 查詢執行的資料定義語言 (DDL) 陳述式時，可能會遇到鎖定衝突。

Amazon Redshift 有以下三種鎖定模式：

• AccessExclusiveLock 會封鎖所有其他鎖定嘗試，且主要是在 DDL 作業期間取得，例如 ALTER TABLE、DROP 或 TRUNCATE。
• AccessShareLock 僅會封鎖 AccessExclusiveLock 嘗試，並在 UNLOAD、SELECT、UPDATE 或 DELETE 作業期間取得。AccessShareLock 不會封鎖試圖讀取或寫入資料表上的其他工作階段。
• ShareRowExclusiveLock 會封鎖 AccessExclusiveLock 和其他 ShareRowExclusiveLock 嘗試，但不會封鎖 AccessShareLock 嘗試。ShareRowExclusiveLock 是在 COPY、INSERT、UPDATE 或 DELETE 作業期間取得的。

若要解決此問題，請識別問題資料表鎖定、識別問題查詢 (如有必要)，然後釋放問題資料表鎖定。

解決方法

**注意：**如果您在執行 AWS Command Line Interface (AWS CLI) 命令時收到錯誤訊息，請參閱對 AWS CLI 錯誤進行疑難排解。此外，請確定您使用的是最新的 AWS CLI 版本。

偵測鎖定

若要識別持有鎖定的程序，請執行下列查詢：

SELECT
  a.txn_start,
  datediff(s,a.txn_start,getdate())/86400||' days '||datediff(s,a.txn_start,getdate())%86400/3600||' hrs '||datediff(s,a.txn_start,getdate())%3600/60||' mins '||datediff(s,a.txn_start,getdate())%60||' secs' AS txn_duration,
  a.txn_owner,
  a.txn_db,
  a.pid,
  a.xid,
  a.lock_mode,
  a.relation AS table_id,
  nvl(trim(c."table"),d.relname) AS tablename,
  a.granted,
  b.pid AS blocking_pid
FROM SVV_TRANSACTIONS a
LEFT JOIN (SELECT pid, relation, granted FROM PG_LOCKS GROUP BY 1,2,3) b
  ON a.relation = b.relation AND a.granted='f' AND b.granted='t'
LEFT JOIN (SELECT * FROM SVV_TABLE_INFO) c
  ON a.relation = c.table_id
LEFT JOIN PG_CLASS d
  ON a.relation = d.oid
WHERE
  a.relation IS NOT NULL
  AND txn_db = 'database name'
  AND tablename = 'table name';

若要偵測特定資料表中的鎖定，請將資料庫名稱替換為您的資料庫名稱，並將資料表名稱替換為您的表名稱。

**注意：**您可以在 Amazon Redshift 預先設定叢集和 Amazon Redshift Serverless 上執行上述查詢。

輸出範例：

         txn_start         |        txn_duration         | txn_owner | txn_db |    pid     |   xid   |      lock_mode      | table_id | tablename | granted | blocking_pid
---------------------------+-----------------------------+-----------+--------+------------+---------+---------------------+----------+-----------+---------+--------------
 2025-02-07 15:22:54.62833 | 0 days 0 hrs 3 mins 46 secs | admin     | dev    | 1073905801 | 3950326 | AccessExclusiveLock |  1410058 | abctbl    | t       |             
 2025-02-07 15:22:57.67816 | 0 days 0 hrs 3 mins 43 secs | admin     | dev    | 1073963119 | 3950380 | AccessShareLock     |  1410058 | abctbl    | f       |   1073905801

如果已授與欄中的結果為 f (false)，則表示該交易正在等待鎖定，因為該鎖定已由其他交易佔用。blocking_pid 欄會顯示持有該鎖定的工作階段程序 ID (PID)。

偵測問題查詢

如果特定資料表的鎖定問題持續發生，請使用 SYS_QUERY_HISTORY 來查看是哪個查詢導致了該問題。

SELECT * FROM SYS_QUERY_HISTORY WHERE transaction_id = transaction ID;

釋放鎖定

若要釋放鎖定，請完成下列步驟：

1. 等待持有鎖定的交易釋放。
2. 執行 PG_TERMINATE_BACKEND 函式以手動釋放鎖定。
   **注意：**當命令成功請求程序停止時，PG_TERMINATE_BACKEND(PID) 查詢會傳回值 1。最佳實務是檢查 SYS_SESSION_HISTORY，以確認該程序已停止。
3. 對於 Redshift 佈建叢集，如果 PG_TERMINATE_BACKEND 未成功停止該程序，請執行 REBOOT_CLUSTER 函式。
   **注意：**REBOOT\ _CLUSTER 會在不關閉連線的情況下重新啟動叢集。
4. 對於 Redshift 佈建叢集，如果 REBOOT_CLUSTER 未成功停止該程序，則從 Amazon Redshift 主控台重新啟動集群或執行 restart-cluster AWS CLI 命令。
   **注意：**reboot-cluster 會關閉所有目前的連線。