Optimistic vs pessimistic locking

Pessimistic locking assumes someone else is about to change your row, so it locks the row before reading and makes everyone else wait. Optimistic locking assumes nobody will, so it reads freely and checks at write time that the row hasn’t changed, throwing away and retrying the loser’s work if it has. Both stop the same races. They differ in who pays when a conflict happens, and how fast each one breaks down as contention rises.

Picture it first: the clubhouse register

Your housing society books its clubhouse from one register. Two committees have run it two ways.

The locked drawer. There is one key. You take it, read the register, write your name and hand it back; everyone else waits at the door. Take a phone call while holding the key and the queue grows. Hold the clubhouse key while waiting for the gym key that your neighbour holds while waiting for yours, and you both stand there until the secretary sends one of you home.

The open register. Every entry is numbered. Anyone may photograph the page, decide at home, and come back to write, but only if the last entry is still number 41. If someone wrote since, you reread and rethink. Nobody queues, but on Diwali eve, with forty families wanting one night, most make several trips.

Map it back: the key is a row lock, the queue a lock wait, the two-key standoff a deadlock, the entry number a version, the extra trips retries. On Diwali eve neither works; the society runs a lottery. A hot row needs a different design, not a better lock.

Two philosophies

Pessimistic concurrency control prevents conflicts: lock what you will touch before acting, and anyone wanting a conflicting lock waits. Its classic form is two-phase locking (2PL), which MySQL and SQL Server use for SERIALIZABLE (serializable isolation).

Optimistic concurrency control (OCC) detects conflicts. H. T. Kung and John Robinson proposed it in 1981, arguing that locking is often needed only in the worst case. Each transaction has three phases:

1. Read: read freely, write only to private copies.
2. Validate: at commit, check that no transaction that committed meanwhile wrote anything this one read.
3. Write: publish the copies, or discard the work and restart.

Their conclusion sums up the trade-off: locking’s hard problem is deadlock, solved by aborting someone; optimism’s is starvation, solved by locking.

Real engines mix both. Under MVCC, plain reads take no locks, but every UPDATE takes a row lock that other writers wait for, and PostgreSQL’s REPEATABLE READ then validates and aborts the loser with 40001. The engine-level algorithms (2PL, OCC, timestamp ordering, SSI) are compared in concurrency control.

In application code it comes down to two shapes, both coded in the lost update problem: SELECT … FOR UPDATE before the read, or a version checked in the UPDATE’s WHERE clause. This page is about choosing between them and running each well.

Pessimistic locking in depth

Row lock modes in PostgreSQL

PostgreSQL has four row-level lock strengths. A plain SELECT takes none, so readers never wait for row locks.

The split that matters is FOR UPDATE versus FOR NO KEY UPDATE. If you lock a wallet only to change its balance, ask for FOR NO KEY UPDATE: it still serializes writers, but it doesn’t block inserts into child tables whose foreign-key checks take FOR KEY SHARE on the parent (see foreign key design). PostgreSQL marks row locks on the rows themselves rather than in memory, so there is no limit on how many you hold, but a SELECT … FOR UPDATE writes to every row it locks.

InnoDB: record, gap and next-key locks

InnoDB has shared (S) and exclusive (X) row locks: FOR SHARE takes S, FOR UPDATE takes X. The surprise is what gets locked: index records, not rows. At the default REPEATABLE READ, searches take next-key locks (a record plus the gap before it), so SELECT … WHERE seat_no BETWEEN 10 AND 20 FOR UPDATE also blocks anyone inserting seat 15, and a locking read on an unindexed column can lock the whole table. A lookup by a unique index locks just one record, and READ COMMITTED turns gap locking off for searches. The full mechanics are in phantom read. Locking reads only lock anything inside a transaction (START TRANSACTION or autocommit = 0).

Waiting, failing fast, skipping

When the row you want is already locked, you have four options.

