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

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?

CAP Theorem Cheat Sheet

Full-Text Search Cheat Sheet

Explore Topics

#DatabaseLockingConcurrency#DatabaseLockingConcurrencyCheatSheet#Database#Advanced#SQLIsolationLevels#PessimisticLocking#OptimisticLocking#ConcurrencyConcepts#Databases#Concurrency#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services