はじめに
PostgreSQLで在庫数の管理を実装した際に、AIがFOR UPDATEではなくFOR NO KEY UPDATE（この記事の対処法1）で実装していました。
理由を調べたところ、子テーブルにINSERTした後に、外部キーで参照している親の行を FOR UPDATEでロックするという順番だと、同じ商品への注文が2件同時に来ただけでデッドロックすることがわかりました。
原因は、外部キー制約が裏で親の行にかけるロックです。
この記事では、なぜ FOR UPDATE だとデッドロックするのか、FOR NO KEY UPDATE だと何が違うのかをまとめます。
Dockerだけでデッドロックを再現する手順も載せています。
題材にするテーブルと購入処理
商品と注文の2つのテーブルを使います。orders.product_idはproducts.idを外部キーで参照します。
CREATE TABLE products (
id int PRIMARY KEY,
stock int NOT NULL
);
CREATE TABLE orders (
id serial PRIMARY KEY,
product_id int NOT NULL REFERENCES products(id)
);
INSERT INTO products VALUES (1, 10);
1件の購入は、次の順番で処理するとします。
BEGIN ISOLATION LEVEL READ COMMITTED;
INSERT INTO orders (product_id) VALUES (1);
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- アプリケーションで取得した在庫を確認する。
-- 在庫が1未満ならROLLBACKし、以降の処理を実行しない。
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
上のSQLでは、アプリケーション側の在庫不足の分岐はコメントで示すにとどめています。再現では、初期在庫10に対して2件の購入だけを実行します。
注文の記録と在庫の減算が同じトランザクションにあれば、在庫不足の際にROLLBACKして注文も取り消せます。
ただし上記の順番では、2件の購入が重なると、在庫の減算に進む前にデッドロックになることがあります。
原因は、外部キー検査による親行のロック
今回の外部キー制約では、ordersにINSERTすると、参照先のproductsの行が存在するか検査されます。その際、親行に FOR KEY SHARE という行ロックを取得します。
確認後に参照先の行が削除されたり、キーを変更されたりするのを防ぐためです。この検査は、PostgreSQL 16の実装（ri_triggers.c）ではSELECT ... FOR KEY SHAREとして確認できます。
外部キー検査の実装
ロックについて次の2点が重要です。
- この例で取得する行ロックは、
COMMITまたはROLLBACKまで保持される。 - 同じ行に対する
FOR KEY SHAREとFOR UPDATEは、異なるトランザクション間で競合する。
両方のトランザクションがINSERTを終えてからFOR UPDATEを要求すると、次の状態になります。
| 順番 | トランザクションA | トランザクションB |
|---|---|---|
| 1 | INSERTし、親行の FOR KEY SHARE を保持 |
|
| 2 | INSERTし、同じ親行の FOR KEY SHARE を保持 |
|
| 3 |
FOR UPDATE を要求し、Bのロックの解放を待つ |
|
| 4 |
FOR UPDATE を要求し、Aのロックの解放を待つ |
AはBを待ち、BはAを待ちます。これがデッドロックです。
一方、片方だけが相手の終了を待ち、相手は処理を進められる状態なら、通常のロック待ちです。
2件が同時に実行されれば必ず発生する、というわけではありません。 Aが先にFOR UPDATEを取得していれば、BはINSERT時の外部キー検査で待機し、この循環は生じません。
PostgreSQLの行ロックと競合関係
行ロックには、次の4種類があります。
| ロック | 主な取得場面 |
|---|---|
FOR UPDATE |
SELECT ... FOR UPDATE、DELETE、キー列を変更する UPDATE
|
FOR NO KEY UPDATE |
SELECT ... FOR NO KEY UPDATE、キー列を変更しない UPDATE
|
FOR SHARE |
SELECT ... FOR SHARE |
FOR KEY SHARE |
SELECT ... FOR KEY SHARE、今回の外部キー検査 |
ここでいうキー列は、外部キーの参照先に使える一意インデックスの列です。主キーだけを指すわけではありません。
異なるトランザクションが同じ行をロックする場合の競合は、次のとおりです。×が競合、○が共存可能を表します。
| 要求する＼保持されている | FOR KEY SHARE | FOR SHARE | FOR NO KEY UPDATE | FOR UPDATE |
|---|---|---|---|---|
| FOR KEY SHARE | ○ | ○ | ○ | × |
| FOR SHARE | ○ | ○ | × | × |
| FOR NO KEY UPDATE | ○ | × | × | × |
| FOR UPDATE | × | × | × | × |
今回注目するのは、FOR NO KEY UPDATEがFOR KEY SHAREと共存でき、自分と同じFOR NO KEY UPDATEとは競合する点です。
公式ドキュメント：行レベルロック
Dockerでデッドロックを再現する
コンテナとテーブルを準備する
デモ専用のPostgreSQLコンテナを起動します。psqlもコンテナ内のものを使います。
docker run -d --rm --name lockdemo \
-e POSTGRES_PASSWORD=pw postgres:16
until docker exec lockdemo pg_isready -h 127.0.0.1 -U postgres >/dev/null 2>&1; do
sleep 1
done
ターミナルを2つ開き、両方で次を実行します。
docker exec -it lockdemo psql -X -U postgres
片方のpsqlで「題材にするテーブルと購入処理」のテーブル作成SQLを実行します。
2つのトランザクションを循環待ちにする
次の順番で、1行ずつ実行してください。
| 順番 | ターミナルA | ターミナルB |
|---|---|---|
| 1 | BEGIN ISOLATION LEVEL READ COMMITTED; |
|
| 2 | INSERT INTO orders (product_id) VALUES (1); |
|
| 3 | BEGIN ISOLATION LEVEL READ COMMITTED; |
|
| 4 | INSERT INTO orders (product_id) VALUES (1); |
|
| 5 |
SELECT stock FROM products WHERE id = 1 FOR UPDATE; → 待機 |
|
| 6 |
SELECT stock FROM products WHERE id = 1 FOR UPDATE; → デッドロック |
どちらか一方に、次のエラーが出ます。中断される側は固定ではなく、待機を始めてからdeadlock_timeout（後述）が経過した時点で循環待ちができていた側が中断されます。手動で1行ずつ実行する場合は、後から待機に入ったターミナルB側で出ることがほとんどです。
ERROR: deadlock detected
もう一方のSELECTはロックを取得でき、在庫 10 を返します。この時点では、どちらも在庫を減らしていないためです。
エラーになった側ではROLLBACK;を実行します。成功した側では、次を実行して購入を完了させます。
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
この場合、最終的な在庫は9、注文数は1になります。デッドロックで失敗した側の注文は取り消されています。
検知までの時間について
PostgreSQLは、ロック待機がdeadlock_timeoutに達すると、デッドロックの有無を検査します。デフォルトは1秒です。
これは「ロック待ちを1秒で打ち切る」という設定ではありません。循環待ちがあれば関係するトランザクションのいずれかを中断し、単なるロック待ちなら待機を続けます。
公式ドキュメント：deadlock_timeout
次の検証に向けて初期化する
以降の比較では、毎回、両方のpsqlでトランザクションを終了させてから初期化します。
-- 両方のpsqlで実行する。
ROLLBACK;
トランザクションがなければ警告が出ますが、そのまま進められます。その後、片方だけで次を実行します。
TRUNCATE orders RESTART IDENTITY;
UPDATE products SET stock = 10 WHERE id = 1;
対処法1：FOR NO KEY UPDATEを使う
今回のように、キー列を変更せず在庫数だけを更新する処理では、ロックをFOR NO KEY UPDATEに変更できます。
SELECT stock FROM products WHERE id = 1 FOR NO KEY UPDATE;
外部キー検査のFOR KEY SHAREと共存できるため、今回の循環待ちを回避できます。一方、FOR NO KEY UPDATE同士は競合するので、在庫の確認と更新は1件ずつ進みます。
初期化後、次の順番で確認できます。
| 順番 | ターミナルA | ターミナルB |
|---|---|---|
| 1 | BEGIN ISOLATION LEVEL READ COMMITTED; |
|
| 2 | INSERT INTO orders (product_id) VALUES (1); |
|
| 3 | BEGIN ISOLATION LEVEL READ COMMITTED; |
|
| 4 | INSERT INTO orders (product_id) VALUES (1); |
|
| 5 |
SELECT stock FROM products WHERE id = 1 FOR NO KEY UPDATE; → 10
|
|
| 6 |
SELECT stock FROM products WHERE id = 1 FOR NO KEY UPDATE; → 待機 |
|
| 7 | UPDATE products SET stock = stock - 1 WHERE id = 1; |
|
| 8 | COMMIT; |
→ 手順6のSELECTが再開し、9を返す |
| 9 | UPDATE products SET stock = stock - 1 WHERE id = 1; |
|
| 10 | COMMIT; |
SELECTが返すのは、それぞれの減算前の値である10と9です。購入完了後の最終在庫は8になります。
READ COMMITTEDでは、ロック待ちの間に他のトランザクションが対象行を更新してコミットすると、その更新後の行をロックして返します。
公式ドキュメント：Read Committed
ただし、後続処理でキー列を変更したりDELETEしたりすると、その操作時にFOR UPDATE相当のロックが必要です。その場合、事前のロックをFOR NO KEY UPDATEに変えるだけでは、今回の競合を回避できません。
対処法2：親の行を先にロックする
処理順を変え、注文のINSERTより先に在庫行をロックする方法もあります。
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- アプリケーションで在庫を確認し、1未満ならROLLBACKして終了する。
INSERT INTO orders (product_id) VALUES (1);
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
両方のトランザクションをこの順番にすると、Bは最初のFOR UPDATEでAの終了を待ちます。BはまだINSERTを実行しておらず、親行のFOR KEY SHAREを保持していないので、今回の循環は生じません。
また、A自身のFOR UPDATEと、AのINSERTによるFOR KEY SHAREは互いに妨げません。
この方法では、同じ行を扱う処理のロック順を揃える必要があります。複数の商品を扱う場合には、商品IDの昇順など、行をロックする順番も統一します。
対処法3：条件付きUPDATEで在庫の判定と減算をまとめる
「在庫があれば1個減らす」という処理なら、SELECTで先にロックせず、条件付きUPDATEで判定と減算をまとめる方法があります。
BEGIN ISOLATION LEVEL READ COMMITTED;
INSERT INTO orders (product_id) VALUES (1);
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0
RETURNING stock;
-- 1行返れば購入成功としてCOMMITする。
-- 0行なら購入失敗としてROLLBACKし、先にINSERTした注文も取り消す。
最後の分岐はアプリケーション側で実装します。この順番では、商品が存在しない場合は先行するINSERTが外部キー違反で失敗するため、UPDATEが0行を返すのは在庫切れの場合です。
今回のテーブルでは、stockのUPDATEが取得する行ロックはFOR NO KEY UPDATE相当なので、注文のINSERT後に実行しても、外部キー検査との間に今回の循環待ちは生じません。
同じ行を更新する別の購入は待機します。READ COMMITTEDでは、先行する購入のコミット後に更新後の行でstock > 0が再評価されるため、最後の1個を別々の購入が両方とも減らすことを防げます。
なお、RETURNING stockが返すのは減算後の在庫です。初期在庫10から2件の購入が成功すれば、先に更新した側が9、後に更新した側が8を受け取ります。
「更新前の在庫を使う別の判断」が必要なら、対処法1や2のようにロックしてから確認する方法が適しています。
公式ドキュメント：同時更新時の条件の再評価
比較結果と実装時の注意
元の順番と3つの対処法の計4パターンを、記事の手順の順番でそれぞれ3回ずつ実行した結果です。
| 1件の購入の中身 | デッドロック |
|---|---|
INSERT → FOR UPDATE → UPDATE |
3回中3回 |
INSERT → FOR NO KEY UPDATE → UPDATE |
3回中0回 |
FOR UPDATE → INSERT → UPDATE |
3回中0回 |
| INSERT → UPDATE（明示的なロックなし） | 3回中0回 |
ここでの結果は、今回のテーブルと実行順に対するものです。FOR NO KEY UPDATEや条件付きUPDATEを使えば、あらゆるデッドロックを防げるわけではありません。 複数の行や別のテーブルも更新する場合には、それらを含めたロック順を考える必要があります。
実装時には、次の点も確認しておきます。
- ORMが
FOR NO KEY UPDATEを指定できない場合は、生SQLを実行するAPIなどで対応する。 - SQL文字列の確認に加え、実際のPostgreSQLに2つの接続を作り、今回の実行順で処理が完了することと、最終在庫・注文数を確認する。
- 在庫が1の場合もテストし、購入失敗時に注文が残らないことを確認する。
- デッドロックエラーへの再試行を設ける場合は、回数を制限し、トランザクション全体を最初から実行する。
デッドロックのSQLSTATEは40P01です。再試行では失敗したSQLだけをやり直すのではなく、それまでの読み取りや判断も含めてトランザクション全体を実行し直します。
公式ドキュメント：直列化失敗の処理（トランザクションの再試行）
検証が終わったら、psqlを\qで終了し、コンテナを停止します。
docker stop lockdemo
おわりに
今回はPostgreSQLで在庫数の変更を行う際に注意するべき点についてまとめました。
SQL単体の動作に加えて、トランザクション全体で「どの行に、どのロックを、どの順番で取得するか」を確認することが大切だと分かりました。
対処法はいくつかあり、それぞれにメリット・デメリットがあるので、要件に応じて適切に選択する必要がある点が学びになりました。
参考サイト
13.3. 明示的ロック
データベースの行ロックをちゃんと理解する（PostgreSQL, MySQL）
【PostgreSQL】SELECT FOR UPDATEで行をロックする