Use a bounded wait on user-facing paths. With the default, a request stuck behind a slow transaction keeps its connection for as long as that transaction lives, and one stuck lock can drain a whole connection pool. PostgreSQL’s idle_in_transaction_session_timeout and (since version 17) transaction_timeout cap the damage from sessions that wander off.

Table locks, granularity and escalation

PostgreSQL also has eight table-level lock modes. Only ACCESS EXCLUSIVE, taken by many ALTER TABLE forms, blocks plain reads, which makes for a classic outage: an ALTER TABLE queued behind a long transaction makes every later query on the table queue behind it. Migration tools set a short lock_timeout and retry (online schema and data migrations).

Granularity is a trade-off: row locks maximise concurrency but cost memory per lock; table locks are cheap but block everyone. SQL Server bridges the two with lock escalation, replacing a statement’s row locks with one table lock once it holds about 5,000 on one table, so a 6,000-row batch update can suddenly block the whole table. PostgreSQL and InnoDB never escalate: PostgreSQL marks row locks in the rows, and InnoDB’s glossary says its compact lock format makes escalation unnecessary.

Deadlocks

A deadlock is a cycle in the wait-for graph: T1 waits for a lock that T2 holds, while T2 waits for one that T1 holds. Neither wait can ever end, so the database breaks the cycle by aborting someone.

The textbook case is two transfers in opposite directions. Asha pays Bilal ₹500 while Bilal pays Asha ₹300. Each transaction debits the payer first, so T1 locks row 101 and T2 locks row 205; then each asks for the other’s row.

How each engine breaks the cycle:

• PostgreSQL waits deadlock_timeout (1 s by default) before running its relatively expensive check, betting that most waits end on their own, then aborts one transaction with SQLSTATE 40P01; which one is hard to predict.
• InnoDB checks as soon as a transaction starts waiting (innodb_deadlock_detect, on by default) and rolls back the one that has changed the fewest rows, with ERROR 1213 (40001). Under very high concurrency the check itself can slow the server, and MySQL suggests turning it off and relying on innodb_lock_wait_timeout.

Detection keeps you alive; prevention keeps you fast:

1. Lock in a consistent order. Sort the rows you will touch by primary key, and ideally lock them all in one statement.
2. Take the strongest lock first. Two transactions that each hold FOR SHARE on a counter and then try to update it deadlock; the MySQL manual uses exactly this example. Ask for FOR UPDATE up front.
3. Keep transactions short, with no network calls inside.
4. Index the predicate (InnoDB) so a locking statement touches only the rows it needs.
5. Retry the victim from BEGIN, with backoff. MySQL’s manual puts it bluntly: deadlocks are not dangerous, just try again. The retry loop is in transaction isolation levels.

-- PostgreSQL, READ COMMITTED: Bilal (205) pays Asha (101) ₹300
BEGIN;
SET LOCAL lock_timeout = '2s';
SELECT id, balance
FROM   wallets
WHERE  id IN (205, 101)
ORDER  BY id                 -- 101 is locked first, whoever is paying whom
FOR NO KEY UPDATE;
-- app: check Bilal's balance and daily limit
UPDATE wallets SET balance = balance - 300 WHERE id = 205;
UPDATE wallets SET balance = balance + 300 WHERE id = 101;
COMMIT;

PostgreSQL applies ORDER BY before it takes the row locks, so the rows are locked in sorted order. The opposite transfer now queues behind this one instead of closing a loop.

SKIP LOCKED: a queue inside your database

A jobs table with a status column is the simplest work queue, and enqueuing can happen in the same transaction as the business write (the idea behind the transactional outbox). The hard part is giving each job to exactly one worker. Plain SELECT … LIMIT 1 lets two workers grab job 101; adding FOR UPDATE stops that but creates a convoy, since every worker wants the same first row and ten workers end up processing one job at a time.

SKIP LOCKED removes the convoy. Rows that another transaction has locked are simply left out of the result, so each worker walks past its colleagues’ rows to the next free ones:

