Optimistic vs Pessimistic Locking Explained

Quick Answer

Pessimistic locking stops a conflict before it can happen by making a transaction hold a real database lock (SELECT ... FOR UPDATE) on a row for as long as it needs it, forcing any other transaction that wants the same row to wait. Optimistic locking lets every transaction proceed without locking anything, and instead checks at write time — usually with a version column and a conditional UPDATE — whether the row changed since it was read, rejecting the write if it did. We ran both against a real MySQL-compatible database with two real concurrent sessions: one that actually blocked and timed out, and one that actually got rejected with zero rows affected.

The Problem Both Strategies Are Trying to Prevent

Both strategies exist to stop the same failure, commonly called the lost update problem: two transactions read the same row, each computes a new value based on what it read, and whichever one writes last silently overwrites the other's change as if it had never happened. Concretely: an account holds a balance of 1000. Two withdrawals, of 100 and 200, both read balance = 1000 before either has written anything. Each computes its own new balance — 900 and 800 — and whichever writes second simply replaces the first result, instead of the database ending up with the correct combined balance of 700. One withdrawal effectively vanishes, with no error, no warning, and no trace that it ever happened.

The uncomfortable part is that each individual statement here is perfectly correct in isolation — the danger lives entirely in the gap between the read and the write, not inside either operation. Pessimistic and optimistic locking are two opposite philosophies for closing that exact gap: one prevents anyone else from entering it, the other lets everyone in and checks for damage on the way out.

Pessimistic Locking: Block the Conflict From Ever Happening

SELECT ... FOR UPDATE inside a transaction takes a real row-level lock in the database. Any other transaction that tries to lock or write that same row has to wait until the first one commits or rolls back and releases it. We demonstrated this for real, using two actual concurrent MySQL client sessions against a live MariaDB server rather than describing it abstractly.

Session A started a transaction, ran SELECT * FROM accounts WHERE id = 1 FOR UPDATE, and then deliberately held that lock for 4 seconds — a stand-in for real business logic running while a lock is held — before committing. About a second later, Session B, configured with a 2-second lock wait timeout, tried to take the same lock and, after genuinely waiting out its timeout, got this real, unedited error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The real timestamps from that run confirm actual time elapsed on both sides, not a simulated delay: Session A acquired its lock at 22:44:52.737 and committed at 22:44:56.761; Session B attempted its own lock at 22:44:53.781, failed after its 2-second timeout expired, and — retried once A had committed — succeeded immediately at 22:45:04.135.

The 4-second hold in that test wasn't an arbitrary number — it stands in for any real work done while a lock is held: an outbound API call, a slow computation, or a user thinking before confirming an action. The longer that window stays open, the more transactions queue up behind it, and with multiple locks taken in different orders by different transactions, that queuing is exactly how deadlocks happen.

Optimistic Locking: Let It Happen, Then Catch It

Optimistic locking takes the opposite approach: add a version column to the row, have every writer read the current version along with the data, and write with a conditional statement — UPDATE ... WHERE id = ? AND version = ? — that also increments the version. No lock is taken at read time, so nothing blocks; the number of rows the UPDATE actually affects tells you, after the fact, whether you won the race.

We ran this for real too, starting from an accounts row with balance = 1000 and version = 0:

-- Client A, still holding its stale read of version = 0
UPDATE accounts SET balance = balance - 100, version = version + 1
  WHERE id = 1 AND version = 0;
-- rows_affected: 1  (balance now 900, version now 1)

-- Client B, also holding its own stale read of version = 0
UPDATE accounts SET balance = balance - 200, version = version + 1
  WHERE id = 1 AND version = 0;
-- rows_affected: 0  (version had already moved to 1 — no rows matched)

Client B's update matched zero rows — not an exception, not a deadlock, just an ordinary statement that found nothing to update, because the version it was conditioned on no longer existed. After re-reading the row (now version = 1, balance = 900) and retrying with the current version, Client B's update succeeded and affected exactly 1 row, leaving the database at the correct final balance of 700.

Worth saying plainly: neither client ever took a lock in this entire sequence, and both of Client A's and the retried Client B's statements committed instantly with nobody waiting on anybody. The conflict stayed invisible until the precise moment of the write, and detecting it cost nothing more than comparing an integer.

Comparing the Real Trade-offs, Not Just the Definitions

Pessimistic locking's strength is that correctness is enforced structurally — no retry logic is required anywhere in application code, because the second writer simply cannot proceed until it's safe to. Its weakness, made concrete by the timeout above, is that any lock held across something slow — a network call, user interaction, a long-running computation — directly becomes another transaction's wait time, and multiple locks taken in inconsistent order across transactions is exactly how deadlocks occur.

Optimistic locking's strength is that nothing blocks under low contention, so throughput scales far better when conflicts are rare, which describes most real applications most of the time. Its weakness is that application code now has to handle the rejection case explicitly — retry, merge, or surface a message to the user — and it offers no protection against a writer that skips the version check entirely and overwrites the row directly.

The trade-off that surprises people most in practice: a UI flow where someone opens a record, thinks for thirty seconds, and then hits save is a genuinely bad fit for pessimistic locking — nobody should be holding a live database lock across human think-time — and a natural fit for optimistic locking, which only cares about the instant of the actual write, not what happened before it.

What Your Application Code Actually Does When a Conflict Happens

For pessimistic locking, a lock-wait-timeout failure like the real ERROR 1205 shown earlier should generally be retried with a short backoff a small number of times, since it usually means the row was briefly busy, not that the operation itself is invalid. If it keeps failing after a few retries, that's a genuine signal of real contention worth investigating, not something to retry indefinitely.

For optimistic locking, a zero-rows-affected result means someone else's write already landed first. The standard responses are: automatically re-read the current row and retry the whole read-modify-write cycle when the operation is safe to simply redo; attempt a field-level merge when only some fields actually conflict; or surface it to a human as a plain "this was updated since you loaded it, please refresh" message when the change reflects a decision a person made, not just a number that's safe to recompute silently.

When to Actually Choose Each One

Reach for pessimistic locking when contention is genuinely high and conflicts are the expected, normal case — a seat-booking system at the exact moment a popular show goes on sale, or an inventory decrement during a flash sale — and where the cost of a wasted retry (re-running a whole complex operation from scratch) is higher than the cost of a short, bounded wait.

Reach for optimistic locking as the default for most CRUD-style applications, anything involving a human editing a record over a stretch of time, and anywhere you're operating across multiple services or a distributed system, where holding a database lock across a network call isn't realistic in the first place.

The two aren't mutually exclusive within a single system. It's entirely normal to apply pessimistic locking to the small number of genuinely hot, short, high-contention operations, and use optimistic locking — or no explicit concurrency control at all — everywhere else in the same application.

Frequently Asked Questions