Understanding Concurrency Issues: Pessimistic and Optimistic Locks in Databases

When concurrency problems appear in a program, mechanisms are required to guarantee data correctness, avoid dirty data, and prevent phenomena such as dirty reads, phantom reads, and non‑repeatable reads.

Pessimistic Lock

Pessimistic locking assumes that data will be contested; therefore a transaction locks the target row before modification, blocking other threads until the lock is released. This ensures data integrity but can reduce throughput, making it suitable for write‑heavy scenarios.

Example using MySQL InnoDB: set autocommit=0 Typical inventory‑deduction transaction:

// start transaction
begin;
select quantity from goods where id=1 for update;
update goods set quantity=2 where id=1;
commit;

The SELECT ... FOR UPDATE statement acquires an exclusive row lock; only one transaction can modify the row at a time, preventing concurrent updates from corrupting the data.

Optimistic Lock

Optimistic locking assumes conflicts are rare. It does not acquire database locks; instead it records a version number (or uses CAS) and checks the version at commit time. If the version has changed, the update fails, allowing the application to retry.

Typical implementation with a version column:

Both friends A and B read the balance (0) and the current version (0).

Friend A updates the balance to 1000 and increments the version to 1; the update succeeds.

Friend B attempts the same update, but the version check fails because the stored version is now 1, so the transaction is aborted and must be retried.

Optimistic locking relies on version comparison rather than database‑provided locks; if the version matches, the update proceeds, otherwise the data is considered stale.

CAS (Compare‑And‑Swap) is a common technique for implementing optimistic locking at the application level, allowing multiple threads to attempt updates concurrently while only one succeeds.

Both locking strategies have trade‑offs: pessimistic locks guarantee consistency at the cost of throughput, while optimistic locks improve throughput for read‑dominant workloads but require conflict handling logic.