Optimistic vs Pessimistic Locking

In multi-user systems, databases must handle concurrent reads and writes to the same records. A common issue is the lost update anomaly, which occurs during a read-modify-write pattern.

Imagine two customers try to buy the last remaining inventory item at the exact same moment.

1. Customer A reads the stock level and sees 1 item.
2. Customer B reads the stock level and also sees 1 item.
3. Customer A submits an order, decrementing the stock to 0.
4. Customer B submits an order, decrementing the stock from their read value (1) to 0.

Customer B has overwritten Customer A's update. The store sold the same item twice, but the database only shows a stock of 0. To resolve this, database engines use concurrency control mechanisms to serialize concurrent write access.

---

Pessimistic locking assumes the worst case, that conflicts are highly likely to occur. It prevents conflicts by locking target database rows before a transaction begins modifying them.

When a transaction acquires a lock on a row, any other transaction attempting to read or write that row is forced to block and wait until the locking transaction finishes and releases the lock.

In relational databases, this is typically done using the SQL statement SELECT ... FOR UPDATE.

Pessimistic Downsides

While pessimistic locking guarantees consistency, it introduces significant performance trade-offs:

• Reduced Throughput: By forcing concurrent transactions to serialize, it reduces the system's ability to utilize CPU and IO resources in parallel.
• Deadlock Risks: If Transaction A locks Row 1 and waits for Row 2, while Transaction B locks Row 2 and waits for Row 1, a deadlock occurs. The database must run detection algorithms to abort one of the transactions.
• Active Thread Blocking: Database connections are held open while waiting for locks to release, potentially exhausting connection pools.

---

Optimistic locking, or Optimistic Concurrency Control, assumes that conflicts are rare. Instead of blocking access, it allows transactions to read and modify data freely. Before committing, the transaction validates whether another transaction has modified the data since it was read.

To implement OCC, tables must track a state-indicator field: usually an incrementing version integer or a high-precision timestamp.

The execution workflow is split into three phases:

1. Read Phase: The transaction reads the target record, including its current version number (e.g. version = 1).
2. Validation Phase: The transaction attempts to update the database row. The SQL query structure dynamically checks the version number in its filtering conditions: UPDATE table SET value = new_value, version = version + 1 WHERE id = X AND version = current_version
3. Commit Phase: If the row has not changed, the version matches, the query modifies the row, and the transaction commits successfully. If another transaction has modified the row, the version in the database is now different, the update affects zero rows, and the transaction fails, prompting an application retry.

OCC Downsides

Although OCC avoids blocking and locks, it suffers under high-concurrency write workloads:

• Transaction Aborts: High conflict rates mean many writes will fail during validation.
• Retry Storms: The application must retry aborted transactions, which increases CPU and network overhead, creating a feedback loop of more conflicts.

---

The optimal strategy depends on the read-to-write ratio and the frequency of data contention:

• Use Pessimistic Locking when contention is high. If multiple transactions frequently update the same records (such as checkout operations for a hot-ticket event or inventory updates), PCC avoids the CPU and network overhead of constant transaction aborts and retries.
• Use Optimistic Locking when contention is low. In read-heavy systems with occasional updates (such as user profile settings or wiki page edits), OCC offers superior performance, higher throughput, and zero risk of deadlocks.

---

• On Optimistic Methods for Concurrency Control — The original paper introducing the concept of OCC.
• PostgreSQL Explicit Locking Documentation — Deep dive into table-level and row-level locks like FOR UPDATE.
• Concurrency Control in Distributed Systems — Explains how locking strategies translate to distributed databases.