Why Locks Matter: Pessimistic vs Optimistic Concurrency Control Explained

1. Why Need Locks?

In multi‑user environments, simultaneous updates to the same record cause conflicts, known as concurrency problems.

2. Typical Conflict Types

Lost update : one transaction overwrites another's changes (e.g., A changes 6→2, B changes 2→6, causing A’s update to be lost).

Dirty read : a transaction reads data that has been modified by another uncommitted transaction (e.g., B changes 6→2, A still reads 6).

3. Concurrency Control Mechanisms

Pessimistic lock : assumes conflicts will occur and locks data pre‑emptively to prevent integrity violations.

Optimistic lock : assumes conflicts are rare and checks for violations only at commit time; it cannot prevent dirty reads.

4. Pessimistic vs Optimistic

Pessimistic lock blocks other access until the lock is released (row lock, table lock, read/write lock, etc.).

Optimistic lock relies on version numbers or timestamps; suitable for read‑heavy workloads, improving throughput.

Neither approach is universally superior; choose based on conflict frequency and performance requirements.

5. Optimistic Lock Applications

1) Use an auto‑increment integer as a version field; update only if the version matches.

2) Use a timestamp.

Hibernate supports this via the @Version annotation.

6. Pessimistic Lock Applications

Use database‑level locks, e.g., SQL Server TABLOCKX to acquire an exclusive table lock for the duration of a transaction.

7. Classic Case Study

A financial system where two operators read the same account balance and attempt to deduct amounts. With optimistic locking, each read includes a version number; the second operator’s commit fails because the version has been incremented by the first operator, preventing lost updates.

8. Summary

Optimistic locking avoids the overhead of long‑running database locks, improving performance under high concurrency, but it relies on application‑level version control and may not protect against external updates, requiring careful design.