ロックと同時実行の制御

概要

ロックは、同じデータを同時に書き換えて結果が壊れないよう、他のトランザクションを待たせる仕組みです。DEA-C01 では、のロードと利用者のクエリがぶつかったときに、やのロックをどう管理するか (スキル 2.1.6) が問われます。

主要技術

①Redshift のロックの種類

はテーブル単位でロックします。

ロック	取る操作	止める相手
AccessExclusiveLock	ALTER TABLE、DROP、TRUNCATE などの DDL	ほかのすべてのロック (SELECT も待つ)
ShareRowExclusiveLock	、INSERT、UPDATE、DELETE	他の書き込みと DDL (SELECT は止めない)
AccessShareLock	SELECT、、UPDATE・DELETE の読み取り	DDL だけ

ロックはトランザクションがコミットかロールバックするまで解放されません。DDL を含むトランザクションを開いたままにすると、ダッシュボードのクエリまで止まります。

②ロック待ちの調べ方と解消

手段	内容
STV_LOCKS	現在のロックの保持者と状態 (スーパーユーザーのみ)
SVV_TRANSACTIONS	開いたままのトランザクションとロック待ち
STL_TR_CONFLICT	過去のロックの衝突の記録
PG_TERMINATE_BACKEND(pid)	ロックを握ったままのセッションを終了してロックを解放する
LOCK コマンド	トランザクションの先頭で明示的にロックを取り、後から割り込まれないようにする

③分離レベルと同時書き込みの衝突

の分離レベルは SNAPSHOT (新しいクラスターと Serverless のワークグループの既定) と SERIALIZABLE の2つです。SERIALIZABLE では、直列に実行したときと同じ結果にならない同時書き込みがあると、片方がエラー 1023 (シリアライズ可能分離の違反) で取り消されます。対処は、書き込むテーブルの順番をそろえる、LOCK で先にロックを取る、失敗したら再試行する、のいずれかです。複数のテーブルを逆の順番で更新する2つのトランザクションは、互いを待ってデッドロックになります。

④RDS / Aurora のロック

(MySQL の InnoDB、PostgreSQL) は行単位でロックします。長時間のトランザクションや大量の UPDATE は、他の処理を待たせ、デッドロックを起こします。抽出ジョブは本番のライターではなくから読み、更新は小さなバッチに分けてこまめにコミットします。ロック待ちは、MySQL の Performance Schema や PostgreSQL の pg_locks・pg_stat_activity で調べます。

試験対策

問題文のキーワード	選ぶもの
中にダッシュボードのクエリが止まる	DDL を含む長いトランザクションを避ける、TRUNCATE と ALTER の時間をずらす
どのセッションがロックを握っているか調べたい	STV_LOCKS、SVV_TRANSACTIONS
ロックを握ったままのセッションを解放	PG_TERMINATE_BACKEND
同時書き込みでエラー 1023 が出る	書き込みの順番をそろえる、LOCK、再試行
抽出ジョブが本番 DB の更新を待たせる	から抽出する