PostgreSQL 実践講座 #6 ロックと同時実行 — デッドロック、DDL ロック、SKIP LOCKED

インフラ PostgreSQL データベース
読了 4分

基礎第 6 回の結論は「MVCC のおかげで読み取りと書き込みは互いを妨げない」でした。残っているのが書き込みと書き込みです。同じ行を 2 つのトランザクションが同時に変えようとするときにはロックが登場し、ここで実戦の事件(突然の待ち、デッドロック、マイグレーションがサービスを止める事故)が起きます。

ロックの 2 つの層 #

  • 行ロック: UPDATE・DELETE は対象の行をロックします。他のトランザクションが同じ行を変えようとすると、先のトランザクションが終わるまで待ちます。別の行なら互いに無関係です。だから行ロックの問題はたいてい「ホットロウ」、つまり全員が変える少数の行(集計カウンタ、人気商品の在庫)で起きます。
  • テーブルロック: DDL が取る層です。実践第 1 回で見たとおり、ALTER TABLE の ACCESS EXCLUSIVE は SELECT まで止め、もっと怖いのは待っている DDL の後ろにすべてのクエリが並ぶ現象でした。lock_timeout がその保険でした。

覚えておく原則は 1 つです。ロックはトランザクションが終わってやっと解放されます。 文が終わってもトランザクションが開いたままなら、ロックは維持されます。実践第 3 回の idle in transaction が危険な 3 つ目の理由です(ロックの保持、VACUUM の妨害に続いて)。

誰が誰を止めているのか #

障害の最中に「クエリが終わらない」と連絡が来たら、待ちの連鎖を追跡します。

ロック待ちの確認 
-- ロック待ち中のクエリと、それを止めている pid
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       state, now() - query_start AS waiting_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids() が「この pid を止めている pid の一覧」を直接返してくれるので、生のビューである pg_locks を手で結合しなくても連鎖の根っこを見つけられます。根っこが idle in transaction や暴走クエリなら、SELECT pg_terminate_backend(pid); で切るのが最後の手段です。

デッドロック: 構造と予防 #

デッドロックは、2 つのトランザクションが互いの握るロックを待ち合う状態です。構造はいつも同じです。A が 1 番の行を握って 2 番を待ち、B は 2 番を握って 1 番を待ちます。PostgreSQL はこれを検出して片方をエラーで切ってくれるので、システムが永遠に止まることはありませんが、切られた側のリクエストは失敗します。

予防ルールも構造からそのまま出ます。複数の行(または複数のテーブル)を変えるときは、いつも同じ順序で取ります。 振替のロジックなら「送る側を先に」ではなく「id が小さい口座を先に」のようにグローバルな順序を決めることです。1 文で複数の行を更新するときも WHERE id IN (...) のロック順は保証されないので、順序が重要なロジックはソートして取ります。

ロック順序の固定 
-- ロックの順序を明示的に固定
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

SELECT FOR UPDATE: 読んで-判断して-書くの安全装置 #

基礎第 6 回で「読んで判断して書くロジックは、まず原子的な 1 文にまとめるのが先」と言いました。1 文でできない場合(読んだ値で複雑な計算が必要なとき)の道具が SELECT ... FOR UPDATE です。読む時点でその行を UPDATE と同じ強さでロックして、自分の判断と書き込みの間に他人が割り込めないようにします。ロックする範囲は最小に、トランザクションは短く、が使い方のルールです。

SKIP LOCKED: DB で作る作業キュー #

FOR UPDATE の派生オプション 1 つが、パターンを丸ごと 1 つ作りました。ロックされた行は飛ばして次の行をくれ、という SKIP LOCKED です。

作業キューのクエリ 
-- ワーカーを複数同時に走らせても、それぞれ別の仕事を取っていきます
UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
    SELECT id FROM jobs
    WHERE status = 'queued'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING *;

ワーカー 10 個が同じクエリを同時に回しても、それぞれロックされていない最初の行を 1 つずつ取っていくので、重複処理も待ちもありません。別のメッセージキューを立てる前に、トランザクションと一緒に原子的に回る作業キューが必要なら、このパターンが PostgreSQL の標準の答えです。

まとめ #

  • MVCC が残した衝突は書き込み対書き込みです。行ロックはホットロウで、テーブルロックは DDL で事件が起きます。
  • ロックはトランザクションが終わってやっと解放されます。短いトランザクションが、あらゆるロック問題の半分を予防します。
  • 待ちの追跡は pg_blocking_pids で連鎖の根っこを見つけ、最後の手段は pg_terminate_backend です。
  • デッドロックの予防はグローバルなロック順序 1 つに要約されます。いつも同じ順序で取ります。
  • 読んで-判断して-書くは FOR UPDATE、作業キューは FOR UPDATE SKIP LOCKED が標準パターンです。次回はパーティショニングです。
X