Sign in Get started

Transactions, Locking & Concurrency in Code · Locking Strategies

Pessimistic Locking with SELECT … FOR UPDATE

Lock the rows you are about to change: seat booking, NOWAIT for instant feedback, and how lock scope differs between Postgres and MySQL.

Scenario

Example app: a cinema seat map. A seat goes through available → held (10 minutes) → sold. Holding a seat involves several checks — the customer's existing holds, a per-order limit of 8 seats, and whether the seat is still free — so it can't be one UPDATE.

What you see in production

Two customers occasionally get the same seat: both see "Seat F7 is yours", both pay, and one is turned away at the door.

The naive code

Read the seat, run the business checks, then update it — with nothing stopping a second request in between:

with transaction.atomic():
    seat = Seat.objects.get(show_id=show_id, label="F7")
    if seat.status != "available":
        raise SeatTaken()
    if Seat.objects.filter(held_by=customer, show_id=show_id).count() >= 8:
        raise TooManySeats()
    seat.status, seat.held_by, seat.hold_expires = "held", customer, now() + timedelta(minutes=10)
    seat.save()

Reproduce it

A small lock manager shows how FOR UPDATE makes the second request wait, then re-read the seat after the first commits — and how NOWAIT fails immediately instead:

FOR UPDATE waits; NOWAIT fails fastTry it yourself
seat = {"F7": "available"}
locks = {}                                   # row -> transaction holding the lock

def lock_row(tx, row, nowait=False):
    holder = locks.get(row)
    if holder and holder != tx:
        if nowait:
            raise RuntimeError(f"{tx}: could not obtain lock on row {row} (SQLSTATE 55P03)")
        return False                          # must wait
    locks[row] = tx
    return True

def hold(tx, nowait=False):
    while not lock_row(tx, "F7", nowait):
        yield f"{tx}: waiting for lock on F7…"
    status = seat["F7"]                       # read AFTER getting the lock: current value
    if status != "available":
        yield f"{tx}: F7 is {status} -> tell customer to pick another seat"
    else:
        seat["F7"] = f"held by {tx}"
        yield f"{tx}: F7 held"
    locks.pop("F7", None)                     # COMMIT releases the lock
    yield f"{tx}: COMMIT"

print("-- FOR UPDATE --")
a, b = hold("A"), hold("B")
for step in (a, b, a, b, b):
    print(next(step))

print("-- FOR UPDATE NOWAIT --")
seat["F7"] = "available"
locks["F7"] = "C"                            # C is in the middle of holding it
try:
    next(hold("D", nowait=True))
except RuntimeError as e:
    print(e, "-> show 'someone is booking this seat right now'")

Output

-- FOR UPDATE --
A: F7 held
B: waiting for lock on F7…
A: COMMIT
B: F7 is held by A -> tell customer to pick another seat
B: COMMIT
-- FOR UPDATE NOWAIT --
D: could not obtain lock on row F7 (SQLSTATE 55P03) -> show 'someone is booking this seat right now'

Root cause

Without a lock, the read and the later write are separate moments; anyone can change the row in between. SELECT … FOR UPDATE takes a row lock at read time. Other transactions that try to lock or update the same row wait until commit, and under Read Committed they then see the new version of the row.

The fix

BEGIN;
SELECT id, status FROM seats WHERE show_id = $1 AND label = $2 FOR UPDATE;      -- wait for the lock
-- or: … FOR UPDATE NOWAIT     → error immediately if someone else holds it
-- MySQL 8: … FOR UPDATE NOWAIT is supported too
UPDATE seats SET status = 'held', held_by = $3, hold_expires = now() + interval '10 minutes' WHERE id = $4;
COMMIT;

Trade-offs

  • Lock scope differs. Postgres locks exactly the rows returned. MySQL InnoDB locks the index records it scans — FOR UPDATE with a WHERE on an unindexed column can lock (and block) far more rows than it returns, and Repeatable Read adds gap locks. Index the columns you lock by.
  • Locks serialize work on hot rows: fine for one seat, bad for one shared counter (see hot rows).
  • Always lock rows in a consistent order when locking several, or you invite deadlocks.
  • Use FOR NO KEY UPDATE (Postgres) when you won't change the key — it doesn't block inserts of child rows referencing it.

Detect it in production

-- Postgres: who is blocking whom right now
SELECT blocked.pid AS blocked_pid, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));
  • MySQL 8: SELECT * FROM performance_schema.data_lock_waits; and sys.innodb_lock_waits.
  • Track lock-wait time per endpoint; long waits mean contention or a slow holder.

Key points

  • FOR UPDATE locks rows at read time; concurrent lockers wait and then see current data.
  • NOWAIT turns waiting into an immediate error you can show as "busy".
  • In MySQL, lock scope follows the index used — index what you lock by.
  • Lock in a consistent order; keep locked transactions short.

Practice questions

Two customers hold the same seat. How does SELECT … FOR UPDATE fix this?

Answer

The first transaction locks the seat row when reading it. The second transaction's FOR UPDATE waits until the first commits, then reads the updated row (now 'held') and refuses — the check and the update can no longer interleave.

When would you use FOR UPDATE NOWAIT instead of waiting?

Answer

When an immediate answer is better than queueing — for example a seat map can tell the user 'someone is booking this seat' instantly. The statement fails with a lock-not-available error instead of blocking.

Why can SELECT … FOR UPDATE in MySQL block many more rows than it returns?

Answer

InnoDB locks the index records it scans (plus gaps under Repeatable Read). Without a suitable index the scan covers many rows, all of which get locked. Indexing the filtered columns limits the lock to the matching rows.

Open in the app to mark lessons complete, take notes and track your progress.