Optimistic vs Pessimistic Locking: How to Choose

When two users edit the same record, the real question is not whether to “use a lock.” It is whether conflicts are rare enough to detect at write time, or frequent and costly enough to reserve the record first.

This guide compares optimistic vs pessimistic locking after database isolation levels. Read database transactions and ACID properties for the prerequisites, then continue to database deadlocks in the Backend Interview Mastery series. The Database tag collects related SQL material.

Table of Contents

The Difference

Optimistic locking allows concurrent reads and detects a collision when writing. Pessimistic locking acquires a database lock before the decision, so a competing writer waits or fails. Neither is automatically safer: the correct choice follows conflict frequency, the cost of retrying, and the invariant being protected.

flowchart TD
    A[Read record] --> B{Conflicts rare?}
    B -->|Yes| C[Optimistic version check]
    B -->|No| D[Short pessimistic row lock]
    C --> E{Version changed?}
    E -->|Yes| F[Retry or show conflict]
    E -->|No| G[Commit]
    D --> G

Optimistic Locking With a Version Column

Store a version with the record and include it in the update predicate. Exactly one writer can update a particular version. The database does not merge values for you; it reports that the caller’s snapshot is stale.

UPDATE documents
SET body = $1, version = version + 1
WHERE document_id = $2
  AND version = $3;

If the affected-row count is zero, reload the document and either retry a safe operation or ask the user to resolve the conflict. This is a strong fit for profile edits and read-heavy records where collisions are uncommon. Do not silently overwrite the newer value. A retry is safe for a commutative change such as adding a tag; a document body edit often needs a merge UI because replaying it could erase someone else’s intent.

Pessimistic Locking With SELECT FOR UPDATE

Use a short transaction when a known row must be reserved before calculating a result. This PostgreSQL-style example locks one event, validates the result while the lock is held, writes the reservation, and then releases the lock at commit:

BEGIN;
DO $$
DECLARE
  seats_remaining integer;
BEGIN
  SELECT available_seats
  INTO seats_remaining
  FROM events
  WHERE event_id = 42
  FOR UPDATE;

  IF NOT FOUND OR seats_remaining <= 0 THEN
    RAISE EXCEPTION 'Event is missing or sold out';
  END IF;

  UPDATE events
  SET available_seats = available_seats - 1
  WHERE event_id = 42;

  INSERT INTO reservations (event_id, customer_id, status)
  VALUES (42, 9001, 'CONFIRMED');
END $$;
COMMIT;

SELECT FOR UPDATE blocks conflicting row operations on the selected PostgreSQL row until commit or rollback. The block raises an exception before the update or insert when the event is missing or sold out; its caller must issue ROLLBACK after that error. Keep the transaction small and never hold it across an HTTP call.

Lock syntax and behavior are engine-specific. PostgreSQL and MySQL support SELECT ... FOR UPDATE with different isolation and locking details; SQL Server commonly expresses the intent with table hints such as UPDLOCK and HOLDLOCK. Use the documented mode for the database you run, define a lock timeout, and acquire multiple resources in one stable order. Otherwise, locks can deadlock, which the next article covers.

For a single inventory counter, the lock may be unnecessary. An atomic conditional update can reserve a seat without a preceding read:

UPDATE events
SET available_seats = available_seats - 1
WHERE event_id = 42
  AND available_seats > 0
RETURNING available_seats;

One returned row means the caller can create the reservation in the same local transaction. No returned row means sold out or missing. This is usually the clearest option when no other decision depends on the locked row.

Decision Table: Which Approach Fits?

The table is not a substitute for a transaction boundary. If a reservation, payment intent, and inventory update must agree locally, commit them together; external side effects still need idempotency and an outbox-style recovery path.

How to Choose

Choose optimistic locking when reads dominate, conflicts are rare, and retrying is cheap. Choose pessimistic locking when contention is expected and a rejected attempt is expensive, such as a short inventory reservation. For a single counter, an atomic conditional update is often simpler than either approach. For predicates spanning several rows, evaluate Serializable isolation or a guard row rather than assuming a row lock protects the whole rule.

Measure before choosing. Track version-conflict rate for optimistic writes; track lock waits, lock timeouts, deadlock victims, and transaction duration for pessimistic paths. A conflict rate that changes after a product launch is a workload signal, not a reason to hard-code one strategy everywhere.

Interview Questions

1. Does optimistic locking prevent lost updates?

Yes, when every write checks the version and treats a zero-row update as a conflict. Without that check, two read-modify-write operations can still overwrite each other.

2. Is pessimistic locking always better for money?

No. A conditional ledger update plus constraints may be safer and shorter-lived than holding a lock while business logic runs. The design must also make client retries idempotent.

3. What is the main cost of pessimistic locking?

Waiting, deadlocks, and reduced throughput under contention. Measure lock waits and transaction duration, then reduce the locked scope before adding more infrastructure.

4. When should an optimistic-locking conflict be retried automatically?

Retry only when replaying the operation preserves the caller’s intent. A server-side increment with an idempotency key may be safe to rerun after rereading the version; a free-form document replacement usually needs a merge or a visible conflict. The retry must reread current state, rebuild the update, cap attempts, and avoid turning repeated collisions into an infinite loop.

5. Should I use NOWAIT or SKIP LOCKED for contention?

Use NOWAIT when an interactive request should fail fast instead of waiting for a lock. Use SKIP LOCKED for independent queue-like work where another worker may safely process a different row. Neither is a universal fix: skipping a row is wrong when that exact row must be reserved, and both features have engine-specific syntax and semantics. Choose a timeout and an explicit product response rather than allowing unbounded waiting.

Conclusion

Optimistic locking moves the conflict to write time; pessimistic locking moves it to read time. Choose the mechanism that makes the invariant and recovery path easiest to prove, then test two concurrent sessions against the real database configuration.

References

1. Explicit Locking - PostgreSQL Documentation https://www.postgresql.org/docs/current/explicit-locking.html
2. Transaction Locking and Row Versioning Guide - Microsoft Learn https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

YouTube Videos

1. “Optimistic vs. Pessimistic Locking” - Mahmoud Youssef https://www.youtube.com/watch?v=718XNSyf_Sw