Esc
↑↓ navigate   Enter open   Esc close

Database concurrency comparison

Optimistic vs Pessimistic Locking

Two strategies for handling concurrent writes to the same database row. The choice affects throughput, deadlock risk, and complexity — and it comes up frequently in backend code reviews and system design interviews.

TL;DR

  • Optimistic locking — no lock held while you work. On commit, check if the version field matches; if not, someone else got there first — retry. Great for low-conflict scenarios. No deadlocks.
  • Pessimistic locking — lock the row immediately on read (SELECT FOR UPDATE). Nobody else can modify it until you release. Guarantees safety but risks deadlocks and reduces concurrency.
  • Default: optimistic. Use pessimistic only when conflicts are truly frequent or when a retry is unacceptable (financial transactions, limited inventory).

Side-by-side comparison

AspectOptimistic LockingPessimistic Locking
Lock heldNo — check on commitYes — row locked on read
ThroughputHigh when conflicts rareLower — readers queue behind writer
Deadlock riskNonePossible — lock ordering matters
Conflict handlingRetry or surface error to userBlocked until lock released
ImplementationVersion column + conditional UPDATESELECT ... FOR UPDATE in transaction
Best conflict rateLow — few writers on same rowHigh — many writers on same row
Use caseUser profile edits, CMS articles, e-commerce catalogueBank transfers, seat/ticket booking, inventory deduction
ORM support@Version (JPA), lock_version (Rails), @VersionColumn (TypeORM)with_for_update() (SQLAlchemy), .lock() (Rails), SKIP LOCKED

Code side-by-side

Updating a product stock count safely:

Optimistic locking (SQL)

-- Read the row and remember version
SELECT id, stock, version
  FROM products WHERE id = 7;
-- → { stock: 10, version: 3 }

-- Business logic: reserve 1 item

-- Write back only if version matches
UPDATE products
  SET stock = 9, version = 4
  WHERE id = 7 AND version = 3;
-- Affected rows = 0? → retry!
-- Affected rows = 1? → success

Pessimistic locking (SQL)

BEGIN;

-- Lock the row immediately
SELECT id, stock
  FROM products
  WHERE id = 7
  FOR UPDATE;
-- Other transactions block here ↑

-- Business logic: reserve 1 item

UPDATE products
  SET stock = stock - 1
  WHERE id = 7;

COMMIT;  -- Lock released

When to use Optimistic Locking

  • User-facing edit forms. Two users editing their own profiles rarely collide. Version check catches the rare case without blocking everyone.
  • CMS and document editing. Conflicts are uncommon; a "someone else saved this document" message is acceptable.
  • High-read, low-write data. Product catalogue, configuration records — reads vastly outnumber writes, so holding locks would be wasteful.
  • Microservices across network boundaries. You cannot hold a database lock across an HTTP call; optimistic locking with retry is the right model.

When to use Pessimistic Locking

  • Last-item inventory. When only one seat or ticket remains, optimistic retries from many concurrent buyers will all fail except one — pessimistic locking queues them cleanly.
  • Financial debit/credit. Retrying a payment deduction risks double-charging if not carefully idempotent. Pessimistic locking prevents the race entirely.
  • Short, fast transactions. Pessimistic locking is safer when the critical section is tiny (milliseconds) — the lock is held briefly and the queuing cost is low.
  • Background jobs consuming a queue. SKIP LOCKED in Postgres lets workers grab the next unclaimed job row without contention.

English phrases engineers use

Optimistic locking conversations

  • "We use a version column to detect concurrent edits."
  • "If the UPDATE affects zero rows, we got a conflict — retry."
  • "This is an optimistic check-and-set pattern."
  • "The ORM throws an OptimisticLockException — catch and retry."
  • "Conflicts are rare here — optimistic is the right trade-off."

Pessimistic locking conversations

  • "We SELECT FOR UPDATE to lock the row before reading the balance."
  • "The second transaction is blocked waiting for the first to commit."
  • "We got a deadlock — transactions were locking rows in different orders."
  • "Use SKIP LOCKED so workers don't queue on the same job row."
  • "Keep the transaction short — holding a lock for too long causes queuing."

Quick decision tree

  • Low conflict rate, user-facing forms → Optimistic
  • High conflict rate, limited resource (last ticket) → Pessimistic
  • Cannot afford a retry (payment) → Pessimistic
  • Long business logic between read and write → Optimistic (never hold a lock that long)
  • Background job queue (claim next item) → Pessimistic + SKIP LOCKED
  • Microservice calling external API between read and write → Optimistic
  • Many concurrent readers, rare writes → Optimistic

Frequently asked questions

What is optimistic locking?

Optimistic locking assumes conflicts are rare. You read a row, remember its version number (or timestamp), do your business logic, then write back only if the version has not changed. If it has changed, another transaction beat you — you retry or surface an error. No lock is held while you think.

What is pessimistic locking?

Pessimistic locking assumes conflicts are likely. You lock the row (SELECT ... FOR UPDATE) the moment you read it, blocking any other transaction from modifying it until you commit or roll back. Safe but can cause queuing and deadlocks under load.

What is a version field and how does it work?

A version field (often called version or updated_at) is a column you increment on every update. When you read a row you note the current version. When you write, your UPDATE includes WHERE version = <what you read>. If 0 rows are affected, someone else changed it first — you lost the race and must retry.

Related comparisons & resources