Collide Two Sessions and See

Goal

You build and fix code that is correct when run alone but wrong when two run at the same time. Concurrency problems are hard because they are hard to reproduce. That is why this lab actually starts two sessions and makes them collide.

Environment

PostgreSQL 16 is already running (5432, DB labdb, user lab).

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

How to create two sessions

Start one side in the background and work on the other side in the meantime.

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

Preparation

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

Steps

1. Create a lock wait → 01-block.txt
2. lock_timeout → 02-timeout.txt
3. Cause a deadlock on purpose → 03-deadlock.txt
4. Eliminate it by aligning the order → 04-order.txt
5. Keep stock non-negative even with 20 concurrent deductions → 05-stock.txt
6. A worker queue with skip locked → 06-queue.txt
7. Advisory locks → 07-advisory.txt
8. Wrap up → 08-notes.md

Notes

In step 3, failure is the correct answer. The two DETAIL lines of deadlock detected tell you exactly who waited for what — learning to read those two lines is the skill from this lab that you will use for the longest time.

Create a lock wait

Run for update on the same row from two sessions. While the second one waits, find who is blocking whom and save it to 01-block.txt.

First run create table stock(id int primary key, qty int not null); insert into stock values (1,10),(2,10);. Run one side in the background: psql -h 127.0.0.1 -U lab -d labdb -c "begin; select * from stock where id=1 for update; select pg_sleep(15);" &. Then, from another session, save the output of select pid, pg_blocking_pids(pid) from pg_stat_activity where cardinality(pg_blocking_pids(pid))>0;.

Stop waiting forever

Set lock_timeout, request a locked row, and confirm that it gets canceled. Save the full error text to 02-timeout.txt.

set lock_timeout='1s'; begin; select * from stock where id=1 for update; — you get canceling statement due to lock timeout. Without it, the wait holds a connection, and when those connections use up the pool, the application stops, not the DB.

Cause a deadlock on purpose

Have two transactions update ids 1 and 2 in opposite orders to cause a deadlock. Save the full error text containing deadlock detected to 03-deadlock.txt.

A goes 1 → (pause briefly) → 2, and B goes 2 → (pause briefly) → 1. Put select pg_sleep(2) in between so they overlap. Start both in the background at the same time and wait. The two DETAIL lines tell you exactly who waited for what — learning to read those two lines is the purpose of this step.

Eliminate it by aligning the order

Change the same two jobs so that both lock in ascending id order and finish without a deadlock. There must be no failures even when you run them more than twice.

It does not matter what the sort criterion is; what matters is that everyone uses the same one. Save the result to 04-order.txt. Deadlocks cannot be eliminated completely, so the application must have a 40P01 retry, but aligning the order removes most of them.

Keep stock from going negative

Reset id=1 in stock to 10, then try to deduct 20 times concurrently. With a stock of 10 and 20 attempts, the final quantity must be exactly 0. Save the result to 05-stock.txt.

Either of two approaches works — read with for update and then decide, or finish in a single statement (update stock set qty=qty-1 where id=1 and qty>0). If possible, the latter is cheaper. For concurrent execution, use for i in $(seq 20); do (psql ... &) ; done; wait.

Not going negative is not enough to pass. If you naively read with select and subtract that value, the stock never goes negative but the result stops around 9 — updates have been lost. That is the bug this step is meant to catch.

Keep workers from queuing up

Create a jobs table and make two workers pick up different jobs. In 06-queue.txt, record the ids each one picked in two lines, worker1=... and worker2=....

With only for update, the second worker waits. for update skip locked skips locked rows. If the first worker holds 1 and 2, the second does not wait and picks up 3 and 4. Use this format for the file — one line each, and also save the SQL you used:

worker1=1,2
worker2=3,4

Lock something that has no row

Use pg_try_advisory_lock to show that two sessions cannot hold the same key at the same time. Record the result in 07-advisory.txt in two lines, session1= and session2=.

After one session acquires it with select pg_try_advisory_lock(42), trying the same key from another session returns f (trying again in the same session is re-entrant and returns t — that proves nothing). Use this format for the file:

session1=t
session2=f

With a connection pool, always call pg_advisory_unlock before returning the connection.

Sum up the three points

In 08-notes.md, write at least three lines: one rule that eliminates deadlocks, what stops first when there is no lock_timeout, and what skip locked changes.

The text must include the Korean words 순서 (order), 커넥션 (connection), and skip locked. The second point is the one most often misdiagnosed in practice — the symptom is not "the DB is slow" but "the server is not responding".