Lab: make a race happen on purpose

Before you start

You need a throwaway database and pgbench, which ships with PostgreSQL. Each test is a small script file that pgbench runs over and over from several connections at once. Our results are from PostgreSQL 18.3 on an Apple M1 Pro laptop; races depend on timing, so your counts will differ, but whether they are zero should not.

Every pgbench command below uses the same shape:

pgbench -n -d <yourdb> -c <clients> -j <clients> -t <transactions per client> -f <script>

-n skips pgbench’s own setup, and -c 1 -t 4000 and -c 8 -t 500 both run 4,000 operations in total.

1. The lost update

A bank account with 100,000, and a withdrawal of 1 written the way it often is: read the balance into the application, subtract, write it back.

CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);
INSERT INTO accounts VALUES (1, 100000);

Save this as withdraw_racy.sql. \gset stores the query’s result in the variable b, as application code would:

SELECT balance AS b FROM accounts WHERE id = 1 \gset
UPDATE accounts SET balance = :b - 1 WHERE id = 1;

Run 4,000 withdrawals one at a time, check the balance, reset it, then run 4,000 from 8 clients:

pgbench -n -d lab -c 1 -j 1 -t 4000 -f withdraw_racy.sql
psql -d lab -c "SELECT balance FROM accounts"          # expect 96000
psql -d lab -c "UPDATE accounts SET balance = 100000"
pgbench -n -d lab -c 8 -j 8 -t 500 -f withdraw_racy.sql
psql -d lab -c "SELECT balance FROM accounts"

With one client, the code is correct. With eight, two clients read the same balance, both subtract one, and both write the same result: one withdrawal disappears. Across our three runs, 3,377 to 3,413 of 4,000 withdrawals were lost, with no error anywhere. A single-threaded test of this code passes every time.

2. Three fixes, tested the same way

Write each as its own script, reset the balance before each run, and run it with 8 clients.

Do the arithmetic in the database (withdraw_atomic.sql):

UPDATE accounts SET balance = balance - 1 WHERE id = 1;

Lock the row while you read it (withdraw_lock.sql):

BEGIN;
SELECT balance AS b FROM accounts WHERE id = 1 FOR UPDATE \gset
UPDATE accounts SET balance = :b - 1 WHERE id = 1;
COMMIT;

Let a stricter isolation level detect it (withdraw_rr.sql):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance AS b FROM accounts WHERE id = 1 \gset
UPDATE accounts SET balance = :b - 1 WHERE id = 1;
COMMIT;

Run the last one twice: once as before, once with --max-tries=0 --latency-limit=10000, which tells pgbench to retry failed transactions as an application should.

Read the third row carefully. Repeatable read did not lose any updates: the 679 withdrawals that committed are exactly the 679 subtracted. Every other withdrawal failed loudly with could not serialize access due to concurrent update. That is the point of the isolation level, and why it is only a fix when the application retries.

3. The duplicate signup

Signup code that checks whether an email exists before inserting it. Make a table with no unique constraint, as the code’s author thought the check made one unnecessary:

CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL);

Save as signup_racy.sql. Each run picks one of 200 email addresses at random:

\set n random(1, 200)
SELECT count(*) AS c FROM users WHERE email = 'u' || :n || '@example.com' \gset
\if :c = 0
INSERT INTO users (email) VALUES ('u' || :n || '@example.com');
\endif

Run 4,000 signups with 1 client and then, on an emptied table (TRUNCATE users;), with 8. Count duplicates:

SELECT count(*) AS total_rows, count(*) - count(DISTINCT email) AS duplicates FROM users;

Seven duplicate accounts out of 4,000 signups is easy to miss in testing and certain to happen in production. Most collisions happen early, while many emails are still new; on a real site every new email is new.

Now fix it in the database and test again with 8 clients:

DROP TABLE users;
CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL UNIQUE);

The second script, signup_upsert.sql, replaces the check with one statement:

\set n random(1, 200)
INSERT INTO users (email) VALUES ('u' || :n || '@example.com') ON CONFLICT (email) DO NOTHING;

With the constraint, duplicates became impossible: the race still happens, but the losing insert gets an error instead of a second row. ON CONFLICT turns that error into the “already exists” outcome the code wanted in the first place.

What you measured

• Code that was correct for 4,000 operations run one at a time lost about 85% of them under 8 concurrent clients.
• Doing the arithmetic in the UPDATE, or locking the row with FOR UPDATE, made it correct.
• Repeatable read prevented lost updates by failing most transactions; with retries, it was correct.
• A check before inserting created 7–8 duplicates; a UNIQUE constraint created none.

Clean up

DROP TABLE accounts, users;

Check yourself

1. Why did the racy withdrawal work perfectly with one client?

   Show answer

   With one client, no withdrawal ever reads the balance while another is between its read and its write, so every write is based on the latest value. The race needs overlap.

   I could explain this without looking

2. Repeatable read without retries left a balance of 99,321. Were any updates lost?

   Show answer

   No. 679 transactions committed and the balance went down by exactly 679. The other 3,321 failed with a serialization error. Nothing was silently lost, but the application must retry them.

   I could explain this without looking

3. After adding UNIQUE (email), the race between two signups still happens. What changes?

   Show answer

   The second insert of the same email fails with a unique violation instead of creating a duplicate row. With ON CONFLICT DO NOTHING, it is quietly skipped instead.

   I could explain this without looking