Optimistic vs Pessimistic Locking: A Deep Dive into Safe Concurrent Updates in Backend Systems

Introduction: Concurrency Is the Default, Not the Exception

---

In modern backend systems, concurrency is unavoidable.

Requests do not arrive one by one. They arrive:

• In parallel

• From different users

• From background jobs

• From distributed services

Any system that allows multiple requests to read and write the same data must deal with concurrency explicitly.
If it does not, data corruption becomes inevitable.

One of the most common and dangerous concurrency issues is the Lost Update Problem, where one update silently overwrites another.

To address this, backend systems rely on locking strategies.
Two strategies dominate real-world systems:

• Optimistic Locking

• Pessimistic Locking

---

1. Understanding the Lost Update Problem in Detail

A Simple Data Model

Product
------------------
id        = 101
price     = 100

Timeline of Events

T1: User A reads price = 100
T2: User B reads price = 100

T3: User A updates price to 120
T4: User B updates price to 90

Final Result

price = 90

User A’s update is completely lost.

This happens because:

• Both users worked with stale data

• Updates were not coordinated

• The database had no reason to reject either update

The database did exactly what it was asked to do.

---

2. Why Databases Do Not Solve This Automatically

Databases guarantee atomicity for individual statements, not correctness across concurrent business operations.

From the database’s perspective:

• Each update was valid

• Each update was consistent

• Each update was committed successfully

Concurrency correctness is a system-level concern, not just a database concern.

This is why explicit locking strategies exist.

---

3. What Locking Really Means

Locking is not about stopping concurrency.
It is about controlling access to shared state.

A locking strategy defines:

• When data can be read

• When data can be written

• Who must wait

• Who can proceed

At a high level, all locking strategies answer this question:

How do we ensure that concurrent operations do not produce incorrect results?

There are two fundamentally different answers:

1. Prevent conflicts before they occur

2. Detect conflicts when they occur

---

4. Pessimistic Locking: Prevent Conflicts Early

Conceptual Model

Pessimistic locking assumes that:

• Conflicts are common

• Concurrent writes are dangerous

• Blocking is acceptable to ensure correctness

Once data is locked:

• No other transaction can modify it

• Other transactions must wait

---

Internal Flow of Pessimistic Locking

BEGIN TRANSACTION
↓
Acquire row lock
↓
Read data
↓
Modify data
↓
Commit transaction
↓
Release lock

---

SQL Example with Row-Level Locking

BEGIN;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

Important details:

• FOR UPDATE acquires an exclusive lock

• Lock is held until commit or rollback

• Other updates on the same row are blocked

---

Go Implementation (Pessimistic Locking)

func deductBalance(db *sql.DB, accountID int, amount int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    var balance int
    err = tx.QueryRow(
        "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
        accountID,
    ).Scan(&balance)
    if err != nil {
        return err
    }

    if balance < amount {
        return errors.New("insufficient balance")
    }

    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
        amount, accountID,
    )
    if err != nil {
        return err
    }

    return tx.Commit()
}

---

Properties of Pessimistic Locking

• Guarantees exclusive access

• Ensures strong consistency

• Eliminates lost updates entirely

However:

• Threads block

• Throughput decreases

• Deadlocks are possible

• Long-running transactions are dangerous

---

5. Optimistic Locking: Detect Conflicts Late

Conceptual Model

Optimistic locking assumes that:

• Conflicts are rare

• Blocking is expensive

• It is better to detect conflicts than prevent them

No locks are taken during reads.

Instead:

• Data carries a version

• Updates verify that the version is unchanged

---

Typical Schema with Version Column

Product
-------------------------
id        INT
price     INT
version   INT

---

Optimistic Locking Update Flow

Read product (version = 3)
↓
Client modifies data
↓
Update where version = 3
↓
If version changed → conflict

---

SQL Example

UPDATE product
SET price = 120,
    version = version + 1
WHERE id = 101
  AND version = 3;

• If one row is updated → success

• If zero rows are updated → conflict detected

---

Go Implementation (Optimistic Locking)

func updateProduct(db *sql.DB, id int, price int, version int) error {
    result, err := db.Exec(
        `UPDATE product
         SET price = $1, version = version + 1
         WHERE id = $2 AND version = $3`,
        price, id, version,
    )
    if err != nil {
        return err
    }

    rows, err := result.RowsAffected()
    if err != nil {
        return err
    }

    if rows == 0 {
        return errors.New("concurrent modification detected")
    }

    return nil
}

---

6. What Happens When Two Updates Occur at the Same Time

Optimistic Locking Case

User A reads version = 5
User B reads version = 5

User A updates → version becomes 6
User B updates → WHERE version = 5 → no rows updated

Outcome:

• One update succeeds

• One update fails safely

• No data corruption occurs

---

Backend Conflict Handling Options

• Reject update and ask for retry

• Automatically retry with fresh data

• Abort operation gracefully

The important point:
Failure is intentional and controlled.

---

7. System Design Perspective

Optimistic Locking Architecture

Client
  |
  | GET resource
  |
API Service
  |
  | SELECT (no lock)
  |
Database
  |
  | return data + version
  |
Client modifies data
  |
  | PUT resource (with version)
  |
Database
  |
  | success OR conflict

---

Pessimistic Locking Architecture

Client
  |
  | Write request
  |
API Service
  |
  | BEGIN TRANSACTION
  | SELECT ... FOR UPDATE
  |
Database
  |
  | row locked
  |
API Service
  |
  | UPDATE
  | COMMIT

---

8. Comparison Summary

---

9. Common Design Mistakes

• Using pessimistic locking for all operations

• Ignoring conflict handling in optimistic locking

• Holding transactions open too long

• Mixing both strategies without clear boundaries

---

10. Key Takeaways

• Concurrent updates are unavoidable

• Lost updates are a system design problem

• Locking is a trade-off between performance and safety

• Optimistic locking scales better

• Pessimistic locking offers stronger guarantees

A well-designed backend system uses both, where appropriate.

---

Reference Material

Books

• Designing Data-Intensive Applications — Martin Kleppmann

• Database Internals — Alex Petrov

• Distributed Systems — Maarten van Steen

Articles and Documentation

• PostgreSQL Documentation: Concurrency Control

• MySQL InnoDB Locking and Transactions

• Martin Fowler: Patterns of Enterprise Application Architecture

---

Thank you for taking the time to read this article.

If you have an interesting backend topic to share or would like to discuss system design ideas, feel free to drop me an email at:

harshptct@gmail.com

---