-- PostgreSQL or MySQL 8.4, READ COMMITTED
BEGIN;
SELECT id, payload
FROM   jobs
WHERE  status = 'ready'
ORDER  BY id
LIMIT  10
FOR UPDATE SKIP LOCKED;
-- process the batch, then:
UPDATE jobs SET status = 'done' WHERE id IN (/* the ids you got */);
COMMIT;

This version holds its locks while it works. If the worker crashes, its transaction rolls back and the jobs reappear: a free retry. The price is an open transaction and a connection per busy worker, which is fine for jobs of a few seconds but bad for ten-minute jobs, since long transactions also hold back vacuum. For long jobs, claim and commit at once, with a lease:

UPDATE jobs
SET    status = 'running', worker_id = 'w-17',
       lease_until = now() + interval '5 minutes'
WHERE  id IN (SELECT id FROM jobs
              WHERE  status = 'ready'
              ORDER  BY id
              LIMIT  10
              FOR UPDATE SKIP LOCKED)
RETURNING id, payload;

A sweeper returns jobs with expired leases to ready. That makes delivery at-least-once, so handlers must be idempotent.

The caveats:

• Inconsistent by design. Both manuals say skipping locked rows gives an inconsistent view: right for a queue, wrong for any read that decides something (a balance, a seat count, a report). An empty result can just mean everything is claimed.
• Ordering is approximate. Batches come out in id order but finish in any order; if job 102 must follow 101, you need per-key ordering.
• Index the ready rows (a partial index ON jobs (id) WHERE status = 'ready') and archive finished jobs, or every poll wades through old rows.

When you need fan-out to many consumer groups, replay, or more throughput than one table absorbs, move to a message queue; design a job scheduler walks through both.

Advisory locks: locking a name, not a row

Sometimes the thing to protect isn’t a row: “the nightly settlement run”, “tenant 42’s migration”. Advisory locks lock an arbitrary key that the database attaches no meaning to; every participant must agree to take it.

In PostgreSQL, transaction-level locks (pg_advisory_xact_lock) are released at commit or rollback; session-level ones (pg_advisory_lock) are held until you unlock them or disconnect, and ignore rollbacks. The pg_try_… variants return false instead of waiting.

-- PostgreSQL: only one instance runs the nightly settlement
SELECT pg_try_advisory_lock(7001);    -- 7001 = "settlement", agreed in code
-- true: run it, then SELECT pg_advisory_unlock(7001);   false: exit

-- One migration per tenant at a time; released at COMMIT or ROLLBACK
BEGIN;
SELECT pg_advisory_xact_lock(2, 42);  -- (2 = migrations, tenant 42)
-- ... migrate tenant 42 ...
COMMIT;

MySQL’s equivalent is GET_LOCK('payments.settlement', 0): a named, session-scoped lock that commit and rollback don’t release. Two gotchas. Session-level locks break behind a transaction-pooling proxy, which PgBouncer lists as never supported, because your next statement may run on a different server connection. And the guarantee stops at the database boundary: if the work writes to object storage or calls a gateway, a session that dies releases the lock while your process may still be running. That is the lease problem, solved with fencing tokens (distributed locking).

Optimistic locking in depth

The basic move is the version compare-and-set from the lost update problem. Three design choices decide whether it holds up.

What to use as the version

xmin is tempting because it costs nothing, but it is 32-bit and wraps, and a background job bumping last_seen_at changes it and invalidates every form that user has open. Prefer a counter you own.

Versioning more than one row

An order with line items is one business object in many rows. If two agents edit different lines, per-line versions won’t notice that together they broke “order total ≤ credit limit”. Version the aggregate root: every line change also runs UPDATE orders SET version = version + 1 WHERE id = ? AND version = ? in the same transaction, at the price of false conflicts between edits that didn’t really clash.

