Optimistic vs Pessimistic Locking: Handling Race Conditions in High-Contention Databases

Imagine an inventory table for an e-commerce flash sale:

Item: Nintendo Switch (Stock: 1)

Two customers (Alice and Bob) click "Buy Now" at the exact same millisecond.
Both requests execute:

SELECT stock FROM items WHERE id = 101; -- Both read: 1
-- Both check in application: stock > 0 (True!)
UPDATE items SET stock = 0 WHERE id = 101;

Both customers receive an order confirmation, but only one physical item exists in the warehouse. This is the classic Lost Update Problem.

To prevent concurrent race conditions, relational databases offer two primary concurrency control patterns: Pessimistic Locking and Optimistic Locking.

Strategy 1: Pessimistic Locking (SELECT FOR UPDATE)

Pessimistic locking assumes conflict will happen. It prevents concurrent access by locking the row immediately upon reading.

BEGIN;

SELECT id, stock 
FROM items 
WHERE id = 101 
FOR UPDATE;

UPDATE items 
SET stock = stock - 1 
WHERE id = 101;

COMMIT;

Tradeoff:

• Pros: Guarantees absolute consistency.
• Cons: If 500 users attempt to purchase simultaneously, 499 threads are blocked waiting for database locks.

Strategy 2: Optimistic Locking (Version Numbers)

Optimistic locking assumes conflict is rare. It does not lock the database row at all during read operations.

Instead, a version column is added to the table:

CREATE TABLE items (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    stock INT NOT NULL,
    version INT NOT NULL DEFAULT 1
);

The Execution Flow:

1. Read:

   SELECT id, stock, version FROM items WHERE id = 101;
   -- Returns: stock=1, version=4

1. Mutate:

   UPDATE items 
   SET stock = stock - 1, 
       version = version + 1 
   WHERE id = 101 AND version = 4;

1. Inspect Affected Rows:
   • If affected rows == 1: The update succeeded cleanly!
   • If affected rows == 0: Another transaction modified the row first! Roll back and retry in application code.

Python Implementation with Retry Loop

import time
from sqlalchemy.orm import Session
from sqlalchemy import text

def purchase_item_optimistic(session: Session, item_id: int, max_retries: int = 3):
    for attempt in range(max_retries):
        row = session.execute(
            text("SELECT id, stock, version FROM items WHERE id = :id"),
            {"id": item_id}
        ).fetchone()

        if not row or row.stock <= 0:
            raise ValueError("Item out of stock!")

        result = session.execute(
            text("""
                UPDATE items 
                SET stock = stock - 1, version = version + 1
                WHERE id = :id AND version = :version
            """),
            {"id": item_id, "version": row.version}
        )
        session.commit()

        if result.rowcount == 1:
            print(f"Successfully purchased item on attempt #{attempt + 1}")
            return True

        time.sleep(0.05 * (2 ** attempt))

    raise RuntimeError("Failed to complete purchase due to concurrent contention")

When to Choose Which Strategy

• High Contention (Flash sales, auction bidding): Pessimistic Locking (FOR UPDATE).
• Low/Moderate Contention (User profile updates, CMS edits): Optimistic Locking (Version Column).