Dev Skills
Tier 2 — distributed

Optimistic vs pessimistic locking: which one, and when?

Lock first and make others wait, or go ahead and check for a conflict when you write. Choose by how often writers collide.

By devskills editorial team · Last updated

1

What problem does the optimistic vs pessimistic choice solve?

When two requests may change the same record, something must stop the second from silently overwriting the first. Pessimistic locking assumes a conflict will happen: it locks the row before reading it, and anyone else who wants it waits. Optimistic locking assumes a conflict is rare: it reads without locking, and at write time checks that nobody changed the row in the meantime, retrying if they did.

Both are correct. They differ in where the cost lands, waiting versus retrying, and that depends on how often writers actually collide. The broader page on transactions and locking introduces both; this one is about choosing between them.

2

What the optimistic vs pessimistic choice costs you

What it costs you
Pessimistic locking costs concurrency: writers to a hot row queue one at a time, a lock held across slow work blocks everyone behind it, and two transactions locking rows in different orders deadlock. Optimistic locking costs wasted work: every conflict throws away an attempt and repeats it, which is cheap when conflicts are rare and collapses into a retry storm when they are common.
3

When to reach for the optimistic vs pessimistic choice, and when not to

Use it when

  • Optimistic: conflicts are rare, such as editing your own profile, admin screens, most CRUD, and any edit that spans user think-time across HTTP requests.
  • Pessimistic: conflicts are frequent and retries are expensive, such as a hot inventory row, or a job queue where workers claim rows.
  • Pessimistic with SKIP LOCKED: many workers pulling distinct jobs from one table without blocking each other.

Do not, when

  • Pessimistic locking across a user's think-time or a network call. A lock held while someone types, or while a payment API responds, blocks everyone else.
  • Optimistic locking on a row that thousands of requests update per second. Most attempts fail and retry, and throughput falls.
  • Either one, when a single atomic statement can do the check and the write together.
4

How the optimistic vs pessimistic choice actually works

p = 10%: most CRUD1.1 attempts
p = 50%: a busy shared record2 attempts
p = 90%: a flash sale on one row10 attempts, all doing the work again

The curve is why the choice depends on contention. At low p, optimistic locking is almost free. As p approaches 1, the retries are the workload, and making writers wait in line is cheaper.

Average attempts per successful optimistic write, if each attempt conflicts with probability p (attempts follow 1 / (1 − p)).

Pessimistic, in SQL. Inside a transaction, SELECT ... FOR UPDATE locks the selected rows until commit or rollback. Another transaction that tries to lock or update them waits. Two options change the waiting: NOWAIT fails immediately if the row is locked, and SKIP LOCKED skips locked rows, which turns a table into a work queue that many workers can poll without blocking each other. Both are supported by PostgreSQL and by MySQL 8.0 and later. Set a lock timeout as well, so a stuck transaction cannot hold everyone up forever.

Optimistic, in SQL. Add a version column (or use an update timestamp). Read the row and its version. Write with UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?, then check the number of rows affected. One row means you won. Zero means someone changed it after you read: re-read, re-apply your change if it still makes sense, and try again, with a cap on attempts. No lock is held between the read and the write, which is what lets the read happen in one HTTP request and the write in another, minutes later.

The option that beats both. When the decision fits in one statement, let the database do it atomically: UPDATE stock SET qty = qty - 1 WHERE sku = ? AND qty >= 1. There is no separate read, so there is no gap to protect, no lock held across application code, and no retry. Reach for optimistic or pessimistic locking when the new value needs logic the database cannot express in the WHERE clause.

Deadlocks are a pessimistic problem. Transaction A locks account 1 then wants account 2; transaction B locks 2 then wants 1. The database detects the cycle and aborts one. Prevent it by always locking in the same order (for example, lowest id first) and keeping transactions short. Optimistic locking cannot deadlock, because it holds nothing while it works.

The same idea outside the database. HTTP has optimistic concurrency built in: the server returns an ETag, the client sends it back in If-Match with its update, and the server answers 412 Precondition Failed if the resource changed. Redis offers WATCH with MULTI/EXEC: the transaction is discarded if a watched key changed. DynamoDB and many key-value stores offer conditional writes. ORMs wrap the version column for you, such as JPA's @Version.

Distributed locks are pessimistic locks with extra failure modes. A lock held in Redis or ZooKeeper can expire while its holder is paused (a long GC pause, a network stall), and the holder then writes believing it still owns the lock. The standard defence is a fencing token: a number that increases with every lock grant, passed with each write, so the storage rejects writes carrying an older token. Martin Kleppmann's essay on distributed locking explains it well.

Choosing, in one sentence. Estimate how often two writers touch the same record within the time it takes to do the work. Rarely: optimistic. Often, with expensive work: pessimistic. Often, with work that fits one statement: an atomic conditional update. The ticket booking problem is a good place to practise all three.

