Optimistic vs Pessimistic Locking
Stop concurrent writers overwriting each other: lock the row before reading, or check a version on write. When each wins and how to build both.
When two writers read the same row, change it and write it back, the second write silently erases the first: a lost update. Pessimistic locking prevents the collision by locking the row before reading it, so the second writer waits. Optimistic locking lets both proceed and checks at write time that the row still has the version they read; the loser's write matches zero rows and it retries or reports a conflict. Pessimistic pays in waiting, optimistic pays in retries, and contention decides which is cheaper.
Context
Locking is as old as shared databases: IBM's System R in the 1970s introduced two-phase locking, where a transaction takes locks as it goes and releases them at commit. In 1981 Kung and Robinson proposed the alternative, "optimistic concurrency control": assume conflicts are rare, do the work without locks, and validate before committing. Both survive today at every layer. Databases expose pessimistic locks through SELECT … FOR UPDATE; ORMs (object-relational mappers) implement optimistic locking with a version column, as in JPA's (Jakarta Persistence API) @Version; HTTP has it built in as ETags (entity tags) and If-Match; and CPUs offer the same idea as a single instruction, CAS (compare-and-swap).
You have seen the problem whenever two people edited the same record and one of them lost their changes without any error. In code it looks innocent:
// two requests run this at the same time for the same account
const {balance} = await db.one(
'SELECT balance FROM accounts WHERE id = $1', [id])
await db.none(
'UPDATE accounts SET balance = $1 WHERE id = $2', [balance - 100, id])
// both read 500, both write 400: one withdrawal vanished- Lost update
- Two read-modify-write cycles overlap and the later write overwrites the earlier one, which nobody notices.
- Row lock
- A lock on one row held until the transaction ends. Other writers, and other FOR UPDATE readers, wait for it.
- Version
- A counter (or timestamp, or hash) that changes on every write. Comparing it tells you whether someone wrote since you read.
- Contention
- How often concurrent writers target the same rows. It is the single number that decides between the two strategies.
- Deadlock
- Two transactions each hold a lock the other is waiting for. The database aborts one of them.
Why it matters
Lost updates do not throw errors; they show up weeks later as a wrong balance, an oversold item, a reverted edit or two workers processing the same job. The default isolation level of PostgreSQL and MySQL does not prevent them for read-modify-write code, so the protection has to be chosen deliberately. Choose wrong and you either serialise a hot path behind a lock, or burn CPU on retry storms when everyone edits the same row.
Wait first or check later
Both strategies serialise the two writers; they differ in when the conflict is detected. Pessimistic locking detects it at read time, before any work is done, by making the second reader wait. Optimistic locking detects it at write time, after the work is done, by making the second write fail its version check.
| Pessimistic | Optimistic | |
|---|---|---|
| Conflict found | Before the work: second writer waits | After the work: second write matches 0 rows |
| No contention | Pays for a lock held across the work | Pays one extra WHERE clause, holds nothing |
| High contention | Writers queue; no work is wasted | Many retries and wasted work; some may starve |
| Human think time | Never hold a lock while a user edits a form | Natural fit: the version travels with the form |
| Typical failure | Lock waits, deadlocks, long transactions | Conflict errors the caller must handle |
Pessimistic locking in practice
SELECT … FOR UPDATE takes a row lock on every row it returns and holds it until the transaction commits or rolls back. Another transaction asking for the same rows with FOR UPDATE, or trying to update them, waits. Plain SELECT still reads without waiting, because MVCC (multi-version concurrency control) serves it the last committed version. Two modifiers change what happens when the row is already locked: NOWAIT fails immediately instead of waiting, and SKIP LOCKED silently leaves the locked rows out of the result (PostgreSQL since 9.5, MySQL since 8.0).
SKIP LOCKED is what turns an ordinary table into a job queue with many workers: each worker claims the oldest job nobody else is holding, and workers never block each other.
BEGIN;
-- claim one job that no other worker holds right now
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- ... do the work, still inside the transaction ...
UPDATE jobs SET status = 'done', finished_at = now() WHERE id = $1;
COMMIT; -- if the worker crashes, the lock is released
-- and the job becomes claimable againDeadlocks and lock ordering
Locks taken in different orders can deadlock: a transfer from A to B locks A then B, a simultaneous transfer from B to A locks B then A, and each waits forever for the other. The database detects the cycle and aborts one transaction: PostgreSQL after deadlock_timeout (1 second by default) with SQLSTATE 40P01, InnoDB immediately with error 1213. The fix is to always lock in a consistent order, and to retry the aborted transaction.
-- lock both accounts in id order, whatever the transfer direction
SELECT id, balance FROM accounts
WHERE id IN ($from, $to)
ORDER BY id
FOR UPDATE;
-- one cron run at a time across every app instance:
-- an advisory lock locks a number, not a row
SELECT pg_try_advisory_lock(4242); -- false = another run holds itOptimistic locking in practice
Give the row a version column. Read it together with the data, and make the write conditional on it still being the same while incrementing it in the same statement. Zero affected rows means someone else wrote first. No lock is held between the read and the write, so the read can happen in one request and the write minutes later in another, after a person has finished editing.
// Drizzle: the version check is just part of the WHERE clause
const rows = await db
.update(docs)
.set({body, version: sql`${docs.version} + 1`})
.where(and(eq(docs.id, id), eq(docs.version, expectedVersion)))
.returning({version: docs.version})
if (rows.length === 0) throw new ConflictError(id) // someone saved first
return rows[0].versionSome ORMs do this for you. JPA and Hibernate add the check to every update of an entity that has a @Version field and throw OptimisticLockException on a mismatch. Prisma and Drizzle have no built-in version field, so you write the condition yourself as above (with Prisma, updateMany with the version in where and a check on count).
@Entity
class Doc {
@Id Long id;
@Version long version; // Hibernate manages it
String body;
}
// generated: UPDATE doc SET body=?, version=? WHERE id=? AND version=?
// 0 rows updated -> jakarta.persistence.OptimisticLockExceptionAcross HTTP: ETag and If-Match
HTTP has optimistic locking in the protocol (RFC 9110, section 13). The server sends the version as an ETag; the client sends it back in If-Match when it saves; the server answers 412 Precondition Failed if the resource changed in between. A server can insist on the header by answering 428 Precondition Required (RFC 6585) when it is missing.
app.put('/docs/:id', async (req, res) => {
const ifMatch = req.get('If-Match')
if (!ifMatch) return res.status(428).end() // demand a version
const expected = Number(ifMatch.replace(/"/g, ''))
const saved = await saveIfVersion(req.params.id, expected, req.body)
if (!saved) return res.status(412).end() // changed since GET
res.set('ETag', `"${saved.version}"`).json(saved)
})Retrying the loser
When the conflicting write came from code rather than a person, the usual answer is to retry the whole read-compute-write cycle a few times with jittered backoff. If retries are frequent, contention is high and a lock, an atomic update or a redesigned data model is cheaper. When a person made the change, do not retry blindly: show them what changed and let them merge.
async function withRetry<T>(fn: () => Promise<T>, attempts = 4) {
for (let i = 1; ; i++) {
try {
return await fn() // re-reads fresh data every time
} catch (err) {
if (!(err instanceof ConflictError) || i === attempts) throw err
const backoff = 20 * 2 ** i
await sleep(backoff / 2 + Math.random() * backoff) // jitter
}
}
}Locks outside the database
A lock in Redis or ZooKeeper (a distributed coordination service) protects things the database cannot see, like calls to an external API. Such locks always come with a TTL (time to live), so a crashed holder does not block everyone forever, and that expiry is exactly what makes them unsafe on their own: a process can pause for longer than the TTL, during a GC (garbage collection) pause or a network stall, wake up believing it still holds the lock, and write after another holder took over. Martin Kleppmann's 2016 analysis of Redis's Redlock made this argument well known.
- 1Client 1 acquires the lock with a 10-second TTL and receives fencing token 33, a number that increases with every grant.
- 2Client 1 stalls for 15 seconds. The lock expires, and client 2 acquires it with token 34 and writes to storage with token 34.
- 3Client 1 wakes up and sends its write with token 33. Storage has already seen 34, so it rejects 33. Without the token check, client 1 would have overwritten client 2.
- 4The fencing check is optimistic locking again: the protected resource compares a number on every write. If the resource cannot do that check, a lease-based lock is an efficiency hint, not a guarantee.
Pitfalls
- Holding a row lock across slow work
A transaction that runs
FOR UPDATEand then calls a payment provider or waits for user input keeps the lock for the whole call. Every other writer to that row queues behind it, connection pools drain, and in PostgreSQL the long transaction also holds back vacuum. Do slow work outside the transaction, or switch to a version check. - A version column the code forgets to check
Optimistic locking only works if every write path includes the version in its
WHEREclause. One admin script or background job that updates by id alone reintroduces lost updates silently. Enforce it in one repository function, or let the ORM do it with@Version. - Optimistic locking on a hot row
A global counter or a flash-sale stock row updated by hundreds of requests per second makes almost every optimistic write fail, and retries multiply the load that caused the conflicts. Use an atomic
UPDATE … SET n = n - 1 WHERE n > 0, a pessimistic lock, or split the counter into several rows that are summed on read. - Locking rows in an arbitrary order
Code paths that lock the same set of rows in different orders deadlock under load, and the database aborts one of them. Sort ids before locking, lock parents before children consistently, and treat deadlock errors as retryable.
- Trusting a TTL lock to protect correctness
A Redis lock can expire while its holder is paused, after which two clients both believe they hold it. Use it to avoid duplicate work, and protect the write itself with a fencing token or a version check in the system of record.
Interview questions
Q1What is the difference between optimistic and pessimistic locking?
Pessimistic locking takes a lock before reading, so a conflicting writer waits; optimistic locking takes no lock and checks a version at write time, so a conflicting writer fails and retries. Both prevent lost updates. Pessimistic costs waiting and the risk of deadlocks; optimistic costs wasted work and retries when conflicts actually happen.
Q2When would you choose each?
Optimistic when conflicts are rare or when the gap between read and write includes human think time, such as editing a form, because you must never hold a lock while a person types. Pessimistic when the same rows are contended heavily and the critical section is short, such as claiming jobs or moving money between two accounts. And neither when a single atomic UPDATE can do the whole thing.
Q3Walk me through implementing optimistic locking for a REST endpoint that edits a document.
Add a version column. GET returns the document with the version as an ETag. PUT requires If-Match, answering 428 if it is missing, and runs UPDATE with WHERE id = ? AND version = ?, incrementing the version in the same statement. One row updated returns 200 with the new ETag; zero rows returns 412, and the client refetches, shows the user what changed, and lets them merge and resend.
Q4What happens when two transactions lock the same two rows in opposite order?
They deadlock: each holds one row and waits for the other. The database detects the cycle, PostgreSQL after deadlock_timeout and InnoDB immediately, and aborts one transaction with a deadlock error while the other proceeds. The fix is to lock rows in a consistent order, for example sorted by id, and to retry transactions that fail with a deadlock error.
Q5How would you build a job queue on PostgreSQL with several workers?
A jobs table and SELECT … FOR UPDATE SKIP LOCKED with LIMIT 1, ordered by creation time, inside a transaction. Each worker gets a job no other worker holds without waiting, and if a worker crashes, the transaction rolls back and the job becomes visible again. For long jobs, mark the row as running with a heartbeat instead of holding the transaction open, and treat processing as at-least-once.
Q6Why is a Redis lock with a TTL not enough to protect a write?
Because the lock can expire while its holder is paused by garbage collection or a network stall, and the holder has no way to notice before it writes. Another client acquires the lock meanwhile, so two writers proceed. The protected resource has to check a fencing token, a monotonically increasing number issued with each lock, and reject writes carrying an older one.
Q7What happens under optimistic locking when contention is very high?
Most writes fail their version check, every failure is a full read-compute-write that was wasted, and the retries add load to the very rows that are contended, so throughput can drop below a simple queue. Some clients may lose repeatedly and starve. At that point I would switch to an atomic update, a pessimistic lock, or shard the hot row into several sub-counters.
- A read-modify-write without protection loses updates silently, and default isolation levels do not stop it.
- Pessimistic locking makes the second writer wait at read time; optimistic locking makes it fail at write time and retry.
- Low contention or human think time: optimistic with a version column, end to end via ETag and If-Match. High contention and short work: FOR UPDATE.
- FOR UPDATE SKIP LOCKED turns a table into a multi-worker job queue; lock rows in a consistent order to avoid deadlocks.
- An atomic UPDATE with a condition beats both when the database can compute the new value itself.
- Leased distributed locks can expire under a paused holder; protect the write with a fencing token.