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.

Here Time is what a caller waits for, and Space is what the technique holds or stores while it works.

The order to try them in: one atomic statement, then a version check, then a row lock, then Serializable with retries. Reach for a distributed lock only when no single database owns the data.

• Doing the arithmetic in the app

  SELECT stock, subtract one in code, then UPDATE … SET stock = 41 is a lost update waiting to happen at Read Committed, and at MySQL’s Repeatable Read too. Write stock = stock - 1 with a WHERE stock > 0 guard so the database does the read and the write under one lock.

• A mutex that only one process can see

  A threading.Lock or synchronized block protects memory inside one process. Run four replicas of the service and you have four independent locks around the same row. Shared data needs a lock the database or a lock service owns.

• A network call inside the transaction

  Calling a payment API or waiting for a user while holding FOR UPDATE stretches a millisecond lock into seconds, and every other writer to that row queues behind it. Do the slow call before or after; inside the transaction, only read and write.

• Locking reads without an index

  InnoDB locks every index record a locking read or UPDATE scans, not just the rows it returns. With no usable index on the WHERE column, it scans the whole table, and every row ends up locked. Check the plan of any statement that takes locks.

• Leaving transactions open

  A session that is “idle in transaction” keeps its snapshot alive, so PostgreSQL’s VACUUM cannot remove versions newer than it, and tables bloat. It may also hold locks. Set idle_in_transaction_session_timeout, and make sure every code path commits or rolls back.

• Bank transfers

  A transfer locks two account rows. If one request locks A then B and another locks B then A, you get the deadlock from the runnable example. The standard fix is to always lock the lower account id first, whichever direction the money moves.

• ORM optimistic locking

  Rails adds a check on a lock_version column automatically and raises StaleObjectError when it fails. JPA does the same with a @Version field and OptimisticLockException. Both run the version-column pattern from this page; you still have to catch the error and retry or tell the user.

• Compare-and-swap in memory

  Java’s AtomicInteger.compareAndSet and Go’s sync/atomic are optimistic concurrency at CPU level: read, compute, swap only if the value is unchanged, else loop. Lock-free queues and counters are built on exactly that loop.

• Kubernetes leader election

  Controllers that must run as a single active copy, such as the scheduler, hold a Lease object in the API server and renew it before it expires. That is a lease, not a guarantee, so the same rule applies: work that must never run twice needs fencing or idempotency on top.