Kung and Robinson’s validation is broader still: nothing the transaction read may have changed, not just the rows it writes. A version check on the updated row misses write skew, where two on-call doctors each read “two on call” and each take themselves off. To guard a decision based on other rows, bump their versions in the same transaction, or run at SERIALIZABLE, which in PostgreSQL is read-set validation done by the engine (serializable isolation).

Optimistic concurrency outside SQL

Most distributed stores make OCC their main write API, because no client should hold a lock across a network.

DynamoDB’s Java mapper adds the condition and increments the version on every save. Elasticsearch needs both numbers because only the pair identifies a change: the sequence number counts operations, and the primary term records which primary assigned it, the same idea as a fencing token. Kubernetes controllers are optimistic all the way down: read, compute the desired state, PUT with the resourceVersion, and on 409 read again.

The contention maths

You don’t need a benchmark to choose. You need two numbers for your hottest row: λ, how many conflicting writes per second hit it, and t, how long each transaction’s window stays open (lock held, or read to commit).

Chance of a conflict. If conflicting writes arrive at random, as a Poisson process at rate λ, the probability that at least one lands inside your window is

p = 1 − e^(−λt)

With t = 20 ms:

The optimistic cost is wasted attempts. If each attempt failed independently with probability p, attempts would average 1/(1 − p), which here simplifies to e^(λt). Treat that as a floor: retries add load, which stretches t, which raises p. And any side effect inside an attempt happens once per attempt.

The pessimistic cost is waiting. Under a lock, the row is busy a fraction ρ = λt of the time. As a rough guide, the textbook M/M/1 queue gives a mean wait of t·ρ/(1 − ρ): with t = 20 ms that is 5 ms at ρ = 0.2, 20 ms at 0.5, 80 ms at 0.8 and 180 ms at 0.9. At ρ = 1 there is no steady state; the queue grows until timeouts fire.

Both share one ceiling. For a single row, both strategies top out at 1/t successful writes per second: 1/0.02 s = 50 per second here. For locking that is obvious. For optimistic it follows from validation: a winner’s window can’t contain another winner’s commit, so successful windows never overlap. Optimism doesn’t raise the ceiling; it spends the overflow on retries instead of queueing.

What that means in practice:

• Use the per-row rate, not total traffic. A Swiggy-style platform taking 3,000 orders a second across 100,000 restaurants averages 0.03 writes a second per restaurant row. Even a restaurant 100 times busier than average sees λt = 3 × 0.02 = 0.06, so p ≈ 6%. A flash sale where 50,000 buyers tap Buy on one SKU within 10 seconds is 5,000 writes a second on one row: λt = 100.
• λt well below 0.1: conflicts are rare and both strategies are cheap. Prefer optimistic, especially across requests.
• λt from about 0.1 to 0.7: both work. Lock if each attempt is expensive or has side effects; stay optimistic if attempts are cheap and pure.
• λt approaching 1: neither works. Shrink t, or split the row.

The cheapest lever is t. A single in-place UPDATE in its own transaction holds the lock for one statement and a commit, not for two round trips plus your app logic. If that takes t from 20 ms to 2 ms, the ceiling rises from 50 to 500 writes a second (1/0.002).

Hot rows: when both strategies fail

Some rows are hot by nature: the one SKU in a flash sale, a celebrity post’s like counter, a big merchant’s wallet. At λt far above 1, locking becomes a queue that times out and optimism becomes a retry storm. The fixes reshape the data so no single row sees that rate (contention and double booking).

Shrink the critical section. Make the write one conditional UPDATE … SET stock = stock - 1 WHERE stock > 0 and do everything else outside the transaction. Gain: no app round trips under the lock. Cost: still one row, still a ceiling of 1/t.

Shard the counter. Keep N rows per counter, add to a random one, and read with SUM, the distributed counter Firestore’s docs describe. Gain: roughly N times the write rate. Cost: every read touches N rows, and a floor such as “stock ≥ 0” now holds only per shard.

