Optimistic vs Pessimistic Locking: Detect-at-Commit vs Lock-Before-Access
Overview Both are concurrency-control strategies for preventing lost updates when multiple transactions touch the same data, but they differ in when they deal with conflict. Optimistic locking assumes collisions are rare and only performs a version check at commit time, while pessimistic locking assumes collisions are likely and takes an exclusive lock before any read or write proceeds. Comparison Diagram Optimistic Pessimistic Record (v1) no lock taken Txn A Txn B v1 → v2 committed v1 ≠ v2 conflict, retry conflict caught at commit time Record blocked Txn A holds lock Txn B waiting commits & releases lock acquires lock then runs conflict prevented up front Comparison Table Aspect Optimistic Locking Pessimistic Locking Access phase No lock taken; any transaction can read or begin writing the row immediately Lock acquired (e.g. SELECT FOR UPDATE) before the transaction reads or writes the row Concurrent access Other transactions freely read and write the same row in parallel Other transactions attempting the same row must wait for the lock holder to finish Conflict detection timing Deferred until commit, via a version number, timestamp, or hash comparison Not needed as a separate step; the lock physically prevents overlapping access On conflict Commit is rejected; the transaction is rolled back and typically retried No conflict occurs; the waiting transaction simply blocks until the lock is released Implementation mechanism Application-level version column checked in the UPDATE’s WHERE clause Database-level row or table locks managed by the lock manager Throughput under low contention High; no blocking overhead when collisions are rare Lower; locking overhead is paid even when no real conflict would occur Behavior under high contention Retry storms and wasted work as many transactions repeatedly fail and re-run Orderly queueing keeps correctness but serializes work and limits parallelism Deadlock risk None, since no locks are ever held Possible when transactions acquire multiple locks in inconsistent order Key Differences Optimistic locking detects conflicts at commit time; pessimistic locking prevents them via upfront locking. Optimistic locking never blocks other transactions, it only forces a retry on collision. Pessimistic locking holds an exclusive lock for the transaction’s full duration, serializing access to the row. Optimistic locking carries no deadlock risk because it never holds locks. Pessimistic locking trades raw throughput for predictability when contention is heavy. When to Use Each Optimistic Locking ...