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

簡単な説明

ロックは、テーブルに同時にアクセスできるセッション数を制御する保護メカニズムです。ロックは、それらのセッションで実行できる操作も決定します。ほとんどのリレーショナルデータベースは行レベルのロックを使用します。ただし、Amazon Redshift はテーブルレベルのロックを使用します。ユーザーテーブルまたはDML クエリで頻繁にDDLステートメントを実行すると、ロックの競合が発生する可能性があります。

Amazon Redshift には 3 つのロックモードがあります：

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

クエリまたはトランザクションがテーブルのロックを取得すると、そのロックはクエリまたはトランザクションの期間中維持されます。同じロックの取得を待機している他のクエリまたはトランザクションは、AccessShareLock を除いてブロックされます。クエリがハングする理由の詳細については、クエリがハングするをご参照ください。

ロックに関する問題を解決するには、ロックを保持しているセッション (PID) を特定してから、セッションを終了します。セッションが終了しない場合は、クラスターを再起動します。

解決方法

クエリを実行して、ロックを保持しているセッションを特定します：

select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c."name"),d.relname) as tablename, a.granted,b.pid as blocking_pid ,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
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 stv_tbl_perm where slice=0) c 
on a.relation=c.id 
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

出力は次のようになります：

txn_owner | txn_db |   xid   |  pid  |         txn_start          |      lock_mode      | table_id | tablename | granted | blocking_pid |        txn_duration         | 
----------+--------+---------+-------+----------------------------+---------------------+----------+-----------+---------+--------------+-----------------------------+
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessExclusiveLock |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |
 usr1     | db1    | 5559927 | 20450 | 2018-06-30 10:52:19.761199 | AccessShareLock     |   351959 | lineorder | f       |        19813 | 0 days 0 hrs 0 mins 30 secs |
 usr1     | db1    | 5559898 | 19813 | 2018-06-30 10:51:57.485722 | AccessShareLock     |   351959 | lineorder | t       |              | 0 days 0 hrs 0 mins 52 secs |

付与 された列の結果が f (false) の場合、別のセッションのトランザクションがロックを保持していることを意味します。blocking_pid 列には、ロックを保持しているセッションのプロセス ID が表示されます。この例では、PID 19813 はロックを保持しています。

ロックを解放するには、ロックを保持しているトランザクションが終了するまで待ちます。次のコマンドを実行して、セッションを手動で終了することもできます。

select pg_terminate_backend(PID);

PID を終了すると、実行中のすべてのトランザクションがロールバックされ、セッション内のすべてのロックが解放されます。ロックは、ロックの取得を待機している他のトランザクションによって要求されます。

注意: PG_TERMINATE_BACKEND(PID) が「1」を返した場合、通常、PID への終了リクエストが成功したことを示します。ただし、PID が実際に終了したことを保証するものではありません。内部ステータスによっては PID を特定できない場合があります。したがって、STV_SESSIONS (および他の関連するシステムテーブル) を確認して、PID が実際に終了したかどうかを確認することもお勧めします。

PG_TERMINATE_BACKEND(PID)が正常に終了しなかった場合は、クラスターを再起動してプロセスを終了します。再起動は、Amazon Redshift コンソールでクラスターを再起動するか、REBOOT_CLUSTER を使用してデータベースを再起動することで実行できます。

関連情報

ロック (Amazon Redshift)

同時書き込みトランザクションの考えられるデッドロック状況