Amazon Redshift でロックを検出して解放する方法を教えてください。

簡単な説明

ユーザーテーブルまたはデータ操作言語 (DML) クエリに対してデータ定義言語 (DDL) ステートメントを頻繁に実行すると、ロックの競合が発生する可能性があります。

Amazon Redshift には 3 種類のロックモードがあります。

• AccessExclusiveLock は、他のすべてのロック試行をブロックし、主に ALTER TABLE、DROP、TRUNCATE などの DDL 操作中に取得されます。
• AccessShareLock は、AccessExclusiveLock の試行のみをブロックし、UNLOAD、SELECT、UPDATE、DELETE 操作中に取得されます。AccessShareLock は、テーブルの読み取りまたは書き込みを試行する他のセッションをブロックしません。
• ShareRowExclusiveLock は、AccessExclusiveLock およびその他の ShareRowExclusiveLock の試行をブロックしますが、AccessShareLock の試行はブロックしません。ShareRowExclusiveLock は、COPY、INSERT、UPDATE、DELETE 操作中に取得されます。

この問題を解決するには、問題となるテーブルロックを特定し、必要に応じて問題となるクエリを特定してから、問題となるテーブルロックを解除します。

解決策

注: AWS コマンドラインインターフェイス (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';

特定のテーブルにおけるロックを検出するには、実際のケースに合わせ、database name をデータベース名に、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

granted 列に表示される結果が 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 コンソールからクラスターを再起動するか、AWS CLI コマンド reboot-cluster を実行します。
   注: reboot-cluster は、現在の接続をすべて閉じます。