-- 16 shards per post (PostgreSQL)
INSERT INTO post_likes (post_id, shard, likes)
VALUES (42, floor(random() * 16)::int, 1)
ON CONFLICT (post_id, shard)
DO UPDATE SET likes = post_likes.likes + 1;

SELECT sum(likes) FROM post_likes WHERE post_id = 42;

One row per unit. For scarce stock, create a row per unit or seat, and let each buyer claim any free one with SKIP LOCKED. Gain: contention spreads over 1,000 rows for 1,000 phones, and each row is claimed once, so nothing oversells. Cost: rows in proportion to stock, holds that must expire, and an empty result that may only mean “all free rows are momentarily locked”.

UPDATE inventory_units
SET    status = 'held', order_id = $1,
       held_until = now() + interval '10 minutes'
WHERE  unit_id = (SELECT unit_id FROM inventory_units
                  WHERE  sku = 'PHONE-42' AND status = 'free'
                  LIMIT  1
                  FOR UPDATE SKIP LOCKED)
RETURNING unit_id;

Queue the writes, or move them to Redis. Put purchases or wallet credits on a queue partitioned by SKU or merchant and let one consumer apply them in batches: no contention, but the user sees “confirming…” (design a digital wallet). Or check and decrement stock in a Redis Lua script, which runs atomically: in-memory speed, but asynchronous replication means a failover can lose decrements, so write orders to the database and reconcile (Redis).

Choosing between them

A hybrid often beats both: start optimistic, and after two conflicts switch that request to FOR UPDATE. It is Kung and Robinson’s own cure for starvation, applied per request.

Going deeper: traps in production

• A lock held across a network call. A FOR UPDATE transaction that calls a payment gateway makes t the gateway’s latency; at 400 ms the row’s ceiling is 2.5 writes a second (1/0.4). Reserve in one short transaction, call the gateway outside it, and confirm in another.
• Side effects in optimistic retries. An email or webhook sent inside a losing attempt goes out again on the retry. Write the event to an outbox in the same transaction and send it after commit.
• False conflicts. A whole-aggregate version, or xmin, conflicts on edits that never clashed. If your conflict rate is high, first count how many were real.
• Pool starvation. Every waiting transaction holds a connection. Use a bounded lock_timeout, short transactions, and a pool sized for waiting, not just for work.
• Finding the blocker. PostgreSQL: pg_blocking_pids(pid) and log_lock_waits. MySQL: SHOW ENGINE INNODB STATUS for the latest deadlock, innodb_print_all_deadlocks to log every one.

Cheat sheet

How to say it in the interview

“Both stop conflicting writes; they differ in who pays. Pessimistic locks first, SELECT … FOR UPDATE in a short transaction, so other writers wait, and I order my locks to avoid deadlocks and set a lock timeout. Optimistic reads a version and writes with WHERE version = ?, so nobody waits and the loser retries; it also works across HTTP requests and in stores like DynamoDB. I choose per row, from conflicting writes per second times the transaction window. Well under 0.1, conflicts are rare, so I go optimistic. Higher, with expensive work, I lock. Near 1, as with a flash-sale SKU, neither survives, so I shrink the window or split the row into unit rows or shards.”

Which would you use for booking the last seat in a Ticketmaster-style system? Neither in textbook form. Each seat is already a row, so I claim it with one conditional update: UPDATE seats SET status = 'held', hold_until = now() + interval '10 minutes' WHERE id = ? AND status = 'free'. One row back means I hold it; zero means the seat is gone, so retrying is pointless. It is a compare-and-set that holds the row lock for one statement. Payment happens after the hold commits, a sweeper releases expired holds, and a waiting room caps λ for a huge on-sale (design Ticketmaster).

