Which strategy has higher throughput?

Optimistic locking typically has higher throughput when conflicts are rare, because threads never block waiting for a lock. However, if the conflict rate is high (many writers on the same row), you waste CPU on retries and throughput can drop below pessimistic locking.

Can optimistic locking lead to deadlocks?

No. Optimistic locking never holds locks, so deadlock is impossible. Pessimistic locking can deadlock when two transactions lock rows in different orders and each waits for the other.

Which ORMs support optimistic locking out of the box?

Most major ORMs do. Hibernate/JPA (@Version annotation), ActiveRecord (lock_version column), Django (no built-in, but easy to implement with F expressions), TypeORM (@VersionColumn decorator), and SQLAlchemy (version_id_col). Pessimistic locking is usually exposed via with_for_update() or similar query methods.

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

Aspect	Optimistic Locking	Pessimistic Locking
Lock held	No — check on commit	Yes — row locked on read
Throughput	High when conflicts rare	Lower — readers queue behind writer
Deadlock risk	None	Possible — lock ordering matters
Conflict handling	Retry or surface error to user	Blocked until lock released
Implementation	Version column + conditional UPDATE	SELECT ... FOR UPDATE in transaction
Best conflict rate	Low — few writers on same row	High — many writers on same row
Use case	User profile edits, CMS articles, e-commerce catalogue	Bank 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

Common Mistakes and Trade-offs

A remarkably frequent mistake teams make when implementing optimistic locking is assuming that simply adding an `UPDATE` statement with a unique constraint automatically solves concurrency issues. This ignores the core problem: what happens if another transaction also attempts to update the same record concurrently? The database handles this by returning a conflict error, but without carefully designed retry logic and idempotent operations, you'll end up in a vicious cycle of failing transactions, increasing latency, and potentially degrading performance significantly. It's not enough to just catch the error; the application needs to intelligently handle it – usually by rolling back the current transaction and attempting another update with new data. Failing to do so creates a brittle system vulnerable to even minor load spikes.

At scale, pessimistic locking can introduce a significant performance bottleneck. While it guarantees exclusive access, the overhead of acquiring locks—especially on highly contended resources—can dramatically slow down operations. This isn't always immediately apparent in smaller systems, but as data volume and transaction rates increase, the constant acquisition and release of locks contribute to substantial queuing delays. The database itself becomes a choke point, leading to increased latency for all users. Optimistic locking avoids this by minimizing lock contention, but it relies on efficient conflict detection which can also introduce some overhead if not implemented correctly – often requiring frequent checks within transaction logic.

There's a pervasive misconception that optimistic locking is 'always better' than pessimistic locking, particularly in scenarios with high concurrency or complex transactions. This is demonstrably false. Optimistic locking shines when the probability of conflicts is low—typically in systems where updates are relatively infrequent and data modifications are isolated to small portions of records. Conversely, pessimistic locking excels in situations demanding absolute data integrity, such as financial transactions or inventory management, where even a single conflicting update can have serious consequences. The assumption that one approach universally outperforms the other ignores the specific characteristics of the application and its workload.

Migrating between optimistic and pessimistic locking isn't a simple switch; it requires careful consideration of the existing codebase and data model. A direct conversion often introduces significant refactoring, particularly if the original system relied heavily on pessimistic locking for its transactional guarantees. Furthermore, combining optimistic and pessimistic locking – using pessimistic locks for critical sections and optimistic locking elsewhere – can be effective but necessitates extremely disciplined design to avoid inadvertently introducing conflicts. The key is a deep understanding of both approaches and how they interact within the overall application architecture; blindly applying one over the other rarely results in optimal performance or reliability.

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.

Show more questions (3)