The Bug That Never Happens on Your Own

Summary

Concurrency bugs happen not because the code is wrong, but because two people touch the same row at the same moment. That is why they never reproduce when you test alone.

Why it matters — the incident where stock goes negative

select qty from stock where id = 1;      -- 1 이 남았다
-- (여기서 다른 사람도 똑같이 1 을 읽는다)
update stock set qty = qty - 1 where id = 1;

Both read "1 left" and both deduct. Stock becomes -1.

The problem is that someone else can step in between reading, deciding, and writing. This is called a lost update.

There are three ways to fix it.

1. select ... for update — lock the row when you read it. Others wait
2. Finish in a single statement — update stock set qty = qty - 1 where id = 1 and qty > 0
3. Optimistic locking — add a version column, use where version = %s, and retry if 0 rows are updated

If option 2 is possible, it is the cheapest. Use option 1 only when you must read the value and decide.

Locks are held until the transaction ends

A row locked with for update stays locked until commit or rollback. That is why you must not call an external API inside a transaction. If that API takes 3 seconds, the row is locked for 3 seconds.

Keep transactions short. Never wait for a person or the network while holding a lock.

Deadlock — each waits for what the other holds

A: id=1 잠금 → id=2 요청
B: id=2 잠금 → id=1 요청

Both wait forever. After deadlock_timeout (1 second by default), PostgreSQL detects this cycle and kills one side.

ERROR:  deadlock detected
DETAIL:  Process 123 waits for ShareLock on transaction 773; blocked by process 122.
         Process 122 waits for ShareLock on transaction 774; blocked by process 123.

The error message tells you exactly who waited for what. Knowing how to read these two lines makes finding the cause much faster.

There is only one way to prevent it — make everyone lock in the same order. If you always lock in ascending id order, a cycle cannot form. It does not matter what the sort criterion is; what matters is that everyone uses the same one.

Also, deadlocks cannot be eliminated completely. So the application must be able to retry when it encounters 40P01.

Stop waiting forever

set lock_timeout = '3s';

Without this, a lock wait holds on to its connection, and when those connections use up the pool, the application stops, not the DB. The symptom shows up not as "the DB is slow" but as "the server is not responding".

This matters especially in migrations. alter table requires ACCESS EXCLUSIVE, and if one long query is ahead of it, every query behind it lines up in the queue. If you deploy without lock_timeout, the service stops at that moment.

Who is blocking whom

select pid, pg_blocking_pids(pid), query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

One line shows the blocking relationships. This is faster than joining pg_locks directly.

When building a queue — SKIP LOCKED

If several workers must take jobs from the same table, using only for update makes the workers queue up. This is because the second worker waits for the row the first worker holds.

select id from jobs where state = 'ready'
order by id limit 10
for update skip locked;

skip locked skips locked rows. The second worker does not wait and picks up the next ones. It is the key line for building a job queue on a DB without dedicated queue middleware.

When you need to lock something that has no row

A rule such as "only one settlement per user at a time" has no row to lock. In this case, use an advisory lock.

select pg_try_advisory_lock(12345);   -- 얻으면 true, 아니면 즉시 false

Two cautions. The key space is global — if another feature uses the same number, they collide. And it is tied to the session — with a connection pool, you must release it before returning the connection. Otherwise the next user receives a connection that is still locked.

Wrap-up

Concurrency problems are hard because they are hard to reproduce. That is why every lab in this course actually starts two sessions and makes them collide. Seeing the waiting and the deadlock on screen yourself is most of the understanding.

In the field

This problem almost never reproduces in a development environment. Clicking through alone always works, and load tests do not overlap if they buy different products. In reality it blows up at the moment when everyone goes after one and the same row, as in limited-quantity sales or coupon issuance.

That is why it takes a long time, in a post-incident investigation, to suspect locking. All the database metrics are normal, and the application log contains only a long run of "stock deduction succeeded". The only clue left is one contradiction in the resulting data — stock is negative, or the issued quantity exceeded the limit.

Before going to production, check two things. Make a list of the paths that touch the same row concurrently, and for each path decide whether it can be finished in a single statement or needs a lock. And set lock_timeout as the session default, so that even if something goes wrong, the connection pool does not collapse with it.