Pessimistic lockingOptimistic locking
MechanismSELECT ... FOR UPDATE, then update, then commitRead with a version, then UPDATE ... WHERE version = ?
Conflict detectedBefore the work: the second writer waitsAt write time: zero rows updated
Cost with no conflictA lock taken and releasedAlmost none
Cost under contentionWriters queue; throughput is one at a time per rowRetries; wasted work grows quickly
DeadlocksPossible; lock in a fixed orderNot possible
Holds a connection while you thinkYes, for the whole transactionNo
Works across HTTP requestsNoYes (version or ETag round-trips with the client)
Good forHot rows, job queues (SKIP LOCKED), expensive workMost CRUD, user edits, low-contention records
Side by side. Neither is "better"; they put the cost in different places.
both patterns in SQL (PostgreSQL)
-- pessimistic: lock, decide, write, commit
BEGIN;
SELECT qty FROM stock WHERE sku = 'A1' FOR UPDATE;      -- others wait here
-- application checks qty, computes the new value
UPDATE stock SET qty = 4 WHERE sku = 'A1';
COMMIT;
-- a job queue: each worker claims a different row, nobody blocks
SELECT id FROM jobs WHERE state = 'ready'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;
-- optimistic: no lock; the WHERE clause detects a conflict
SELECT qty, version FROM stock WHERE sku = 'A1';         -- say qty 5, version 12
UPDATE stock SET qty = 4, version = version + 1
WHERE sku = 'A1' AND version = 12;                        -- 0 rows: someone won, retry
optimistic retry loop in Go (database/sql)
var ErrConflict = errors.New("stock changed too often, try again")
func Reserve(ctx context.Context, db *sql.DB, sku string, n int) error {
	for attempt := 0; attempt < 3; attempt++ {
		var qty, version int
		err := db.QueryRowContext(ctx,
			"SELECT qty, version FROM stock WHERE sku = $1", sku).Scan(&qty, &version)
		if err != nil {
			return err
		}
		if qty < n {
			return ErrOutOfStock
		}
		res, err := db.ExecContext(ctx,
			"UPDATE stock SET qty = $1, version = version + 1 WHERE sku = $2 AND version = $3",
			qty-n, sku, version)
		if err != nil {
			return err
		}
		if rows, _ := res.RowsAffected(); rows == 1 {
			return nil // nobody changed it between our read and our write
		}
		// zero rows: another writer got there first; read again and retry
	}
	return ErrConflict
}
For this exact case the atomic `UPDATE ... SET qty = qty - $1 WHERE sku = $2 AND qty >= $1` is simpler. The loop is for when the new value needs logic that cannot go in SQL.
Common trap
  • Holding a FOR UPDATE lock while calling a payment provider or waiting for a user.
  • Optimistic locking with unlimited retries. Cap the attempts and return a clear conflict error.
  • Forgetting to check rows affected, which turns the version check into a no-op.
  • Reaching for either one when a single conditional UPDATE would do the check and the write atomically.
Say this
"Profile edits use optimistic locking with a version column, because two people rarely edit the same profile and it works across the form's two HTTP requests. The flash-sale stock is a single conditional decrement, so there is nothing to lock. The job table uses FOR UPDATE SKIP LOCKED so workers never block each other. Cost: optimistic writes can fail and the client must retry, which at this contention is about one in a hundred."
5

The follow-up an interviewer will ask about the optimistic vs pessimistic choice

"Flash sale: 10,000 people try to buy the same 100 items in one second. Optimistic or pessimistic?"

What a strong answer sounds like

Neither as the first choice. Optimistic locking would be the worst here: nearly every attempt conflicts, so each buyer retries several times and the database does many times the useful work. Pessimistic locking is correct, but every buyer queues on one row lock, holding a connection while they wait, so the connection pool empties and unrelated pages slow down. The better answer is to remove the read-then-write entirely: one conditional decrement, update stock set quantity minus one where the item matches and quantity is at least one, and look at rows affected. The database serialises those statements on the row with no application logic in between, so each takes microseconds. If that row is still too hot, I would put a queue in front so requests are admitted at a rate the row can take, or split the stock into several rows and decrement any one with stock left. Cost: the queue adds waiting for buyers, and split stock means the last few units can sit in one row while another reports none left, which needs a sweep.

6

Optimistic vs pessimistic locking: common questions

Does optimistic locking actually lock anything?

Not in the sense of making others wait. The database still locks the row briefly while executing the UPDATE, as it does for any write, but nothing is held between your read and your write. The "lock" is the version check in the WHERE clause.

What is the difference between NOWAIT and SKIP LOCKED?

With NOWAIT, a locking read fails at once with an error if a row is already locked. With SKIP LOCKED, it silently leaves locked rows out of the result. NOWAIT suits "tell the user it is busy"; SKIP LOCKED suits queues where any free row will do.

How does optimistic locking work in a REST API?

The GET response carries an ETag (often the row version). The client sends it back in an If-Match header on PUT or PATCH. If the resource changed, the server responds 412 Precondition Failed, and the client re-fetches and retries or shows the conflict.

Which is better for high concurrency?

It depends on whether the concurrent requests touch the same rows. Many requests on different rows: optimistic, since conflicts stay rare. Many requests on the same row: pessimistic locking, a queue, or ideally an atomic conditional update, because optimistic retries multiply the load.

7

Your turn

60-second self-check

A wiki lets users edit a page in a form for several minutes and then save. Which locking approach, and why not the other?

Stuck? Take one hint at a time0 of 2 used
0 / 25 characters
8

Learn it from the source

Related concepts

Transactions, isolation and lockingIsolation levelsIdempotency, and why exactly-once is a mythDistributed counters: sharded counters, write batching and HyperLogLog

All concepts on one page: system design cheat sheet