Pessimistic vs Optimistic Locks: Choosing the Right Concurrency Control for High‑Traffic Databases

Concurrency Control Overview

When multiple transactions may access the same data simultaneously, concurrency control ensures data consistency and isolation. It prevents issues such as dirty reads, non‑repeatable reads, and phantom reads. The two main approaches are pessimistic concurrency control and optimistic concurrency control.

Pessimistic Lock

Pessimistic locking assumes conflicts are likely, so it acquires an exclusive lock on a record before modification. This "lock‑then‑access" strategy guarantees that only one transaction can modify the locked row at a time, avoiding data races but incurring lock overhead and potential deadlocks.

Typical implementation in MySQL InnoDB uses SELECT ... FOR UPDATE within a transaction and requires disabling autocommit:

set autocommit=0;

Example of a stock‑deduction scenario using a pessimistic lock:

begin;
select quantity from items where id=1 for update;
update items set quantity=2 where id=1;
commit;

This ensures that only one session can lock the row with

id=1</b> at a time; other sessions wait until the transaction commits.</p>
<p>Note that MySQL’s default isolation uses row‑level locks, but if a query cannot use an index, a table‑level lock may be applied, which can greatly reduce concurrency.</p>
<h2>Optimistic Lock</h2>
<p>Optimistic locking assumes conflicts are rare. It does not acquire a database lock during the transaction. Instead, it records a version number (or timestamp) with each row and checks that the version has not changed before committing.</p>
<p>Typical implementation records a <code>version

column and updates it only when the version matches the value read earlier:

select version from items where id=1;
update items set quantity=2, version=3 where id=1 and version=2;

If the version has changed, the update fails, preventing lost updates without using explicit locks. This approach avoids deadlocks and reduces lock contention.

Optimistic Lock Implementation Details

The core steps are conflict detection and data update, often realized with a Compare‑And‑Swap (CAS) operation. CAS succeeds for only one thread when multiple threads attempt to update the same value simultaneously.

Using a version field solves the ABA problem, where a value changes from A→B→A and a CAS incorrectly assumes no change. By incrementing the version on each update, the sequence A→B→A becomes A→B→C, making the stale update detectable.

Choosing Between Pessimistic and Optimistic Locks

Optimistic lock : higher throughput, no actual lock, but higher failure rate under heavy contention; suitable when conflicts are infrequent.

Pessimistic lock : lower failure rate, but incurs lock overhead and can cause deadlocks; suitable for high‑contention scenarios where data integrity is critical.

In modern high‑concurrency, high‑performance systems, optimistic locking is often preferred, while pessimistic locking is reserved for cases with very high contention or when the cost of retrying failures is unacceptable.

Practical Optimistic‑Lock SQL Example

To safely decrement inventory in a single atomic statement:

update item set quantity = quantity - 1
where id = 1 and quantity - 1 > 0;

This statement reads the current quantity, ensures it remains positive after decrement, and updates it atomically, providing an optimistic‑lock style safeguard.

Overall, selecting the appropriate lock strategy involves evaluating conflict probability, performance requirements, and the acceptable failure‑retry cost for the specific application.