Two writers,
one truth. Locks and isolation levels keep it honest.

A race condition is when the result depends on the timing of operations that should have been independent. The classic is the lost update: two transactions read a balance of 100, each add 10, each write 110 — one update vanishes. Concurrency control exists to make these impossible.

Pessimistic locks come in shared (read) and exclusive (write) flavours; the danger is the deadlock — two transactions each holding a lock the other needs. Databases detect cycles and abort a victim, but you avoid them by acquiring locks in a consistent order. Optimistic concurrency avoids deadlocks entirely by never holding locks — at the price of a retry path you must actually write.

A deadlock is precisely a cycle in the wait-for graph, and consistent lock ordering makes the cycle impossible:

Whoever holds the lower-ordered lock always makes progress, so no one waits in a circle. The runnable version below detects the deadlock and shows the fix.

SQL isolation levels are a contract: each one promises to prevent certain anomalies, in exchange for less concurrency. Higher is safer and slower.

* In the ANSI standard. PostgreSQL's Repeatable Read (snapshot isolation) actually prevents phantoms but allows write skew; true Serializable closes that too.

Multi-Version Concurrency Control is how modern databases (PostgreSQL, MySQL InnoDB, Oracle) avoid the read-write blocking that naïve locking causes. Instead of overwriting a row, a write creates a new version tagged with the transaction that made it. Each transaction reads the version that was valid as of its start — a consistent snapshot.

The result: readers don't block writers, writers don't block readers. The cost is version bloat (the VACUUM problem in Postgres) and the subtle write-skew anomaly that only true serializable isolation removes.

Vendors built the ANSI levels on very different machinery, so two databases can both say "Repeatable Read" and disagree about whether a lost update can happen. Know your defaults:

Two consequences. Code that is correct on one database's default can be wrong on another's, so set the isolation level explicitly in the code that depends on it. And of these five, only CockroachDB defaults to Serializable: if you have never chosen a level, write skew is probably possible in your system today.

Write skew is the anomaly snapshot isolation lets through, and the textbook case is an on-call rota. The rule: at least one doctor stays on call for every shift. Alice and Bob are both on call for shift 1234, both feel ill, and both click "go off call" at the same moment. Each click runs this under PostgreSQL's Repeatable Read:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors
 WHERE shift_id = 1234 AND on_call;          -- both transactions see 2
-- app: 2 on call, so someone else still covers
UPDATE doctors SET on_call = false
 WHERE shift_id = 1234 AND name = 'alice';   -- Bob's transaction updates 'bob'
COMMIT;                                       -- both commit: nobody is on call

No row was written twice, so snapshot isolation has no conflict to detect. Each transaction read a consistent snapshot and made a sound decision, and together they broke the rule. The shape is always read, decide, then write a different row from the ones the decision depended on. Double-booked meeting rooms and two signups claiming one username are the same bug.

That last limit matters for "check that nothing exists, then insert" rules such as room bookings: there is no row to lock. Run those transactions as Serializable, or materialize the conflict by pre-creating one row per room and time slot to lock, or let a unique or exclusion constraint enforce the rule. Whatever runs at Serializable needs a retry that re-runs the whole transaction, reads included, because the decision was made on data that changed:

import random, time

RETRY = {"40001", "40P01"}   # serialization_failure, deadlock_detected

def with_retry(run_txn, attempts=5):
    for attempt in range(attempts):
        try:
            return run_txn()          # BEGIN ISOLATION LEVEL SERIALIZABLE ... COMMIT
        except DatabaseError as e:    # e.g. psycopg.Error, which carries .sqlstate
            if e.sqlstate not in RETRY or attempt == attempts - 1:
                raise
            time.sleep(random.uniform(0, 0.02 * 2 ** attempt))   # jittered backoff

Keep side effects such as emails out of the transaction, and send them after the commit succeeds.

-- 1. Atomic conditional update: no read-modify-write in the app at all
UPDATE inventory SET stock = stock - 1
 WHERE sku = 'A-42' AND stock > 0;        -- 1 row: reserved; 0 rows: sold out

-- 2. Optimistic: a version column checked in the same statement
UPDATE docs SET body = $1, version = version + 1
 WHERE id = $2 AND version = $3;          -- 0 rows: someone saved first; reload

-- 3. Pessimistic, but fail fast instead of queueing behind the holder
SELECT * FROM seats WHERE id = 17 FOR UPDATE NOWAIT;

-- 4. A job queue: each worker claims different rows and nobody waits
SELECT id, payload FROM jobs
 WHERE status = 'queued'
 ORDER BY id
 LIMIT 10
 FOR UPDATE SKIP LOCKED;

Reach for the atomic update first: the database reads and writes under one row lock, so two buyers of the last item can't both win, and the affected-row count says who did. The version column is optimistic locking in one statement, and HTTP APIs expose the same check as If-Match (see API design). NOWAIT turns a held lock into an immediate error, which suits a UI that should say "someone else is editing" rather than hang.

SKIP LOCKED (PostgreSQL 9.5+, MySQL 8.0+, Oracle; SQL Server's equivalent is the READPAST hint) turns a table into a work queue: each worker skips rows another worker holds, and a crashed worker's rows unlock when its transaction rolls back. For long jobs, don't hold the transaction open. Claim rows by setting a status and a lease expiry, commit, and let a sweeper requeue expired leases.

When the contended resource lives across services, you reach for a distributed lock (Redis, ZooKeeper, etcd). These are far trickier than in-process locks, because a holder can pause — a long GC, a network stall — past the lock's expiry, then wake up still believing it holds the lock while someone else has taken it.

The fix is a fencing token: a monotonically increasing number issued with each lock grant. The protected resource records the highest token it has seen and rejects any write carrying a lower one — so a stale holder's writes bounce, even if its lock looks valid.

Two details decide whether fencing works. The token must come from something that never issues the same number twice, even across failovers, and consensus stores provide one: etcd stamps every write with a cluster-wide revision that only grows, and ZooKeeper does the same with its transaction ID (zxid). And the resource must check the token atomically with the write:

UPDATE files SET body = $1, fence = $2
 WHERE name = $3 AND fence <= $2;   -- 0 rows: a newer lock holder exists, so stop

A lock service that can't produce such a token can't fence, which is one of the two main objections in Martin Kleppmann's 2016 critique of Redis's Redlock algorithm. A resource that can't check tokens, such as a third-party API with no version precondition, can't be fenced either; there, rely on idempotency keys.