Optimistic and Pessimistic Locking: A Practical Guide to Preventing Lost Updates

Lost updates happen when two transactions read the same row, both modify it, and the second write silently overwrites the first. The result is corrupted data, missing increments, or orders that disappear. Most teams discover this only in production, under load.

Two strategies prevent this: optimistic locking and pessimistic locking. Neither is universally better. The right choice depends on how often conflicts actually happen and how long your transactions run.

Optimistic Locking: Assume Conflicts Are Rare

Optimistic locking does not lock anything when you read a row. Instead, you track a version number or timestamp. When you write, you update only if the version still matches what you read. If another transaction changed the row in the meantime, the update affects zero rows and your application must retry or fail.

Here is a typical schema addition:

ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

A read returns the version:

SELECT id, status, version FROM orders WHERE id = 42;
-- returns id=42, status='pending', version=3

Your application changes the status and writes back with a WHERE clause on the version:

UPDATE orders
SET status = 'shipped', version = version + 1
WHERE id = 42 AND version = 3;

If version is still 3, the row updates to version 4. If another process already updated it to version 4, the WHERE clause fails, zero rows are updated, and your application knows the data is stale.

In Python with raw SQL:

def ship_order(conn, order_id, expected_version):
    with conn.cursor() as cur:
        cur.execute(
            """
            UPDATE orders
            SET status = 'shipped', version = version + 1
            WHERE id = %s AND version = %s
            """,
            (order_id, expected_version),
        )
        if cur.rowcount == 0:
            raise StaleOrderError("Order was modified by another transaction")

Optimistic locking works well when reads far outnumber writes and collisions are unusual. It avoids holding locks, so it scales better under read-heavy traffic.

Pessimistic Locking: Assume Conflicts Are Common

Pessimistic locking locks the row when you read it, so no other transaction can modify it until your transaction commits or rolls back. In PostgreSQL, you use SELECT ... FOR UPDATE.

BEGIN;
SELECT id, status FROM orders WHERE id = 42 FOR UPDATE;
-- row is locked; other transactions block here
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT;

While the lock is held, any other SELECT ... FOR UPDATE on the same row waits. This serializes access and prevents lost updates entirely, but it can create bottlenecks and deadlocks if transactions touch rows in different orders.

In application code:

def ship_order_pessimistic(conn, order_id):
    with conn.cursor() as cur:
        cur.execute("BEGIN")
        cur.execute(
            "SELECT status FROM orders WHERE id = %s FOR UPDATE",
            (order_id,),
        )
        row = cur.fetchone()
        if row is None:
            raise OrderNotFoundError(order_id)
        cur.execute(
            "UPDATE orders SET status = 'shipped' WHERE id = %s",
            (order_id,),
        )
        cur.execute("COMMIT")

Pessimistic locking fits write-heavy workloads where conflicts are likely, or operations that must absolutely succeed without retry logic. It is also safer when your business logic is complex and retries would be hard to implement correctly.

Choosing Between Them

Use optimistic locking when your users mostly read and occasionally edit. Use pessimistic locking when two users are likely to edit the same record at the same time and you cannot afford a failed write.

Common Gotchas

Optimistic locking with ORMs: Many ORMs support version columns automatically, but they may issue a separate SELECT before the UPDATE. That gap is fine for optimistic locking because the UPDATE still validates the version, but make sure your ORM does not overwrite columns you did not intend to change.

Pessimistic locking outside a transaction: SELECT ... FOR UPDATE only works inside a transaction. If your ORM wraps each statement in its own transaction, the lock is released immediately and you gain nothing.

Isolation levels: Both strategies assume at least READ COMMITTED. Under SERIALIZABLE, optimistic conflicts may surface as serialization errors instead of zero-row updates. Know your database's default.

Lock escalation: Some databases escalate row locks to table locks if too many rows are locked. Locking thousands of rows in one transaction can block the entire table.

A Hybrid Pattern

For high-contention workflows, combine both. Use optimistic locking on the main record to detect stale data, but use a short pessimistic lock on a related "lock" row when performing a multi-step mutation.

-- Application-level lock row
UPDATE order_locks
SET locked_by = 'worker-1', locked_at = NOW()
WHERE order_id = 42 AND locked_at IS NULL;

If the update affects zero rows, another worker already holds the lock. Release it when done:

UPDATE order_locks SET locked_by = NULL, locked_at = NULL WHERE order_id = 42;

This avoids database row locks on the business table while still serializing critical sections.

Checklist: Deciding for Your Service

• Measure how often the same row is updated concurrently.
• If conflicts are rare, add a version column and use optimistic updates.
• If conflicts are common and retries are complex, use SELECT ... FOR UPDATE.
• Keep pessimistic transactions under a few hundred milliseconds.
• Monitor deadlocks and lock wait time in your database.
• Test conflict paths under load, not just the happy path.

Practical Takeaway

Lost updates are silent and damaging. Add a version column to tables that users edit concurrently, validate it on every write, and reserve SELECT ... FOR UPDATE for the few workflows where a failed retry is worse than a brief wait.

IFLEON can help review your data layer and implement safe concurrency patterns.