【PostgreSQL】SELECT FOR UPDATEで行をロックする

SELECT FOR UPDATEとは

SELECT ... FOR UPDATEは、取得した行に対して排他的な行ロックを取得する構文である。ロックはトランザクションの終了まで保持され、他のトランザクションは同じ行に対するUPDATEやDELETE、SELECT FOR UPDATEで待たされる。

残高の読み取りと更新のように、読んだ値をもとに書き込む処理で使う。

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

BEGINからCOMMITまでの間、id = 1の行は他のトランザクションから更新できない。

SELECT FOR UPDATEを単体で実行すると、psqlの自動コミットによって文の終了と同時にトランザクションが終わり、ロックもその場で解放される。必ずBEGINからCOMMITまでの間で使う。

ロックがない場合に起きる問題

SELECTで読んだ値をアプリケーション側で計算し、その結果をUPDATEで書き戻す処理を考える。

-- セッションA
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- セッションB
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- セッションA
UPDATE accounts SET balance = 900 WHERE id = 1;
-- セッションB
UPDATE accounts SET balance = 1500 WHERE id = 1;

セッションAの減算は、後から実行されたセッションBのUPDATEで上書きされて消える。ロストアップデートと呼ばれる状態である。

SELECTにFOR UPDATEを付けると、セッションBはSELECTの時点で待たされる。

-- セッションA
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- セッションB
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- ここで待機

セッションAがCOMMITすると、セッションBのロック待ちが解除される。以下はpsqlで\timing onを有効にした状態のセッションBの出力である。

 id | name  | balance
----+-------+---------
  1 | alice |     900
(1 row)

Time: 2926.777 ms (00:02.927)

約3秒待たされたうえで、セッションAの更新後の値である900が返っている。デフォルトの分離レベルであるREAD COMMITTEDでは、ロック待ちから復帰した時点で行が読み直されるため、最新の値をもとに計算できる。

なおUPDATE accounts SET balance = balance - 100のように、SQLひとつで完結する更新はUPDATE自体が行ロックを取るためSELECT FOR UPDATEは不要である。読んだ値をアプリケーション側で加工したり、値によって処理を分岐したりする場合に使う。

待機後に0行になる場合がある

行が読み直される以上、待機中の更新によってWHERE句の条件を満たさなくなれば、その行は結果に含まれない。

BEGIN;
SELECT * FROM accounts WHERE id = 1 AND balance >= 500 FOR UPDATE;

 id | name | balance
----+------+---------
(0 rows)

Time: 1981.935 ms (00:01.982)

待機中に別のセッションが残高を50へ更新したため、条件を満たす行が消えている。ロックを取得できたなら必ず1行返る、と決めてかかるとバグの原因になる。0行のケースも扱えるように実装する。

REPEATABLE READ以上ではエラーになる

REPEATABLE READとSERIALIZABLEでは、待機の解除時に対象行が更新済みだとトランザクションが中断される。

ERROR:  could not serialize access due to concurrent update

分離レベルを上げる場合、アプリケーション側にトランザクションのリトライが必要である。

通常のSELECTはブロックされない

FOR UPDATEが阻害するのは、書き込みと行ロックの取得のみである。FOR UPDATEを付けないSELECTは待たされず、すぐに結果を返す。

SELECT * FROM accounts WHERE id = 1;

 id | name  | balance
----+-------+---------
  1 | alice |    1000
(1 row)

Time: 0.368 ms

セッションAのコミット前に実行したため、更新前の1000がすぐに返っている。読み取りの一貫性はMVCCで保証されており、行ロックには依存しない。

待たずにエラーにする(NOWAIT)

NOWAITを付けると、ロックを取得できない場合は待たずエラーになる。

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

ERROR:  could not obtain lock on row in relation "accounts"

ロック待ちによる処理の滞留を避けたい場面で使う。即座に失敗させてリトライやエラー応答に回せる。

ロック済みの行を飛ばす(SKIP LOCKED)

SKIP LOCKEDを付けると、他のトランザクションがロック中の行を結果から除外する。

SELECT * FROM accounts ORDER BY id FOR UPDATE SKIP LOCKED;

 id | name  | balance
----+-------+---------
  2 | bob   |    2000
  3 | carol |    3000
