What Isolation Levels Stop and What They Do Not

Goal

You will create yourself, on a real PostgreSQL, the fourth column of the table you saw in the reading: write skew, which no isolation level catches. And you will use in turn the three tools that prevent it: row locks, the serializable level, and skipping for work queues.

Why it matters

When you meet a concurrency bug, the prescription "raise the isolation level" comes out first. This lab shows in numbers when that prescription works and when it does not. The 100 won lost in step 2 turns into an error in step 4, and in step 5 the rule is broken without even an error.

What to watch especially is that the same 900 appears twice. Once because an update disappeared, and once because an update was rejected. You cannot tell them apart from the value alone, and you can tell which it was only by whether you received an error. In production, this difference separates accidents that get reported from accidents you never learn about.

Environment

PostgreSQL 16 runs inside this Pod. You connect like this.

export PGPASSWORD=lab
psql -X -q -v ON_ERROR_STOP=1 -h 127.0.0.1 -U lab -d labdb

This is a lab in which two sessions must overlap. There is only one terminal, so you start one side in the background. If you put SELECT pg_sleep(3); inside the transaction, that session waits with the transaction open.

psql ... -f /root/txn/lost.sql > /root/txn/lost_a.out 2>&1 &
sleep 1
psql ... -f /root/txn/lost.sql > /root/txn/lost_b.out 2>&1
wait

Put all outputs under /root/txn/. Do not touch the seed tables (customers and so on).

Steps

1. Using /root/txn/schema.sql, create and load three tables: purse, oncall, and tasks.
2. Reproduce a lost update on account 1 with /root/txn/lost.sql and save it to /root/txn/02-lost.txt.
3. Protect account 2 with a row lock using /root/txn/forupdate.sql and save it to /root/txn/03-forupdate.txt.
4. Get a serialization error on account 3 with /root/txn/repeatable.sql and save it to /root/txn/04-repeatable.txt.
5. Using /root/txn/skew.sql, break the on-call rule of team rr and save it to /root/txn/05-skew.txt.
6. Using /root/txn/serializable.sql, keep the rule of team ssi and save it to /root/txn/06-serializable.txt.
7. Have two workers split the queue with /root/txn/claim.sql and save it to /root/txn/07-queue.txt.
8. Summarize what you learned and save it to /root/txn/08-notes.md.

Notes

• You can list tables with \dt and see a table's column structure with \d purse.
• SELECT balance AS b FROM purse WHERE id = 1 \gset puts the query result into the psql variable b. You use it later as :b.
• To see the SQLSTATE along with errors, put \set VERBOSITY verbose at the top of the file.
• At the start of each step, reset the target account or team to its initial value. That way you get the same result however many times you redo it.
• Common mistake 1: writing UPDATE purse SET balance = balance - 100. Then the database reads the latest value and calculates, so the loss is not reproduced. It must be :b - 100, which uses the value you read as it is.
• Common mistake 2: leaving the start interval between the two sessions longer than the wait time. If the second reads after the first session has already committed, nothing happens.

Create three tables for the lab

Create and load /root/txn/schema.sql. In purse(id, owner, balance), put three accounts (id 1, 2, 3) with a balance of 1000; in oncall(id, team, name, on_duty), put two people of team rr (id 1, 2) and two of team ssi (id 3, 4), all on duty; and in tasks(id, state, worker), put six tasks (id 1–6) in the queued state.

Connect with psql -h 127.0.0.1 -U lab -d labdb, and the password is lab. If you run export PGPASSWORD=lab, it does not ask every time.

Start with DROP TABLE IF EXISTS so that you end up in the same state however many times you load it. The later steps keep resetting and reusing these tables, so idempotency matters.

You can insert the six tasks at once with INSERT ... SELECT g, 'queued', null FROM generate_series(1, 6) g.

Reproduce a lost update

Create /root/txn/lost.sql and run a transaction that subtracts 100 from account 1 in two overlapping sessions. Read the balance first, wait 3 seconds, and write the absolute value obtained by subtracting 100 from the value you read. Leave the result in /root/txn/02-lost.txt as three lines, before, after, and expected.

In psql, put the query result into a variable with SELECT balance AS b FROM purse WHERE id = 1 \gset and use it later as :b. SELECT pg_sleep(3); makes the session wait 3 seconds with the transaction open.

If you start one session in the background and run the second session one second later, the two sessions overlap. Both read the pre-commit 1000, so both write 900. You withdrew twice, but the balance decreased only once.

