Concurrency, isolation, and locking

Most analyst SQL never thinks about concurrency — the database handles it. But when you write SQL that participates in production workflows (queues, counters, ledgers), the isolation level matters. So does the order in which you lock things.

The four ANSI isolation levels

• READ UNCOMMITTED — sees uncommitted writes from other transactions ('dirty reads'). Allowed by ANSI but rarely useful; most engines treat this as READ COMMITTED
• READ COMMITTED — default for Postgres, Oracle, SQL Server. Only sees committed data. Within a transaction, repeated SELECTs may see different data ('non-repeatable reads')
• REPEATABLE READ — default for MySQL InnoDB. Once a row is read in a transaction, re-reading shows the same value. Still permits 'phantom reads' (new rows matching your filter appear)
• SERIALIZABLE — strictest. Transactions appear to execute one at a time. Postgres implements this via Serializable Snapshot Isolation (SSI); MySQL via locking

Optimistic vs pessimistic locking

Two strategies for handling concurrent writes to the same row.

-- Pessimistic: take a lock before reading, hold until commit
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- ... compute new balance ...
UPDATE accounts SET balance = $new WHERE id = 1;
COMMIT;

-- Optimistic: read without lock; check unchanged on write
BEGIN;
SELECT balance, version FROM accounts WHERE id = 1;
-- ... compute new balance, save the version we saw ...
UPDATE accounts SET balance = $new, version = version + 1
WHERE id = 1 AND version = $expected_version;
-- If row count = 0, someone updated under us → retry from the SELECT
COMMIT;

• Pessimistic (FOR UPDATE) — right when contention is high or the compute step is short. Blocks other writers; simple to reason about
• Optimistic (version column) — right when contention is low and the compute step might be long. No locks held; conflicts retry. Better throughput under low contention

FOR UPDATE SKIP LOCKED: the queue pattern

Multiple workers consuming from a job queue need to avoid two workers picking up the same job. SKIP LOCKED is the elegant solution.

-- Worker picks one unclaimed job; other workers skip it and grab a different one
BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;

UPDATE jobs SET status = 'in_progress', claimed_by = $worker, claimed_at = NOW()
WHERE id = $picked_id;
COMMIT;

Deadlocks

Two transactions each holding a lock the other wants. The engine detects the cycle and kills one (the 'victim') — that connection sees a deadlock error and must retry.

-- Transaction A:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- Transaction B (running concurrently):
UPDATE accounts SET balance = balance - 5 WHERE id = 2;
UPDATE accounts SET balance = balance + 5 WHERE id = 1;
-- DEADLOCK — A holds 1 wants 2, B holds 2 wants 1

1. Always acquire locks in a consistent order (e.g., always lock the lower-id row first)
2. Keep transactions short — long-held locks compound contention
3. Handle deadlock errors at the application layer — retry with backoff
4. Use the smallest-scope lock that's correct — FOR UPDATE on one row, not the whole table

Advisory locks

When the row-level lock doesn't fit the semantics — e.g., you want to ensure only one worker runs a daily ETL — advisory locks are a named, transaction-scoped or session-scoped lock with no underlying table.

-- Postgres advisory lock — exclusive within a session
SELECT pg_try_advisory_lock(12345);  -- returns false if already held
-- ... do work ...
SELECT pg_advisory_unlock(12345);