二つのセッションでぶつけてみる

目標

1人で動かすと正しいのに、2人が同時にやると間違うコードを、自分で作って直します。並行性の問題は再現が難しいから難しいのです。そのため、このラボはすべてセッションを2つ実際に立ち上げてぶつけます。

環境

PostgreSQL 16がすでに起動しています(5432、DB labdb、ユーザーlab)。

export PATH=/usr/lib/postgresql/16/bin:$PATH
psql -h 127.0.0.1 -U lab -d labdb

2つのセッションの作り方

片方をバックグラウンドで起動し、その間にもう片方を操作します。

psql -h 127.0.0.1 -U lab -d labdb   -c "begin; select * from stock where id=1 for update; select pg_sleep(15);" &
sleep 1
# 여기서 두 번째 세션 작업
wait

準備

create table stock(id int primary key, qty int not null);
insert into stock values (1,10),(2,10);

ステップ

ロック待ちを作る → 01-block.txt
lock_timeout → 02-timeout.txt
わざとデッドロックを起こす → 03-deadlock.txt
順序をそろえてなくす → 04-order.txt
同時に20回減算しても、マイナスにならないようにする → 05-stock.txt
skip lockedでワーカーキュー → 06-queue.txt
アドバイザリーロック → 07-advisory.txt
まとめ → 08-notes.md

参考

ステップ3は失敗が正解です。deadlock detectedのDETAIL 2行が、誰が何を待ったのかを正確に教えてくれます。その2行の読み方が、このラボでいちばん長く使える技術です。

ロック待ちを作る

セッション2つで、同じ行をfor updateしてください。2つ目が待っている間に、誰が誰を止めているのかを調べて01-block.txtに残します。

まずcreate table stock(id int primary key, qty int not null); insert into stock values (1,10),(2,10);。片方はバックグラウンドで: psql -h 127.0.0.1 -U lab -d labdb -c "begin; select * from stock where id=1 for update; select pg_sleep(15);" &。そのあと別のセッションでselect pid, pg_blocking_pids(pid) from pg_stat_activity where cardinality(pg_blocking_pids(pid))>0;を保存してください。

無限に待たないようにする

lock_timeoutを設定し、ロックされた行を要求して、キャンセルされることを確認してください。そのエラーの全文を02-timeout.txtに残します。

set lock_timeout='1s'; begin; select * from stock where id=1 for update;でcanceling statement due to lock timeoutが出ます。これをかけないと、待ちがコネクションを握り、そのコネクションがプールを食い尽くすと、DBではなくアプリケーションが止まります。

デッドロックをわざと作る

2つのトランザクションが互いに逆の順序でid 1・2を更新するようにして、デッドロックを起こしてください。deadlock detectedを含むエラーの全文を03-deadlock.txtに残します。

Aは1 → (少し休んで) → 2、Bは2 → (少し休んで) → 1。select pg_sleep(2)を間に入れて重ねてください。2つをバックグラウンドで同時に起動してwaitします。DETAILの2行が、誰が何を待ったのかを正確に教えてくれます。 その2行の読み方が、このステップの目的です。

順序をそろえてなくす

同じ2つの作業が、どちらもid昇順でロックするように変えて、デッドロックなしで終わるようにしてください。2回以上実行しても失敗がない必要があります。

並べ替えの基準は何でもよく、全員が同じ基準を使うことだけが重要です。結果を04-order.txtに残してください。デッドロックは完全にはなくせないので、アプリケーションは40P01のリトライを備える必要がありますが、順序をそろえればほとんどは消えます。

在庫がマイナスにならないように

stockのid=1を10に戻したあと、同時に20回の減算を試みてください。在庫10に対して20回なら、最終的な数量はちょうど0でなければなりません。結果を05-stock.txtに残します。

2つのうち1つで十分です。for updateで読んで判断するか、1つの文で終わらせる(update stock set qty=qty-1 where id=1 and qty>0)かです。可能なら後者が安上がりです。同時実行はfor i in $(seq 20); do (psql ... &) ; done; wait。

マイナスにならないだけでは合格しません。 素朴にselectで読んでその値を引くと、マイナスにはなりませんが、結果が9あたりで止まります。更新が消えたのです。それがこのステップで捕まえようとしているバグです。

ワーカーが列に並ばないように

jobsテーブルを作り、ワーカー2つが互いに違う仕事を取っていくようにしてください。06-queue.txtにworker1=...とworker2=...の2行で、それぞれが取ったidを残します。

for updateだけを使うと、2つ目のワーカーが待ちます。for update skip lockedは、ロックされた行を飛ばします。 最初のワーカーが1、2を握っていれば、2つ目は待たずに3、4を取ります。ファイルはこの形式で。それぞれ1行ずつ、使ったSQLも一緒に残してください:

worker1=1,2
worker2=3,4

行がないものにロックをかける

pg_try_advisory_lockで、同じキーを2つのセッションが同時には取れないことを示してください。07-advisory.txtにsession1=とsession2=の2行で結果を残します。

1つのセッションがselect pg_try_advisory_lock(42)で取得したあと、別のセッションで同じキーを試すとfが出ます(同じセッションでもう一度やると再入なのでtになります。それでは証明になりません)。ファイルはこの形式で:

session1=t
session2=f

コネクションプールでは、返却の前に必ずpg_advisory_unlockしてください。

3つをまとめる

08-notes.mdに3行以上。デッドロックをなくす規則を1つ、lock_timeoutがないとどこが先に止まるか、skip lockedが変えるもの。

本文に순서・커넥션・skip lockedが含まれている必要があります(韓国語の2語は、順に「順序」と「コネクション」を意味します)。2つ目が、実務でいちばんよく原因を取り違える部分です。症状は「DBが遅い」ではなく「サーバーが応答しない」です。