Optimistic vs pessimistic locking explained

The lost update problem in one transaction

Consider a product with stock = 10. Request A reads 10, subtracts 1, writes 9. Request B reads 10 (before A commits), subtracts 1, writes 9. Two units sold; stock shows 9 instead of 8. The bug is invisible unless you reconcile against order logs.

Fixing it requires atomicity of the read-modify-write. You can either prevent B from reading until A finishes (pessimistic), or let B read but reject its write if the row changed (optimistic). Both approaches assume your application runs inside a database transaction with appropriate isolation — default Read Committed in PostgreSQL still allows lost updates unless you add explicit locking or versioning.

Pessimistic locking: lock first, write second

Pessimistic locking assumes conflict is likely. Before modifying a row, the transaction acquires an exclusive lock so no other transaction can read it for update or write it until the lock holder commits or rolls back.

SELECT FOR UPDATE

In SQL databases, the standard pattern is SELECT ... FOR UPDATE (PostgreSQL, MySQL InnoDB) or SELECT ... WITH (UPDLOCK, ROWLOCK) (SQL Server). The lock is held for the duration of the transaction. Other transactions block on the same row until the lock releases — queuing writers in arrival order (usually).

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- stock is now locked; other FOR UPDATE on this row waits
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT; -- lock released

When pessimistic locking shines

• High contention on hot rows — flash-sale inventory, wallet balances, ticket pools where many writers target the same record.
• Short transactions — lock duration is milliseconds; blocking cost stays low.
• Must succeed on first try — payment capture, legal audit trails where silent retry loops are unacceptable UX.

Costs and failure modes

Pessimistic locks reduce throughput under contention: every blocked transaction holds a connection from the pool. Long transactions amplify the problem — a lock held during an external API call can stall dozens of peers. Worse, two transactions locking rows in opposite order create deadlocks; the database kills one transaction and the application must retry. Always lock rows in a consistent order (e.g. sort by primary key) when updating multiple records in one transaction.

Optimistic locking: detect conflict at commit

Optimistic locking assumes conflict is rare. Readers do not block writers; instead, each row carries a version token — an integer column, a timestamp, or a hash of the row — that must match at update time.

Version column pattern

-- schema: products(id, stock, version)

BEGIN;
SELECT stock, version FROM products WHERE id = 42;
-- application computes new_stock = stock - 1

UPDATE products
SET stock = 9, version = version + 1
WHERE id = 42 AND version = 5;

-- if rowCount = 0, someone else committed first → conflict
COMMIT;

The WHERE version = 5 clause is a compare-and-swap: the update succeeds only if no other transaction incremented the version since you read it. ORMs expose this as @Version (JPA/Hibernate), lock_version (Rails ActiveRecord), or rowVersion (Entity Framework).

HTTP ETags and conditional updates

REST APIs use the same idea without a dedicated column: return an ETag header on GET, require If-Match: "etag-value" on PUT/PATCH. A mismatch returns 412 Precondition Failed. This is optimistic concurrency for clients that may not share your database — common in REST API design for document stores and CMS backends.

When optimistic locking shines

• Low contention — profile edits, CMS pages, settings where simultaneous writes to the same row are uncommon.
• Long read phases — user fills a form for minutes; holding a row lock that whole time is impossible.
• Read-heavy workloads — no lock overhead on SELECT; writers pay only on conflict.

Handling conflicts

Optimistic failure is not an error in the database sense — it is an application signal. Standard responses: retry the whole read-modify-write (with exponential backoff under load), merge changes (last-write-wins or field-level merge), or return 409 Conflict to the client and let the user reconcile. Under high contention, optimistic retries can thrash — many failed UPDATEs and wasted CPU — which is when pessimistic locking wins.

How MVCC interacts with both strategies

Modern databases (PostgreSQL, MySQL InnoDB, SQL Server) use multi-version concurrency control (MVCC): readers see a snapshot and do not block writers, and vice versa, for plain SELECTs. Pessimistic FOR UPDATE opts out of that freedom — it takes a real row-level lock. Optimistic locking stays MVCC-friendly for reads but relies on the atomic UPDATE predicate to detect write-write conflicts.

Serializable isolation can detect some anomalies automatically, but it is expensive and may abort transactions you did not expect. Explicit optimistic or pessimistic locking gives predictable, application-level semantics without paying for full serializable scans on every query.

Pessimistic vs optimistic vs distributed locks

Row locks work inside one database. When multiple services compete for a resource that is not a single row — a cron job, a file export slot, cross-table invariants — you may need a distributed lock (Redis, etcd, PostgreSQL advisory locks). Distributed locks are always pessimistic in spirit: one holder at a time.

Optimistic concurrency can span services too: store a version in the database and pass it through an event pipeline; consumers reject stale events. Pair with idempotency keys so retried requests do not double-charge when a conflict retry replays the same logical operation.

Common mistakes

Locking outside a transaction

SELECT FOR UPDATE in autocommit mode releases the lock immediately after the statement — before your UPDATE runs. Always wrap lock + modify in an explicit BEGIN ... COMMIT.

Optimistic update without checking row count

ORMs that silently ignore WHERE version = ? mismatches produce zero-row updates with no error. Verify affected row count equals 1 and branch on conflict.

Long-held pessimistic locks during I/O

Never call external APIs, send email, or render PDFs while holding FOR UPDATE. Read and lock only at the last possible moment, or switch to optimistic locking with a short final transaction.

Using optimistic locking on hot counters

A global view counter with 1,000 writes per second will retry constantly. Use atomic UPDATE SET count = count + 1 (no version check) or a sharded counter pattern instead.

Strategy decision table

Implementation checklist

• Identify every read-modify-write path in your codebase — those are lock candidates.
• Default to optimistic locking for low-contention entities; add a version column in migrations.
• Use pessimistic locks only inside short transactions; never across network I/O.
• When locking multiple rows, always acquire locks in a consistent sort order.
• On optimistic conflict, retry with backoff or return 409 — never silently discard user input.
• Verify ORM update row counts; zero rows updated means conflict.
• Expose ETags on GET for public APIs that support conditional updates.
• Load-test hot rows under expected peak; switch strategy if optimistic retry rate exceeds ~5%.
• Log lock wait time and deadlock events in metrics to catch regressions early.
• Document which entities use which strategy so the next engineer does not mix patterns.

Key takeaways

• Pessimistic locking blocks writers early with row locks — best for hot rows and short transactions.
• Optimistic locking detects conflicts at commit via version checks — best when contention is rare.
• Both solve the lost update problem; neither replaces proper transaction boundaries.
• High retry rates signal you picked the wrong strategy for the contention level.
• Cross-service coordination needs distributed locks or idempotency, not just a version column.

Related reading

• Database transactions and isolation levels explained — ACID, MVCC, deadlocks, and when Serializable is overkill
• Distributed locking explained — leases, Redis, fencing tokens, and cross-service exclusivity
• Idempotency explained — safe retries when optimistic conflicts replay the same request
• Exponential backoff and retry patterns explained — backoff schedules for conflict retries under load