Pessimistic vs Optimistic Locking: Controlling Concurrent Access

Terminology

What & Why

The Booking Problem

Two users try to book the same hotel room at the same time. Both read the room status as "Available," both write "Booked," and both receive a confirmation. The hotel now has two confirmed bookings for one room. This is the lost update problem, and it happens whenever concurrent transactions read-then-write without coordination.

The core issue is the gap between reading and writing. During that gap, another transaction can slip in and change the data. Pessimistic and optimistic locking are two fundamentally different strategies for closing this gap.

Two Philosophies

Pessimistic locking assumes conflict is likely. It acquires a lock before reading, preventing anyone else from touching the data until the transaction completes. This is safe but blocking: other transactions wait in line.

Optimistic locking assumes conflict is rare. It reads freely, then checks at write time whether anyone else modified the data. If so, it retries. This is non-blocking but wasteful under high contention: transactions do work that gets thrown away on conflict.

How It Works

Pessimistic Locking Flow

With pessimistic locking, Client A issues a SELECT FOR UPDATE, which reads the row and acquires an exclusive lock. When Client B tries the same query, it blocks until Client A commits or rolls back. Only then does Client B proceed, now seeing the updated data.

The key property: the lock serializes access. No two transactions can hold the lock on the same row simultaneously. This eliminates the lost update problem entirely, at the cost of blocking.

Optimistic Locking with Version Columns

Optimistic locking adds a version column to the table. Every read includes the version. Every update includes a WHERE clause that checks the version has not changed. If the version changed (another transaction committed first), the UPDATE affects zero rows, and the application retries.

When a conflict occurs, the flow changes:

Row Lock Lifecycle

A row lock in a pessimistic system goes through several states. Under normal operation, it cycles between Unlocked and Locked. Under contention, waiting queues form. In pathological cases, deadlocks occur and the database must intervene.

Throughput Under Contention

The performance characteristics of pessimistic and optimistic locking diverge as contention increases. At low concurrency, optimistic locking wins because there is no blocking overhead. As the number of concurrent writers grows, optimistic locking degrades due to increasing retry rates, while pessimistic locking provides predictable (though lower) throughput by serializing access.

The crossover point (where pessimistic starts outperforming optimistic) depends on the specific workload, but it typically occurs when conflict rates exceed 10-20%. Beyond that point, the wasted work from optimistic retries exceeds the blocking cost of pessimistic locks.

Booking System Architecture

A complete booking system using pessimistic locking routes every request through a lock manager that serializes access to the contested row.

Complexity Analysis

The probability of conflict under optimistic locking depends on the number of rows $n$ being contested and the number of concurrent writers $k$. If each writer targets a uniformly random row:

For the booking problem where all $k$ writers target the same row ($n = 1$), the conflict probability is 1 for $k \geq 2$: every concurrent writer except the first will conflict.

The expected number of retries for a single writer under uniform contention is:

Throughput for pessimistic locking is bounded by the serialization cost. If each transaction holds the lock for time $T_{\text{lock}}$:

Throughput for optimistic locking under contention, accounting for retries:

Implementation

Pessimistic Locking (SELECT FOR UPDATE)

FUNCTION book_room_pessimistic(room_id, user_id):
    BEGIN TRANSACTION

    // Acquire exclusive lock on the row
    row = QUERY "SELECT status FROM rooms
                 WHERE id = room_id
                 FOR UPDATE"

    IF row.status != "Available":
        ROLLBACK
        RETURN "Room not available"

    QUERY "UPDATE rooms
           SET status = 'Booked', booked_by = user_id
           WHERE id = room_id"

    COMMIT
    RETURN "Booking confirmed"

Optimistic Locking (Version Column)

FUNCTION book_room_optimistic(room_id, user_id):
    max_retries = 5
    attempt = 0

    WHILE attempt < max_retries:
        // Read current state and version (no lock)
        row = QUERY "SELECT status, version FROM rooms
                     WHERE id = room_id"

        IF row.status != "Available":
            RETURN "Room not available"

        // Attempt conditional update
        affected = QUERY "UPDATE rooms
                          SET status = 'Booked',
                              booked_by = user_id,
                              version = row.version + 1
                          WHERE id = room_id
                            AND version = row.version"

        IF affected == 1:
            RETURN "Booking confirmed"

        // Version mismatch, someone else modified the row
        attempt = attempt + 1
        WAIT random_backoff(attempt)

    RETURN "Too many conflicts, please try again"

Deadlock Prevention (Lock Ordering)

FUNCTION transfer_funds(from_id, to_id, amount):
    // Always lock accounts in ascending ID order to prevent deadlocks
    first_id = MIN(from_id, to_id)
    second_id = MAX(from_id, to_id)

    BEGIN TRANSACTION

    first = QUERY "SELECT balance FROM accounts
                   WHERE id = first_id FOR UPDATE"
    second = QUERY "SELECT balance FROM accounts
                    WHERE id = second_id FOR UPDATE"

    IF from_id == first_id:
        from_balance = first.balance
        to_balance = second.balance
    ELSE:
        from_balance = second.balance
        to_balance = first.balance

    IF from_balance < amount:
        ROLLBACK
        RETURN "Insufficient funds"

    QUERY "UPDATE accounts SET balance = balance - amount
           WHERE id = from_id"
    QUERY "UPDATE accounts SET balance = balance + amount
           WHERE id = to_id"

    COMMIT
    RETURN "Transfer complete"

Real-World Applications

Key Takeaways