Database Locking & Concurrency Cheat Sheet
Covers pessimistic and optimistic locking, isolation levels, deadlocks, and MVCC for handling concurrent reads and writes safely.
2 PagesAdvancedFeb 25, 2026
SQL Isolation Levels
The ANSI SQL transaction isolation levels.
- READ UNCOMMITTED- Allows dirty reads (seeing uncommitted changes from other transactions); rarely used, not truly supported by Postgres
- READ COMMITTED- Each statement sees only committed data as of when it started; the default in Postgres, Oracle, SQL Server
- REPEATABLE READ- A transaction sees a consistent snapshot for its entire duration; prevents non-repeatable reads but allows phantom reads in some engines
- SERIALIZABLE- Transactions behave as if executed one at a time; strongest guarantee, may abort transactions with serialization failures under contention
- Dirty read / non-repeatable read / phantom read- Dirty: reading uncommitted data. Non-repeatable: same row changes between two reads in a transaction. Phantom: a repeated query returns new rows
Pessimistic Locking
Lock rows upfront to prevent concurrent modification.
sql
BEGIN;-- Lock the row so no other transaction can modify it until COMMITSELECT balance FROM accounts WHERE id = 1 FOR UPDATE;UPDATE accounts SET balance = balance - 100 WHERE id = 1;UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT;-- FOR UPDATE SKIP LOCKED: useful for job queues, skips already-locked rowsSELECT * FROM jobs WHERE status = 'pending'ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Optimistic Locking
Detect conflicting writes with a version column.
sql
-- Add a version column to detect concurrent modificationALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 0;-- Read the current version in the application-- SELECT balance, version FROM accounts WHERE id = 1;-- Update only succeeds if version hasn't changed since the readUPDATE accountsSET balance = balance - 100, version = version + 1WHERE id = 1 AND version = 5;-- If 0 rows affected, another transaction won the race; app retries or errors
Concurrency Concepts
Vocabulary for reasoning about concurrent access.
- MVCC (Multi-Version Concurrency Control)- Postgres/MySQL InnoDB keep multiple row versions so readers never block writers and writers never block readers
- Deadlock- Two transactions each hold a lock the other needs; the database detects the cycle and aborts one transaction automatically
- Lock granularity- Row-level locks (most common) allow high concurrency; table-level locks are coarser and block more traffic
- Advisory locks- Application-defined locks (e.g., Postgres pg_advisory_lock) not tied to a specific row, useful for coordinating app-level critical sections
- Optimistic vs pessimistic- Pessimistic locks upfront assuming conflict is likely; optimistic checks for conflict only at write time, better for low-contention workloads
PostgreSQL Row & Table Lock Modes
The specific lock strengths Postgres exposes beyond a plain FOR UPDATE.
- FOR UPDATE- Strongest row lock; blocks any other UPDATE, DELETE, or locking read on the same rows until commit
- FOR NO KEY UPDATE- Like FOR UPDATE but doesn't conflict with FOR KEY SHARE, letting foreign-key checks on other rows proceed concurrently
- FOR SHARE- Read lock that blocks writers but allows other transactions to also take FOR SHARE on the same rows
- FOR KEY SHARE- Weakest row lock, taken automatically by referencing foreign keys; blocks only key-modifying updates
- ACCESS EXCLUSIVE- Table-level lock taken by DDL like ALTER TABLE or DROP TABLE; blocks all other access including plain SELECT
- ROW EXCLUSIVE- Table-level lock automatically taken by UPDATE/DELETE/INSERT; conflicts with ACCESS EXCLUSIVE but not with itself
Diagnosing and Retrying Deadlocks
Inspect blocking chains live and handle serialization failures in application code.
sql
-- Find who is blocking whom right nowSELECT blocked.pid AS blocked_pid, blocked_stmt.query AS blocked_query, blocking.pid AS blocking_pid, blocking_stmt.query AS blocking_queryFROM pg_locks blockedJOIN pg_locks blocking ON blocked.locktype = blocking.locktype AND blocked.database IS NOT DISTINCT FROM blocking.database AND blocked.relation IS NOT DISTINCT FROM blocking.relation AND blocked.pid != blocking.pidJOIN pg_stat_activity blocked_stmt ON blocked_stmt.pid = blocked.pidJOIN pg_stat_activity blocking_stmt ON blocking_stmt.pid = blocking.pidWHERE NOT blocked.granted AND blocking.granted;-- Force a lock wait timeout so a stuck transaction fails fast instead of hangingSET lock_timeout = '5s';-- Application retry loop pseudocode:-- Postgres error code 40P01 = deadlock_detected, 40001 = serialization_failure-- for attempt in range(3):-- try: run_transaction(); break-- except (DeadlockDetected, SerializationFailure): backoff_and_retry()
SSI Write Skew Under SERIALIZABLE
Postgres's Serializable Snapshot Isolation catches anomalies REPEATABLE READ misses.
sql
-- Classic write-skew: two on-call doctors both check "is someone else on call?"-- and both go off duty, violating the invariant "at least one doctor on call"BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*) FROM doctors WHERE on_call = true; -- sees 2-- ... app decides it's safe to go off call ...UPDATE doctors SET on_call = false WHERE id = 1;COMMIT;-- Under REPEATABLE READ this commits fine on both sessions (no shared row is-- written by both), leaving zero doctors on call.-- Under SERIALIZABLE, Postgres detects the read/write dependency cycle and-- aborts one transaction with: ERROR: could not serialize access due to-- read/write dependencies among transactions -- the app must retry.
Distributed Locking Across Services
Coordinate mutual exclusion across processes when a single DB transaction isn't enough.
sql
-- Postgres advisory locks scoped to a session, released on disconnect/commitSELECT pg_advisory_xact_lock(hashtext('invoice-generation-job'));-- ... critical section runs inside this transaction ...-- lock auto-released at COMMIT/ROLLBACK, no risk of an orphaned lock-- Non-blocking variant for "only one worker should run this" patternsSELECT pg_try_advisory_lock(hashtext('nightly-report'));-- returns true/false immediately instead of waiting-- Redis-based lock (Redlock-style) for cross-database coordination, with a TTL-- so a crashed holder can't block forever:-- SET lock:invoice-job <uuid> NX PX 30000-- ... work ...-- DEL lock:invoice-job only if value still equals <uuid> (Lua script for atomicity)
Advanced Concurrency Failure Modes
Beyond the classic ANSI anomalies — patterns that bite in production.
- Lost update- Two transactions read-modify-write the same row; the second commit silently overwrites the first's change unless a version check or FOR UPDATE prevents it
- Write skew- Two transactions read overlapping data and write disjoint rows, each individually valid but jointly violating an invariant; only SERIALIZABLE catches it
- Lock convoy- Many transactions queue behind one long-held lock, causing latency spikes that look like an outage even though no deadlock occurred
- Starvation- A transaction repeatedly loses out to others under contention and never acquires the lock, distinct from deadlock (no cycle, just bad luck/priority)
- Phantom via gap locks (InnoDB)- MySQL InnoDB uses next-key locks (row + gap) under REPEATABLE READ specifically to prevent phantom inserts that plain row locks would allow
- Idempotency key pattern- Combine a unique constraint with ON CONFLICT DO NOTHING to make retried writes safe under at-least-once delivery, sidestepping lock contention entirely
Pro Tip
Always acquire locks (SELECT FOR UPDATE) in the same, consistent order across all code paths that touch multiple rows — inconsistent lock ordering is the number one cause of deadlocks under load, and the database can only abort one side, not prevent the collision.
Was this cheat sheet helpful?
Explore Topics
#DatabaseLockingConcurrency#DatabaseLockingConcurrencyCheatSheet#Database#Advanced#SQLIsolationLevels#PessimisticLocking#OptimisticLocking#ConcurrencyConcepts#Databases#Concurrency#CheatSheet#SkillVeris
