Jesse's Notes

Concurrency and Locks

5 min read

A lock is not the goal. The goal is an invariant — a rule about the data that must always hold, like “a seat is reserved at most once” or “at least one admin is on call”. A lock is just one mechanism for protecting that rule under concurrency, and often the wrong one. Before reaching for a lock, name the invariant; then ask whether a lock is even the right tool to defend it.

Locks are an implementation detail. The invariant is the architecture.

See invariants for the underlying concept — a lock is how you keep an invariant true while two writers race; a database constraint is how you keep it true on every write, including ones written by people who never read your code.

A lock is not a mutex

The dangerous mental model is treating a database row lock like an in-process mutex. A mutex protects a region of code for the lifetime of the process. A database lock lives exactly as long as its surrounding transaction — not one millisecond longer. Acquire a lock with no transaction around it and you release it in the same breath, getting zero protection. The mechanism that made the lock meaningful was the transaction boundary, not the lock keyword.

Five ways locking goes wrong

  1. Lost updates. Two transactions read the same stale value, each computes a new value from it, and both write. The second silently clobbers the first. This is the canonical race a lock (or optimistic versioning) exists to stop.
  2. Forgotten transaction boundary. The lock is acquired and dropped before the work that depended on it runs. Correctness silently disappears.
  3. Locking too much. A query that returns many rows serializes every worker touching any of them. You meant to guard one row and instead built a queue nobody asked for.
  4. Long transactions. Holding a lock across an external HTTP call ties lock duration to a system you don’t control, cascading contention through everyone waiting behind you.
  5. Deadlocks. Two transactions grab the same locks in opposite order and wait on each other forever. The fix is boring and reliable: impose a global ordering so everyone acquires locks in the same sequence.

Scope and duration are independent knobs

How many rows a lock covers (scope) and how long it is held (duration) are separate dials. The healthy combination is narrow scope held briefly. Useful controls:

  • SKIP LOCKED — step over rows another worker already holds instead of queuing behind them (the basis of most SQL-backed job queues).
  • NOWAIT — fail immediately rather than wait, when blocking is itself a bug.
  • lock_timeout — bound the wait so a stuck holder degrades gracefully instead of stalling everything.

Row locks can’t protect multi-row rules

A row lock protects only the rows it actually returned — not the rows that should have matched. So a rule like “no more than 100 reservations per event” is not safe with row locks: after you count, another transaction can insert a 101st row your lock never saw. This is write skew / phantom reads. Three real fixes:

  • Lock a single aggregate row that represents the whole set (e.g. the Event), so all writers serialize through one point.
  • Raise the isolation level to SERIALIZABLE, which turns the reads into shared locks and refuses to let the skew commit — at a throughput cost.
  • Push the rule into a database constraint, so it holds regardless of how the write arrives.

Isolation levels decide what races are even possible

The default in many databases (e.g. MySQL’s READ COMMITTED) permits write skew and phantoms. SERIALIZABLE forbids them but serializes more aggressively. Crucially, different engines implement this differently — PostgreSQL’s serializable snapshot isolation detects conflicts and aborts; MySQL leans on locking. “Set the isolation level” is not portable advice; you have to know your engine. See consistency models and CAP.

Prefer constraints to discipline

The most robust defense isn’t a cleverer lock — it’s moving the invariant out of application code into the database: unique indexes, CHECK constraints, foreign keys. A constraint is enforced on every write that exists and every write that doesn’t yet, by callers you’ll never meet. A lock only protects the code paths that remember to take it. When no physical row exists to lock (a purely logical resource), advisory locks (GET_LOCK, pg_advisory_lock) let you serialize on a named key — but they are voluntary, so they’re only as good as the discipline of everyone who must remember to take them.

If correctness relies on the caller behaving themselves, you don’t have a hard rule — you have a ticking time bomb.

This is the same lesson as constraints over conventions: a guarantee that has a bypass list isn’t a guarantee.

Observability: lock-wait time hides

Slow-query timers measure how long a query ran, not how long it spent waiting on a lock. The two are different, and the lock wait is the one that bites in production. Look at the engine’s own lock views (performance_schema.data_locks, information_schema.INNODB_TRX, pg_locks) to see real blocking.

My read

The reframing that stuck with me: the lock is plumbing; the invariant is the design. Coming from Java/.NET, “lock” pattern-matched to a mutex in my head, and that’s exactly the trap — a synchronized block protects a code region, a DB lock protects nothing without a transaction around it. The practical takeaway is to push as many invariants as possible down into constraints, where they hold for callers I can’t see (background jobs, rake tasks, the admin console, next year’s teammate), and treat application locks as the narrow, short-lived exception.

Further reading

Graph View

Backlinks