(2 rows)

id = 1はロック中のため結果に含まれない。複数のワーカーが同じテーブルからタスクを取り出すジョブキューの実装でよく使われる。各ワーカーが未処理の行をLIMIT 1で取得すれば、互いに競合せず別々の行を処理できる。

BEGIN;
SELECT id FROM jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE jobs SET status = 'running' WHERE id = [取得したid];
COMMIT;

SKIP LOCKEDはテーブルの一貫したビューを返さない。公式ドキュメントでもキューとして使うテーブル向けの機能と位置付けられており、集計や一覧表示のような汎用の用途には向かない。

なおNOWAITとSKIP LOCKEDは同時に指定できず、構文エラーになる。

待ち時間の上限を設ける(lock_timeout)

NOWAITでは即座に失敗するが、一定時間だけ待ちたい場合はlock_timeoutを設定する。

SET lock_timeout = '3s';
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (0,5) in relation "accounts"

lock_timeoutが適用されるのはロックの獲得待ちのみである。クエリ全体の実行時間を制限するstatement_timeoutとは別の設定である。

ロック待ちを確認する

ロック待ちが発生しているセッションはpg_stat_activityで確認できる。pg_blocking_pidsを併用すると、待たせている側のプロセスIDが分かる。

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid), left(query, 50) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND backend_type = 'client backend';

 pid | wait_event_type |  wait_event   | pg_blocking_pids |                       query
-----+-----------------+---------------+------------------+----------------------------------------------------
 110 | Timeout         | PgSleep       | {}               | SELECT pg_sleep(6);
 117 | Lock            | transactionid | {110}            | BEGIN; SELECT * FROM accounts WHERE id=1 FOR UPDAT
 124 |                 |               | {}               | SELECT pid, wait_event_type, wait_event, pg_blocki
(3 rows)

wait_event_typeがLockのセッションはロック待ちの状態にある。pg_blocking_pidsの返した110は、ロックを保持したままのセッションである。3行目は監視クエリ自身であり、stateがactiveのため結果に含まれる。

参考: 【PostgreSQL】pg_stat_activityで実行中クエリとロック待ちを確認する

ロックの強さを選ぶ

行ロックにはFOR UPDATEを含めて4種類のモードがある。下にいくほど弱いロックである。

複数のセッションが同じ行を参照するだけで更新しない場合、FOR SHAREなら互いに待たされずに済む。ただし全員がFOR SHAREで読んだあとにUPDATEしようとすると、互いのロック解放を待ち合ってデッドロックになる。更新の予定があるならFOR UPDATEを使う。

注意点

ロックの対象は取得した行のみ

FOR UPDATEがロックするのは、SELECTが実際に返した行だけである。条件に一致する行が存在しない場合は何もロックされないため、同じ行を同時にINSERTする競合は防げない。一意制約やINSERT ... ON CONFLICTで対処する。

参考: 【PostgreSQL】ON CONFLICTでUPSERT（INSERT or UPDATE）を1文で書く

ロック順序を揃える

複数の行をロックする処理では、セッションごとにロックの順序が異なるとデッドロックになる。ORDER BYを付けて常に同じ順序でロックを取得する。

SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

ただしREAD COMMITTEDでは、返る行の並び順が崩れる場合もある。ソートの後にロック待ちが発生し、待機中にソート対象の列が更新されるケースである。結果の並び順も保証したい場合は、副問い合わせ側にロック句を書いて外側でORDER BYする。

集約やDISTINCTとは併用できない

FOR UPDATEは、集約関数やGROUP BY、DISTINCT、UNIONなどを含むクエリでは使えない。

SELECT count(*) FROM accounts FOR UPDATE;

ERROR:  FOR UPDATE is not allowed with aggregate functions

トランザクションを短く保つ

ロックはトランザクションの終了まで解放されない。SELECT FOR UPDATEとCOMMITの間に外部APIの呼び出しなど時間のかかる処理を挟むと、待機するセッションが増えて接続数を圧迫する。ロック区間には更新に必要な処理だけを含める。

参考: 【PostgreSQL】接続数の上限と現在の接続数を確認する

参考

• PostgreSQL: SELECT
• PostgreSQL: Explicit Locking