All posts

Stock that doesn't add up, double bookings, stuck transfers

October 25, 2026 · 10 min read

Concurrency bugs never show up in tests that run one request at a time. This post sets off five of them on an online store's sale day and checks where to guard against each.

The setup: sale day, and orders pile onto a popular item

Concurrency bugs happen only when two requests touch the same data at almost the same moment. They slip through development and first appear on a busy day.

The examples use PostgreSQL, with notes wherever MySQL behaves differently.

Stock that does not add up (lost update)

An item has 10 in stock, and two orders arrive at once. Each reads the stock, subtracts 1 in the app, and writes it back. Both read 10 and write 9, so two units sold but the stock says 9. No error appears anywhere.

A higher isolation level may not help, depending on the database. PostgreSQL's Repeatable Read makes the later writer fail, but under MySQL (InnoDB) Repeatable Read the overwrite goes through.

If the update is a plain addition, move the arithmetic into the database. The second order waits for the first to commit, then subtracts from the latest value, and the stock correctly becomes 8.

UPDATE items SET stock = stock - 1
 WHERE id = 42 AND stock > 0;
-- One row updated means reserved. Zero rows means out of stock: refuse

Updates that cannot move into the database, such as editing a description, write on the condition of the version they read (optimistic locking). Zero rows means someone wrote first.

UPDATE items
   SET description = $1, version = version + 1
 WHERE id = 42 AND version = $2;   -- $2 is the version you read

Overlapping bookings (write skew)

Booking a room means "insert if there are no bookings for this room in this slot." When two people run it at once, both see zero and insert different rows, double-booking the room.

Snapshot isolation (PostgreSQL's Repeatable Read) does not prevent this. It detects concurrent writes to the same row, and these two wrote different rows.

There are three defenses.

1. Make it a constraint. This leaves no gaps: whatever path writes, the database refuses.
2. Lock the starting row. But booking rows that do not exist yet cannot be locked. Lock the parent room row with SELECT ... FOR UPDATE instead.
3. Use Serializable. PostgreSQL fails one side with a serialization error (40001). The app needs retries.

-- Extension that lets GiST handle integer equality
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(starts_at, ends_at) WITH &&
  );
-- Of two overlapping bookings, one fails with a constraint violation (23P01)

Stuck transfers (deadlock)

A transfer from account 1 to 2 runs at the same moment as one from 2 to 1. One locks account 1 first, the other account 2, and each waits for the other's row. The database aborts one, but PostgreSQL waits deadlock_timeout (1 second by default) before checking for a cycle, so each deadlock costs over a second.

If everyone locks in the same order, such as "lower account ID first," later transactions simply wait for earlier ones, and no cycle forms.

BEGIN;
-- Lock in ascending ID order, whichever way the money moves
SELECT id, balance FROM accounts
 WHERE id IN (1, 2)
 ORDER BY id
   FOR UPDATE;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

Deadlocks still never reach zero; the order can flip where the app cannot see it, as in foreign key checks. Handle deadlocks (40P01 in PostgreSQL, 1213 in MySQL) together with write skew's serialization error (40001) in one place that restarts the whole transaction from the beginning. Retrying a single statement carries on with stale values read earlier.

func inTx(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
	for attempt := 0; ; attempt++ {
		err := runTx(ctx, db, fn) // BEGIN through COMMIT; roll back on failure
		// Restart up to 3 times, only for 40001 and 40P01
		if !retryable(err) || attempt == 2 {
			return err
		}
		time.Sleep(backoff(attempt))
	}
}

Only the popular item is slow (long transactions)

The order code updated stock inside a transaction, called the payment API, and only then committed. A row lock lasts until commit, so the time spent waiting on the payment API is time spent holding the lock.

Updates to the same row proceed one at a time. The most a row can pass is "1 ÷ lock hold time," and adding servers does not change it.

Orders that cannot pass queue for the lock, each holding a database connection. When the pool runs out, unrelated screens stop too. Open transactions also block PostgreSQL's VACUUM and MySQL's undo log cleanup.

The fix is to move external API calls outside the transaction. Write the order as "awaiting payment" in a short transaction, call the payment API, then record the result in another short transaction. Pass the order ID to the payment as the idempotency key.

Adding one column stops every order (DDL lock queue)

During the sale, someone ran ALTER TABLE orders ADD COLUMN note text. It should have been instant, yet every read and write on orders stopped for the next 5 minutes.

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts even with ordinary SELECTs. Queries arriving behind a waiting ALTER cannot pass it. However fast the ALTER is, the table stops for as long as the longest query ahead of it.

-- First, look for transactions that have been open a long time
SELECT pid, now() - xact_start AS age, left(query, 60)
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY age DESC
 LIMIT 5;

-- Give up after 3 seconds, and retry later
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

A long transaction holds up migrations as well as row updates, and that migration then stops every query behind it. Keeping transactions short limits the damage of the other bugs too.

The five bugs and where to guard

When asked, "How do you prevent overselling stock?"

Name the failure first: "Reading and then writing back loses an update when two requests arrive together." Then answer that you move the arithmetic into the database with UPDATE ... SET stock = stock - 1 WHERE stock > 0, and treat zero rows updated as out of stock.

Go on to why payment moves outside the transaction (lock hold time caps the popular item's throughput) and why several items are locked in ID order, and one answer becomes a conversation about the whole design. Do not stop at "we use Serializable"; add what you pay for it (you need retries).