Optimistic vs Pessimistic Locking in PostgreSQL

When two users edit the same record at once, one of them is about to overwrite the other's changes without knowing it. This is the lost-update problem, and it's one of the most common concurrency bugs in web applications, because the two users aren't even in the same transaction, they're separate HTTP requests seconds or minutes apart. Solving it means choosing between two strategies, optimistic and pessimistic, and the choice shapes how your whole editing flow behaves.

This is part 21 of the PostgreSQL Masterclass, following deadlocks.

The lost update, concretely

Two support agents open the same ticket. Both see status "open." Agent A sets it to "resolved" and saves. Agent B, still looking at the old form, sets priority to "high" and saves a moment later. B's save writes the whole row from the stale form, silently reverting A's status change. A's work is gone, and nobody got an error.

This can't be fixed by a database transaction alone, because each save is its own short transaction. The two never overlap. You need a strategy that spans the read and the later write.

Pessimistic locking: lock it while I work

Pessimistic locking assumes conflicts will happen, so it locks the row when a user starts editing and holds the lock until they finish. Anyone else who tries to edit waits or is refused. This is SELECT ... FOR UPDATE extended across the edit:

BEGIN;
  SELECT * FROM tickets WHERE id = 42 FOR UPDATE;  -- lock while editing
  -- ... user edits, then ...
  UPDATE tickets SET status = 'resolved' WHERE id = 42;
COMMIT;

For a database-to-database operation that's quick, this is clean and correct. For a human editing a form, it's usually wrong, because you'd hold a database transaction open for the minutes a person takes to fill it in, which violates the "keep transactions short" rule badly. A user who wanders off to lunch holds the lock indefinitely.

So pessimistic locking with a real database lock fits short, machine-speed critical sections: decrementing inventory, allocating a seat, processing a payment. It does not fit "a human has a form open." For that, you either use a shorter application-level lock with a timeout, or you use the optimistic approach.

Optimistic locking: assume it's fine, verify on save

Optimistic locking assumes conflicts are rare, so it takes no lock during editing. Instead it detects a conflict at save time by checking whether the row changed since you read it. The usual mechanism is a version column that increments on every update:

ALTER TABLE tickets ADD COLUMN version integer NOT NULL DEFAULT 0;

You read the row and remember its version. When you save, you include the version you read in the WHERE clause and bump it:

UPDATE tickets
SET status = 'resolved', version = version + 1
WHERE id = 42 AND version = 7;   -- 7 is the version we read

If nobody changed the row, its version is still 7, the update matches one row, and it succeeds. If someone else saved in the meantime, the version is now 8, the WHERE matches zero rows, and the update affects nothing. Your application checks the affected-row count: zero means a conflict, and you tell the user "this record changed since you opened it" instead of silently clobbering the other edit.

// pseudocode
rows = update(... WHERE id = 42 AND version = 7)
if rows == 0:
    // someone else changed it; reload and let the user redo their edit
    raise ConflictError

A timestamp like updated_at can play the version role too, but an integer version is more reliable because timestamps can collide at the same millisecond.

Which to choose

The decision comes down to how likely conflicts are and how long the critical section is:

• Optimistic is the default for user-facing edits and anything where conflicts are rare. No locks held during thinking time, no blocked users, and a clean conflict message on the rare clash. The cost is that a conflicting user has to redo their edit.
• Pessimistic fits short, machine-speed operations where a conflict is likely and retrying is wasteful, like decrementing stock under heavy contention. The critical section is milliseconds, so holding a lock is fine.

A useful way to think about it: optimistic locking is cheap when you're usually right that no one else is editing, and pessimistic locking is worth its cost when you're usually wrong. Most web-app editing is the former.

Combining with the isolation levels

These strategies connect back to the isolation article. Serializable isolation effectively gives you optimistic concurrency for free, since it detects conflicts and raises a serialization error you retry. A version column is the explicit, application-controlled version of the same idea, and it works at Read Committed without the retry-everything cost of Serializable. Many teams use the version-column approach precisely because it's targeted: only the operations that need conflict detection pay for it.

The lost-update bug is quiet and common, and neither a plain transaction nor Read Committed catches it. Add a version column to anything users edit concurrently, check the affected-row count on save, and you turn silent data loss into an explicit, handleable conflict. That's usually all it takes.

Next, we go deep on the pessimistic tool itself: SELECT FOR UPDATE and the row-locking patterns like skip-locked queues that build on it.