Before running, reset account 1 to 1000. That way you get the same result however many times you redo it.

Block it with SELECT ... FOR UPDATE

Create /root/txn/forupdate.sql. It has the same flow as step 2, but locks the row with FOR UPDATE when reading the balance. Run two overlapping sessions on account 2 and leave the result in /root/txn/03-forupdate.txt as three lines, before, after, and expected.

If you read with SELECT balance AS b FROM purse WHERE id = 2 FOR UPDATE \gset, that row is locked. The second session waits at this query itself until the first session commits, and after waking up, it reads the updated value.

This is why it is called pessimistic locking. It anticipates in advance that a conflict will occur and makes them queue. In exchange, waiting time arises, and if you lock several rows in different orders, a deadlock occurs.

REPEATABLE READ blocks it with an error

Create /root/txn/repeatable.sql. Start the same flow as step 2 with BEGIN ISOLATION LEVEL REPEATABLE READ; and run two overlapping sessions on account 3. Leave the SQLSTATE received by the session that tried to write late and the final balance in /root/txn/04-repeatable.txt as three lines, sqlstate, after, and expected.

By default, psql does not show the SQLSTATE in error messages. If you put \set VERBOSITY verbose at the top of the file, the code appears along with it, as in ERROR: 40001: ....

Here the balance becomes 900. It is the same number as in step 2, but the meaning is the exact opposite. In step 2 it is a 900 in which one withdrawal silently disappeared, and in step 4 it is a 900 in which one withdrawal was rejected. The rejected side can simply be retried, but the side that disappeared does not even have a chance to be retried.

What even REPEATABLE READ does not block

Create /root/txn/skew.sql. It is a transaction that checks whether the number of people on duty in team rr exceeds two and then removes itself from duty, and the isolation level is REPEATABLE READ. Have two sessions run overlapping with id 1 and id 2 respectively, and leave the number of remaining people on duty in /root/txn/05-skew.txt as two lines, isolation and on_duty.

To chain the condition check and the update together inside psql, put the truth value into a variable with SELECT (count(*) > 1)::text AS ok ... \gset and wrap the rest in \if :ok and \endif. Pass your own number like psql -v me=1 and use it in SQL as :me.

The two sessions modify different rows. The writes do not overlap, so snapshot isolation has no basis to detect a conflict. Both succeed and the number of people on duty becomes 0. This is the write skew that arises when you modify a different row based on the result of a read.

Catch write skew with SERIALIZABLE

Create /root/txn/serializable.sql. Start the same flow as step 5 with BEGIN ISOLATION LEVEL SERIALIZABLE; and the target is team ssi (id 3 and 4). Leave the SQLSTATE received by one side and the number of remaining people on duty in /root/txn/06-serializable.txt as two lines, sqlstate and on_duty.

SERIALIZABLE does not look only at write conflicts but tracks dependencies between reads and writes as well. It notices that each of the two transactions modified the set that the other had read, and rolls one of them back.

You can copy the step 5 file and change only the isolation level, the team name, and your own number. To see the SQLSTATE, you need \set VERBOSITY verbose here too.

The price is an error. If you decide to use this level, the application must have retry.

Pick up work split with SKIP LOCKED

Create /root/txn/claim.sql. It is a transaction that picks three tasks in the queued state in id order, locks them with FOR UPDATE SKIP LOCKED, changes them to running, and writes its own name in worker. Run two overlapping sessions, worker-a and worker-b, so that they take all six, and leave the result in /root/txn/07-queue.txt as three lines, worker_a, worker_b, and unclaimed.

A CTE is convenient for putting the rows to pick and the rows to modify in one statement.

WITH picked AS (
  SELECT id FROM tasks WHERE state = 'queued' ORDER BY id
  FOR UPDATE SKIP LOCKED LIMIT 3
)
UPDATE tasks t SET state = 'running', worker = :'me'
FROM picked WHERE t.id = picked.id;

:'me' inserts the psql variable wrapped as a string literal. Without SKIP LOCKED, the second worker queues in front of the rows the first worker locked, and after waking up it sees rows that have already been processed again.

Summarize what blocked what

Write at least four lines in /root/txn/08-notes.md: why the balances of step 2 and step 4 are the same 900 but mean different things, why REPEATABLE READ could not block it in step 5, and in which situations you would choose locks and isolation levels respectively.

The text must contain 쓰기 편향 (write skew), FOR UPDATE, and SKIP LOCKED.

Summarize step 5 especially well. Most concurrency accidents in practice come from that fourth column that is not in the table.