How do you avoid deadlocks? Lock rows in the same order everywhere, usually ascending primary key in one SELECT … ORDER BY id FOR UPDATE. Take the strongest lock first instead of upgrading from FOR SHARE, keep network calls out of transactions, and index locking predicates in InnoDB. Then retry from BEGIN on 40P01 or 1213.

What does SKIP LOCKED do and when is it safe? It leaves out rows other transactions have locked instead of waiting for them. That is right for a queue, where any free job will do, and wrong for any read that decides something, because the result deliberately omits rows.

Why not just use a Redis distributed lock? Because the lock and the data would live in different systems. Its lease can expire while a paused holder keeps writing, and a failover can hand the same lock to two clients, so the database would have to check a fencing token on every write, which is a version check anyway. For data in one database, row locks or a conditional update are simpler and correct.

Optimistic locking, but 30% of writes conflict. Now what? 30% means λt ≈ 0.36 (1 − e^(−0.36) ≈ 0.30): about 1.43 attempts on average. First check the conflicts are real, not a whole-aggregate version or xmin bumped by unrelated writes. Then shrink the window: compute before the transaction, or make the write an atomic update. If it is one busy row, switch that path to FOR UPDATE, or fall back to it after two conflicts; if it is a hot row, split it.

Where to go next

• Lost update problem: the race both strategies prevent, with code for each fix.
• Transaction isolation levels: what each level already protects, and the retry loop.
• Serializable isolation: two-phase locking versus SSI, the same two philosophies at the scale of a whole transaction.
• MVCC: why readers take no locks while writers still queue.
• Contention and double booking: hot-row patterns for design interviews.
• Distributed locking: leases, fencing tokens and when you really need them.
• Design a flash sale: one SKU, a hundred thousand buyers.
• Design a job scheduler: SKIP LOCKED queues and when to outgrow them.

References and further reading

1. On Optimistic Methods for Concurrency Control (Kung and Robinson, ACM TODS, 1981)eecs.harvard.eduThe origin of OCC: read, validation and write phases, read-set/write-set validation, and why locking’s problem is deadlock while optimism’s is starvation.
2. PostgreSQL documentation: Explicit Lockingpostgresql.orgTable and row lock modes with their conflict tables, deadlock detection and lock ordering advice, and session- versus transaction-level advisory locks.
3. PostgreSQL documentation: SELECT (the locking clause)postgresql.orgFOR UPDATE / NO KEY UPDATE / SHARE / KEY SHARE, NOWAIT, SKIP LOCKED and its “inconsistent view” warning, and ORDER BY being applied before rows are locked.
4. PostgreSQL documentation: Lock Management settingspostgresql.orgdeadlock_timeout (1 s default) and why the deadlock check waits, plus max_locks_per_transaction and the unlimited number of row locks.
5. MySQL 8.4 Reference Manual: InnoDB Lockingdev.mysql.comShared and exclusive locks, intention locks, record, gap and next-key locks, and gap locking switched off for searches at READ COMMITTED.
6. MySQL 8.4 Reference Manual: Locking Readsdev.mysql.comFOR SHARE versus FOR UPDATE, the FOR SHARE counter deadlock, NOWAIT (error 3572) and SKIP LOCKED for queue-like tables.
7. MySQL 8.4 Reference Manual: Deadlocks in InnoDBdev.mysql.comHow deadlocks form, innodb_deadlock_detect, victim selection, the 200-transaction wait-for limit and the minimise-and-retry advice.
8. SQL Server transaction locking and row versioning guide (Microsoft Learn)learn.microsoft.comLock granularity, escalation at 5,000 locks per table reference or 24% of engine memory, update locks, and LOCK_TIMEOUT error 1222 leaving the transaction open.
9. Kubernetes API concepts: updates and resourceVersionkubernetes.ioPUT with the object’s resourceVersion; a stale version gets 409 Conflict, and clients must retry.
10. Elasticsearch: Optimistic concurrency controlelastic.coSequence numbers and primary terms, and conditional writes with if_seq_no and if_primary_term.