Types of Locks Explained: Optimistic vs. Pessimistic Locking

Scenario context

Pessimistic locking locks the record before editing — safe but slower under low contention. Optimistic locking checks for changes before saving using a version number — fast but can fail and require retry under high contention. Choosing correctly depends on your write-conflict probability.

---

Imagine a Shared Google Doc. Two people open it simultaneously and both start typing.

• Pessimistic approach: Onl…

This walkthrough focuses on Two Ways to Prevent the Concurrent Edit Problem.

Before making a design choice, clarify:

• Two people open it simultaneously and both start typing.
• • Pessimistic approach: Only one person can type at a time.
• The doc is "locked" for everyone else until the first person finishes.

Concepts in play: transaction-isolation-distributed-acid, caching, jvm-internals-and-garbage-collection, consistency-replication

Stage 1 — Locking Fundamentals: Concurrency and Write Conflicts

• Before picking a locking strategy, it helps to understand exactly what can go wrong without one.
• When two transactions read and write the same row at overlapping times, the database can produce incorrect results — silently.
• These are called concurrency anomalies .
• Locking prevents these anomalies by serialising access to shared data.

Observable outcome: Locking prevents these anomalies by serialising access to shared data.

Stage 2 — Pessimistic Locking: SELECT ... FOR UPDATE

• Pessimistic locking acquires an exclusive database lock on a row before any modification.
• While Transaction A holds the lock, Transaction B's SELECT ...
• FOR UPDATE on the same row blocks until A commits or rolls back.
• 📊 Lock State Machine Lock types: Deadlock risk: Transaction A holds Row 1, wants Row 2.

-- Transaction A: lock the row
BEGIN TRANSACTION;
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;

-- Now safely modify
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT TRANSACTION;

sequenceDiagram
    participant A as Transaction A
    participant B as Transaction B
    participant DB as Database

    A->>DB: BEGIN + SELECT FOR UPDATE (product 42)
    DB-->>A: Row locked
    B->>DB: SELECT FOR UPDATE (product 42)
    DB-->>B: Waiting for lock...
    A->>DB: UPDATE + COMMIT
    DB-->>B: Lock acquired
    B->>DB: UPDATE + COMMIT

Observable outcome: 📊 Lock State Machine Lock types: Deadlock risk: Transaction A holds Row 1, wants Row 2.

Stage 3 — Optimistic Locking: Version Column Pattern

• Optimistic locking adds a version column.
• No lock is held during the read — only at write time does the system check whether anyone else modified the row first.
• In Java with JPA: JPA throws OptimisticLockException when the version check fails — the calling service must catch it and retry.

-- Step 1: Read with version
SELECT id, balance, version FROM accounts WHERE id = 1;
-- Returns: { id: 1, balance: 1000, version: 3 }

-- Step 2: Update with version check
UPDATE accounts
SET balance = 900, version = 4         -- increment version
WHERE id = 1 AND version = 3;          -- only if version unchanged

-- Step 3: Check affected rows
-- 1 row affected = success
-- 0 rows affected = conflict → retry

@Entity
public class Account {
    @Id Long id;
    double balance;

    @Version          // JPA manages optimistic locking automatically
    int version;
}

Observable outcome: In Java with JPA: JPA throws OptimisticLockException when the version check fails — the calling service must catch it and retry.

Stage 4 — Deep Dive: How Database Locks Are Implemented

• Databases implement pessimistic locks via a lock manager : an in-memory hash table keyed by (table, row) that tracks which transaction holds each lock and which are waiting.
• The lock manager periodically scans wait queues for cycles to detect deadlocks, then rolls back one transaction as the victim.
• Optimistic locking needs no lock manager — the version check happens inside the UPDATE statement itself, handled entirely by the storage engine at commit time.

Observable outcome: Optimistic locking needs no lock manager — the version check happens inside the UPDATE statement itself, handled entirely by the storage engine at commit time.

Stage 5 — Choosing Your Lock Strategy

• The decision between pessimistic and optimistic locking is primarily a question of conflict frequency .
• Use the flowchart below as a quick decision guide, then validate against your observed write-conflict rate in production.
• 📊 Lock Type Selection Decision Tree If your write-conflict rate sits between 5 % and 10 %, measure under realistic load before deciding.
• Optimistic locking's retry overhead compounds quickly when conflicts are frequent, so a brief load test is worth the effort before committing to a strategy.

flowchart TD
    A[Write operation needed] --> B{How frequent are write conflicts?}
    B -->|High - more than 10 percent| C[Use Pessimistic Locking]
    B -->|Low - less than 5 percent| D[Use Optimistic Locking]
    C --> E{Single row or multiple rows?}
    E -->|Single row| F[SELECT FOR UPDATE]
    E -->|Multiple rows| G[Lock in consistent order to prevent deadlock]
    D --> H[Add version column to table]
    H --> I{Conflict detected on save?}
    I -->|Yes| J[Throw OptimisticLockException - retry]
    I -->|No| K[Commit successfully]

flowchart TD
    A[Choose a lock strategy] --> B{Concurrent reads frequent?}
    B -- Yes --> C{Writes also frequent?}
    C -- No --> D[ReadWriteLock shared reads / exclusive writes]
    C -- Yes --> E[Pessimistic row lock]
    B -- No --> F{Single-threaded access?}
    F -- Yes --> G[No lock needed]
    F -- No --> H{Low conflict probability?}
    H -- Yes --> I[Optimistic MVCC version column]
    H -- No --> J[Pessimistic lock SELECT FOR UPDATE]

Observable outcome: Optimistic locking's retry overhead compounds quickly when conflicts are frequent, so a brief load test is worth the effort before committing to a strategy.

Stage 6 — Real-World Applications: Choosing the Right Strategy for Real Systems

Observable outcome: You can explain how “Real-World Applications: Choosing the Right Strategy for Real Systems” changes the architecture or implementation path.

End-to-end outcome

After working through the stages, you should be able to explain:

• Choosing the wrong lock strategy leads to either lost updates (under-locking) or throughput bottlenecks (over-locking).
• Keep these four rules of thumb handy: 1.

Alternatives considered

Decision Guide: Pessimistic vs. Optimistic

Decision Guide: Pessimistic vs. Optimistic

Failure and degraded modes

Trade-offs & Failure Modes: Trade-offs and Failure Modes

• Detection: The probability of lock contention under random access can be modeled as: $$P( ext{conflict}) pprox 1 - e^{-\lambda t}$$ where $\lambda$ is the arrival rate of writers and $t$ is the average transac…
• User or operator impact: The probability of lock contention under random access can be modeled as: $$P( ext{conflict}) pprox 1 - e^{-\lambda t}$$ where $\lambda$ is the arrival rate of writers and $t$ is the average transac…