Optimistic and pessimistic locking
Two ways to stop concurrent writers overwriting each other: lock the row before changing it, or check at write time that it hasn't changed since you read it. Pick by how often writers collide and what a retry costs.
What it is
Both strategies solve the lost update: two sessions read the same row, each computes a new value from what it saw, and both write. The second write lands on top of the first, and the first change is gone. A counter column shows the shape: two users who read it with ordinary selects can both see the same value and hand out a duplicate. The pattern catalog from Patterns of Enterprise Application Architecture frames the choice as a bet: the pessimistic form assumes conflicts are likely and limits how many sessions work on the same data at once; the optimistic form assumes conflicts are rare, lets everyone work on the same records, and checks for a clash just before committing.
Pessimistic locking takes the lock first. In PostgreSQL 17, SELECT ... FOR UPDATE locks the rows it returns; another transaction that tries to UPDATE, DELETE or SELECT ... FOR UPDATE the same rows blocks until the locking transaction ends, and the lock is released at commit or rollback. Row locks don't block plain reads: a SELECT without a locking clause still returns the row. FOR SHARE takes a shared lock instead, which keeps writers out but lets other FOR SHARE readers through. MySQL 8.4's InnoDB offers the same two clauses, releases their locks on commit or rollback, and needs autocommit off so the lock lives inside a transaction you control.
Optimistic locking holds no lock between the read and the write and verifies at write time. Each row carries a version number that goes up with each update. A writer reads the row and its version, then sends an update whose condition says the version still equals what it read. DynamoDB documents exactly this: a Version attribute, an update with a condition expression on it, and a ConditionalCheckFailedException when another process got there first. The fix for a failure is to read again, recompute and resend; each retry costs another read, so cap the number of attempts. HTTP has the same idea built in: a client sends PUT with If-Match carrying the ETag it last saw, and the server answers 412 Precondition Failed when the resource has changed, leaving the client to start over or show the user both versions.
-- Pessimistic: the lock is taken on read and held to COMMIT
BEGIN;
SELECT qty FROM seats WHERE id = 42 FOR UPDATE; -- other writers wait here
UPDATE seats SET qty = qty - 1 WHERE id = 42;
COMMIT; -- lock released
-- Optimistic: nothing held after the read, the version is the check
SELECT qty, version FROM seats WHERE id = 42; -- returns version 7
UPDATE seats SET qty = qty - 1, version = 8
WHERE id = 42 AND version = 7; -- 0 rows updated: retryWhen to use it
Pessimistic fits when the same row is written by many sessions at nearly the same time, so conditional writes would fail often, and when the work after the read is expensive to repeat. It also fits when several rows must stay consistent together inside one transaction: lock the parent row with FOR SHARE before inserting a child, so nobody can delete the parent in between. For a table of jobs pulled by many workers, MySQL 8.4 adds NOWAIT and SKIP LOCKED to its locking reads, so a worker either fails fast or passes over rows another worker already holds instead of queuing behind them. Row locks live inside transactions, so the isolation level you run at changes what a locking read does when the row moved under it; see Transactions and isolation levels.
Optimistic fits when conflicts are infrequent and a retry is cheap, and when you'd rather not run a lock service at all; DynamoDB's guidance names inventory updates and collaborative editing as typical cases. It is also the right shape whenever the edit spans user think time: a form that stays open while the user works can carry the version it loaded and send it back, while a row lock held that long keeps a transaction open and leaves other writers waiting indefinitely, which is why holding a transaction open for user input is a documented bad idea. The same logic covers public APIs, where ETag and If-Match give clients the version check without the server keeping per-client lock state.
When not to
Skip both when a single statement does the job. UPDATE seats SET qty = qty - 1 WHERE id = 42 AND qty > 0 applies the change and the check in one atomic step; the database serializes concurrent writers on that row for you, and the caller learns from the row count whether it won. Reach for a version column or an explicit lock when the new value depends on something you computed outside the statement.
Skip optimistic locking on a hot row. When hundreds of writers target the same item, conflicts become frequent and the retries multiply reads and write cost, which is the main cost of the pattern under high contention. Skip it too when a retry can't be made invisible: if a failed write means a person must redo their edit, frequent conflicts turn into a bad product, not a technical detail.
Skip pessimistic locking across anything slow or external: a user's think time, a call to another service, a queue publish. In PostgreSQL 17 a transaction waiting for a conflicting row lock waits until it is released, and explicit locking raises the chance of deadlocks, which the server resolves by aborting one of the transactions involved. Two transactions that lock the same rows in different orders are one documented cause; take locks in a consistent order and be ready to retry an aborted transaction. When the thing to protect isn't a database row at all, a lease with an expiry replaces the row lock; see Distributed locks and leases.
Compared
| Question | Pessimistic (lock first) | Optimistic (check at write) |
|---|---|---|
When the conflict is handled | Before the change: the second writer waits for the lock | At write time: the second writer's condition fails |
Cost when conflicts are rare | Each writer pays for the lock, even with nobody around | Low: one column, a condition and the retry handling around it |
Cost when conflicts are common | Writers queue; the row's throughput is bounded by how long each holds the lock | Many failed writes, each followed by a re-read; a lock or an atomic statement usually does better here |
Edit spans user think time | Poor: a transaction stays open, and other writers wait with no deadline | Good: the version travels with the form or the ETag |
Several rows must change together | Usually the better fit: lock the rows in a consistent order inside one transaction | Harder: each row's check is separate unless the conditional writes share one transaction |
Failure you must handle | Deadlock (the server aborts one transaction) and long waits | A conflict error and a retry loop with a cap |
Where you see it |
| Version columns, DynamoDB condition expressions, HTTP |
In a picture
How to say it in an interview
The common mistake
Quiz
Questions that use this
Related
Sources
- PostgreSQL 17 documentation: 13.3. Explicit Locking
- MySQL 8.4 Reference Manual: 17.7.2.4 Locking Reads
- Amazon DynamoDB Developer Guide: Optimistic locking with version number
- martinfowler.com: Optimistic Offline Lock (Patterns of Enterprise Application Architecture catalog)
- MDN: HTTP conditional requests