Skip to content

Optimistic vs Pessimistic Locking

1. What is it? (2-3 lines)

Pessimistic Locking assumes concurrent conflicts will happen and proactively locks target database rows before modifying them (SELECT FOR UPDATE). Optimistic Locking assumes concurrent conflicts are rare; it avoids database locks during execution and checks for conflicting changes (via a version integer or timestamp column) at commit time.

2. Why does it exist?

High-concurrency systems must handle race conditions when two users update the same record simultaneously (Lost Update problem). Pessimistic locking prevents race conditions by forcing serial queueing at the cost of thread blocking. Optimistic locking avoids thread blocking entirely, replacing database locks with atomic conditional CAS (Compare-And-Swap) updates.

3. Where is it used?

  • Pessimistic Locking: Financial ledger balances, ticket booking / inventory reservations, low-latency queues (SELECT FOR UPDATE SKIP LOCKED).
  • Optimistic Locking: Distributed web applications, REST APIs, e-commerce cart updates, ORM entities (Hibernate @Version, JPA, DynamoDB conditional writes).

4. How does it work? (simple flow)

  • Pessimistic Flow:
  • Transaction A executes SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; (Acquires Exclusive Lock).
  • Transaction B attempts SELECT ... FOR UPDATE on id = 1 \(\rightarrow\) BLOCKED until Transaction A commits/rolls back.

  • Transaction A updates row and COMMITs \(\rightarrow\) Releases lock \(\rightarrow\) Transaction B unblocks.

  • Optimistic Flow:

  • Transaction A reads row: id=1, balance=100, version=5.
  • Transaction A modifies balance locally to 150.
  • Transaction A executes atomic update: UPDATE accounts SET balance = 150, version = 6 WHERE id = 1 AND version = 5;.
  • If affected rows \(= 1\): Success!
  • If affected rows \(= 0\) (Transaction B updated version to 6 first): Optimistic Lock Exception! Application aborts or retries read-modify-write loop.

5. Advantages

  • Pessimistic: Zero application retry logic needed; guarantees 100% update success without aborts once lock is acquired.
  • Optimistic: Zero database locks held; maximum throughput for read-heavy workloads with low write contention.

6. Limitations

  • Pessimistic: Risk of deadlocks, thread pool starvation, and long blocking queues under high QPS.
  • Optimistic: High write contention causes massive application abort rates and CPU spin-retry waste.

7. Common interview questions (10)

  1. What is the fundamental operational difference between Optimistic and Pessimistic Locking?
  2. When should an engineer choose Optimistic Locking over Pessimistic Locking?
  3. How does SELECT FOR UPDATE SKIP LOCKED implement high-throughput job queues in relational databases?
  4. How do DynamoDB Conditional Writes implement Optimistic Locking in distributed Key-Value stores?
  5. What happens to Optimistic Locking throughput under high write contention (e.g. flash sale ticket checkout)?
  6. How do deadlocks occur in Pessimistic Locking and how does the database detect them?
  7. What is the role of a version or updated_at column in Optimistic Concurrency Control (OCC)?
  8. How does Hibernate / JPA handle OptimisticLockException?
  9. How does Pessimistic Share Lock (FOR SHARE) differ from Pessimistic Exclusive Lock (FOR UPDATE)?
  10. How do long user think times (human editing a form for 5 minutes) mandate Optimistic Locking over Pessimistic Locking?

8. Common misconceptions

  • Misconception: Optimistic Locking uses database locks. Fact: Optimistic Locking uses zero database locks; it relies on atomic SQL UPDATE ... WHERE version = X comparisons.
  • Misconception: Pessimistic Locking is always slower. Fact: Under high write contention on a single row, Pessimistic Queueing is far more efficient than Optimistic RETRY loops that abort 99% of requests.

10. 30-second cheat sheet

  • Pessimistic: SELECT FOR UPDATE \(\implies\) Lock Row First. Prevents conflicts. Best for high write contention & finance.
  • Optimistic: WHERE version = 5 \(\implies\) Check Version at Commit. Zero locks. Best for low write contention & web APIs.
  • Rule of Thumb: Human think time / microservices \(\implies\) Optimistic; Bank ledger / inventory queue \(\implies\) Pessimistic.