Pessimistic and Optimistic Locking, Row and Table Locks

In one line

Pessimistic locking takes locks before changing data (SELECT ... FOR UPDATE) so others wait; optimistic locking lets everyone proceed and rejects stale writes with a version check. Row locks protect individual rows; table-level lock modes mostly matter for DDL. SKIP LOCKED turns a table into a work queue, and lock_timeout stops lock waits from piling up.

0/5 · 0%

Think of it like this

Editing a shared document. Pessimistic: you check the file out and nobody else can edit until you return it. Optimistic: everyone edits a copy, and when saving, the system rejects your save if someone else saved first, so you merge and retry.

Key ideas

01
Row locks in PostgreSQL: FOR UPDATE (exclusive, for rows you'll modify), FOR NO KEY UPDATE (taken by normal UPDATEs that don't change keys), FOR SHARE / FOR KEY SHARE (FK checks take KEY SHARE on the parent). Plain SELECTs take no row locks; readers never block writers under MVCC.
02
Table lock modes range from ACCESS SHARE (every SELECT) to ACCESS EXCLUSIVE (most ALTER TABLE, DROP, TRUNCATE, VACUUM FULL). An ALTER waiting for ACCESS EXCLUSIVE queues behind a long SELECT, and every new query then queues behind the ALTER: an outage from a "quick" migration.
03
Optimistic locking: a version column; UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?; zero rows updated means a conflict, so reload and retry or report. Ideal for low-contention edits (user profiles, admin forms). JPA's @Version does this.
04
FOR UPDATE SKIP LOCKED lets many workers each grab different unlocked rows, a safe job queue in plain SQL. NOWAIT fails immediately instead of waiting.
05
Advisory locks (pg_advisory_xact_lock(key)) lock an application-defined number: useful for "only one cron job instance" without a row to lock.

Code & diagrams

locking.sqlsql

-- pessimistic: reserve stock
BEGIN;
SELECT qty FROM stock WHERE product_id = 42 FOR UPDATE;   -- others wait here
UPDATE stock SET qty = qty - 1 WHERE product_id = 42;
COMMIT;

-- optimistic: version check
UPDATE profile SET bio = 'New bio', version = version + 1
WHERE user_id = 7 AND version = 12;
-- UPDATE 0  -> someone else saved first: reload and retry or show a conflict

-- job queue with SKIP LOCKED
WITH next AS (
  SELECT id FROM job
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE job SET status = 'running', started_at = now()
FROM next WHERE job.id = next.id
RETURNING job.id;

-- guard migrations against lock pile-ups
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Interview problem

The problem

Flash sale: 10,000 buyers, 100 units

A flash sale has 100 units and 10,000 concurrent buyers. With SELECT ... FOR UPDATE on the stock row, p99 latency is 8 seconds and the pool is exhausted. Redesign to prevent overselling with acceptable latency.

When it breaks

Migration blocked behind a long-running query

What you see

ALTER TABLE waits for ACCESS EXCLUSIVE; all new SELECTs queue behind it; the site goes down although the ALTER itself is instant.

Fix & prevent

SET lock_timeout = '3s' and retry the migration; cancel long analytics queries; run migrations at low traffic.

Optimistic locking under high contention

What you see

Most updates conflict and retry repeatedly; throughput collapses and users see repeated conflict errors.

Fix & prevent

Switch hot paths to atomic updates or pessimistic locks; keep optimistic locking for low-contention edits.

Explain it without notes

When is optimistic locking better than pessimistic locking?

Practice

Write a query that claims one pending email job per worker without two workers ever getting the same job.

Trade-offs

↔
Pessimistic locks guarantee progress order but serialise; optimistic locks maximise concurrency but waste work on conflicts.

Done when you can

I can choose between pessimistic, optimistic and atomic-statement approaches and use SKIP LOCKED and lock_timeout.

Command Palette