Isolation Levels & Locking

Every time two users touch the same data at the same time, the database has to make a choice: how much should one user see of the other's unfinished work? That choice is the isolation levelNamed after the "I" in ACID. Isolation determines whether Transaction A can see data that Transaction B changed but hasn't committed yet. The SQL standard defines four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Each level prevents progressively more anomalies but costs progressively more performance.. Set it too low and you'll read garbage data that was never committed. Set it too high and your throughput drops to a crawl because every transaction waits in line.

The trick is that most of your queries need the default (READ COMMITTED), and only a few critical paths — payments, inventory decrements, seat bookings — need the heavy stuff. Stripe runs SERIALIZABLE on every payment transaction. They'd rather retry a failed transaction than charge someone twice. Booking.com uses optimistic lockingA strategy where you DON'T lock the row when you read it. Instead, you include a version number in your WHERE clause when you UPDATE. If someone else changed the row between your read and your write, the version won't match, your UPDATE affects 0 rows, and you know to retry. It's "optimistic" because you assume conflicts are rare — and for most workloads, they are. Booking.com uses this for hotel availability: each room record has a version column. with version columns for hotel availability — no heavy locks, just a version check at write time.

You can see your database's current isolation level right now. Open a terminal and run this:

-- PostgreSQL: check your default isolation level
SHOW transaction_isolation;
-- Output: read committed  (that's the default)

-- MySQL: check your default isolation level
SELECT @@transaction_isolation;
-- Output: REPEATABLE-READ  (MySQL defaults to a HIGHER level than Postgres!)

-- See your current transaction's unique ID
SELECT txid_current();  -- PostgreSQL assigns every transaction a sequential number

It's Friday at 10:00 AM. A popular band just announced a concert. 100 tickets go live. 10,000 fans hit "Buy" within the same second. Your job is to build the ticket system. Let's think about what happens if you get this wrong.

Let's set up the exact table so you can reproduce this yourself. Run this in either terminal:

-- Create a simple tickets table for our flash sale
CREATE TABLE tickets (
    id          SERIAL PRIMARY KEY,
    event_name  TEXT NOT NULL,
    available   INT  NOT NULL CHECK (available >= 0),
    price       NUMERIC(10,2) NOT NULL
);

-- Insert our concert: 100 tickets at $75 each
INSERT INTO tickets (event_name, available, price)
VALUES ('Summer Rooftop Concert', 100, 75.00);

-- Verify it's there
SELECT * FROM tickets;
--  id |       event_name       | available | price
-- ----+------------------------+-----------+-------
--   1 | Summer Rooftop Concert |       100 | 75.00

This is called a lost updateWhen two transactions read the same row, both compute a new value based on what they read, and then both write. The second write overwrites the first, and the first transaction's change is silently lost. It's one of the most common concurrency bugs in web applications. The fix: either use SELECT FOR UPDATE to lock the row before reading, or use UPDATE tickets SET available = available - 1 so the decrement is atomic (the database does the math, not your application).. Two people bought tickets, but the count only went down by one. The second write silently erased Alice's purchase. In a flash sale with 10,000 concurrent users, this can happen hundreds of times. You end up with 500 "successful" purchases for 100 tickets.

The numbers are real. Taylor Swift's Eras Tour had 14 million people in the Ticketmaster queue for about 2 million tickets. At that scale, even a 0.01% lost update rate means 1,400 double-sold seats. The resulting congressional hearing wasn't about slow pages — it was about people who were charged for seats that had already been sold to someone else.

The first thing every developer tries: wrap it in a transaction. BEGIN, do the work, COMMIT. Surely that fixes everything, right?

Let's test it. Open both terminals and follow along. We'll use PostgreSQL's default isolation level (READ COMMITTED) and see if plain transactions protect us.

-- Step 1: Start a transaction
BEGIN;

-- Step 2: Read the current ticket count
SELECT available FROM tickets WHERE id = 1;
-- You'll see: 100

-- PAUSE HERE. Go to Terminal B and run its Step 1 and Step 2.
-- Come back when Terminal B also shows 100.

-- Step 3: Decrement based on what we read
UPDATE tickets SET available = 99 WHERE id = 1;

-- Step 4: Commit
COMMIT;

-- Step 5: Check the result
SELECT available FROM tickets WHERE id = 1;
-- You'll see: 99   <-- Should be 98! We lost Bob's update.

-- Step 1: Start a transaction
BEGIN;

-- Step 2: Read the current ticket count
SELECT available FROM tickets WHERE id = 1;
-- You'll see: 100  (same as Alice — that's the problem!)

-- PAUSE HERE. Go back to Terminal A and run Steps 3 & 4 (UPDATE + COMMIT).
-- Then come back here.

-- Step 3: Decrement based on what we read (we think it's still 100)
UPDATE tickets SET available = 99 WHERE id = 1;
-- NOTE: This UPDATE blocks briefly until Alice's COMMIT finishes.
-- But then it goes through — setting available to 99 AGAIN.

-- Step 4: Commit
COMMIT;

-- Step 5: Check the result
SELECT available FROM tickets WHERE id = 1;
-- 99 — We both bought a ticket, but only one was counted!

So "just use transactions" is not wrong — it's incomplete. Transactions give you atomicityThe "A" in ACID. Atomicity means "all or nothing." If your transaction has three SQL statements and the server crashes after the second one, PostgreSQL rolls back ALL of them on restart. You never get a half-done state. But atomicity says nothing about whether OTHER transactions can interfere while yours is running — that's isolation's job. (all-or-nothing), but the level of isolation between concurrent transactions depends on the isolation level you're running at. And the default, READ COMMITTED, is designed for speed, not safety. It assumes conflicts are rare and your application code handles the edge cases. For a flash sale with 10,000 concurrent writes to the same row? Those edge cases become the common case.

The SQL standard defines four bad things that can happen when transactions overlap. They range from "obviously wrong" to "subtly catastrophic." Each higher isolation level prevents more of them. Let's reproduce every single one so you see them happen in your terminals, not just read about them in a textbook.

Let's go through each one. For every anomaly, you'll get the exact SQL to run in your two terminals so you can see the problem live.

Anomaly 1: Dirty Read

A dirty readReading data from a transaction that hasn't committed yet. If that transaction rolls back, you just based a decision on data that NEVER EXISTED. It's called "dirty" because the data hasn't been cleaned up (committed) yet. The SQL standard says READ UNCOMMITTED allows this, but PostgreSQL is opinionated: even at READ UNCOMMITTED, PostgreSQL silently upgrades you to READ COMMITTED. MySQL's InnoDB, however, will actually let you read uncommitted data. happens when Transaction B reads data that Transaction A wrote but hasn't committed yet. If A later rolls back, B just acted on data that never existed. Imagine approving a $10,000 loan because you read the applicant's account balance as $50,000 — but that balance was from an uncommitted transaction that gets rolled back. The applicant actually has $500.

-- Reset first
UPDATE tickets SET available = 100 WHERE id = 1;

-- === TERMINAL A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- PG ignores this, treats as READ COMMITTED
UPDATE tickets SET available = 50 WHERE id = 1;     -- Changed but NOT committed
-- Don't commit yet! Switch to Terminal B.

-- === TERMINAL B ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT available FROM tickets WHERE id = 1;
-- PostgreSQL returns: 100  (the committed value — it REFUSES to show you the dirty 50)
COMMIT;

-- === TERMINAL A ===
ROLLBACK;  -- We changed our mind. The 50 was never real.
-- If B had read 50, it would have acted on fantasy data.
-- PostgreSQL protected you.

-- In MySQL/InnoDB, dirty reads actually happen at READ UNCOMMITTED

-- === TERMINAL A ===
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE tickets SET available = 50 WHERE id = 1;
-- Don't commit!

-- === TERMINAL B ===
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT available FROM tickets WHERE id = 1;
-- MySQL returns: 50  ← DIRTY READ! This data was never committed.
COMMIT;

-- === TERMINAL A ===
ROLLBACK;  -- The 50 never happened.
-- But Terminal B already read 50 and maybe made decisions based on it.
-- That's why almost nobody uses READ UNCOMMITTED in production.

Anomaly 2: Non-Repeatable Read

This is the one you'll actually hit in production. You read a row, do some logic, then read the same row again — and the value changed because someone else committed an update in between. It's called "non-repeatable" because the same SELECT gives you a different answer each time you run it within the same transaction.

This is exactly the flash sale bug from Section 2. Let's reproduce it explicitly.

-- Reset
UPDATE tickets SET available = 100 WHERE id = 1;

-- === TERMINAL A ===
BEGIN;
SELECT available FROM tickets WHERE id = 1;
-- Returns: 100
-- PAUSE. Go to Terminal B.

-- === TERMINAL B === (run all of this)
BEGIN;
UPDATE tickets SET available = 80 WHERE id = 1;
COMMIT;  -- Bob's update is now committed.

-- === TERMINAL A === (come back here)
SELECT available FROM tickets WHERE id = 1;
-- Returns: 80  ← DIFFERENT from your first read!
-- Same transaction, same SELECT, different result.
-- That's a non-repeatable read.
COMMIT;

At READ COMMITTEDThe default in PostgreSQL. Each SQL statement sees a snapshot of all data committed at the instant that statement begins executing. So two SELECT statements in the same transaction can see different data if someone committed changes between them. This is fine for most queries, but dangerous for read-then-write patterns like our flash sale., each SELECT sees a fresh snapshot of committed data. If someone committed a change between your two SELECTs, you'll see it. For casual reads (like showing a user's profile) this is fine. For the flash sale? It's a disaster, because your application reads 100, computes 99, and writes 99 — not knowing someone changed it to 80 in between.

Anomaly 3: Phantom Read

A phantom readA non-repeatable read is about a single row changing. A phantom read is about the SET OF ROWS changing. You run a WHERE query, get 5 rows. Someone inserts a new row that matches your WHERE. You run the same query again — now you get 6 rows. The new row is the "phantom." This matters for aggregations like COUNT, SUM, and for business logic that depends on "how many rows match this condition." is sneakier than a non-repeatable read. Instead of an existing row changing, a new row appears (or an existing one disappears) between two queries. You count the rows matching a condition — get 5. Someone inserts a matching row. You count again — get 6. The new row is the "phantom."

This matters for queries like "how many tickets are available across all events" or "how many orders did we process today." Let's set up a multi-event table to demonstrate:

-- Setup: add more events
INSERT INTO tickets (event_name, available, price) VALUES
    ('Jazz Night', 50, 45.00),
    ('Comedy Show', 75, 30.00);

-- === TERMINAL A ===
BEGIN;
-- How many events have tickets priced over $40?
SELECT COUNT(*) FROM tickets WHERE price > 40.00;
-- Returns: 2  (the concert at $75 and jazz at $45)
-- PAUSE. Go to Terminal B.

-- === TERMINAL B ===
INSERT INTO tickets (event_name, available, price) VALUES ('VIP Gala', 20, 200.00);
-- No transaction needed — it auto-commits.

-- === TERMINAL A === (come back)
SELECT COUNT(*) FROM tickets WHERE price > 40.00;
-- Returns: 3  ← PHANTOM! A new row appeared that matches our WHERE.
-- If you're generating an invoice or report inside this transaction,
-- the totals at the top won't match the line items at the bottom.
COMMIT;

Anomaly 4: Serialization Anomaly (Write Skew)

This is the subtlest and most dangerous anomaly. Two transactions both read overlapping data, then each writes based on what it read. Neither transaction's individual read or write is wrong. But the combination of their writes creates a state that couldn't have happened if they ran one at a time. This is called write skewA concurrency anomaly where two transactions read the same data, make different decisions based on it, and write non-conflicting rows. Neither transaction violates any constraint individually, but together they create an invalid state. Classic example: two doctors are on-call. Hospital policy requires at least one. Both check "are there 2+ doctors on-call?" (yes), both request leave (each removes themselves). Result: zero doctors on-call. Neither UPDATE conflicts with the other because they modify different rows — but the invariant (at least 1 doctor) is violated..

Classic example: a hospital requires at least one doctor on-call at all times. Two doctors are on-call. Both check: "Is there more than one doctor on-call?" Both see: yes (2). Both submit "take me off-call." Both transactions succeed — because they're updating different rows (each doctor's own record). Result: zero doctors on-call. Nobody violated any constraint individually, but the combined result is illegal.

-- Setup: two doctors on-call
CREATE TABLE doctors (
    id       SERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    on_call  BOOLEAN NOT NULL DEFAULT true
);
INSERT INTO doctors (name, on_call) VALUES ('Dr. Alice', true), ('Dr. Bob', true);

-- === TERMINAL A === (Dr. Alice wants to go home)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- "Are there at least 2 doctors on-call? If yes, I can leave."
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- Returns: 2 — safe to leave!
-- PAUSE. Go to Terminal B.

-- === TERMINAL B === (Dr. Bob ALSO wants to go home)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- Returns: 2 — also looks safe!
UPDATE doctors SET on_call = false WHERE name = 'Dr. Bob';
COMMIT;  -- Bob is off-call now.

-- === TERMINAL A === (come back)
UPDATE doctors SET on_call = false WHERE name = 'Dr. Alice';
COMMIT;  -- Alice is off-call too.

-- Check the damage:
SELECT * FROM doctors WHERE on_call = true;
-- Returns: 0 rows. Nobody is on-call. Hospital policy violated.
-- REPEATABLE READ didn't catch this because each txn modified a DIFFERENT row.

The key insight: write skew can't be prevented by row-level locks because the two transactions modify different rows. The database doesn't detect a conflict because there's no physical overlap. Only SERIALIZABLEThe highest isolation level. The database guarantees that the result of running concurrent transactions is identical to SOME serial (one-at-a-time) order. PostgreSQL implements this using Serializable Snapshot Isolation (SSI), which tracks read and write dependencies between transactions. If it detects a cycle that could produce an anomaly, it aborts one of the transactions with a "serialization failure" error. Your application must be ready to retry. Stripe wraps every payment in SERIALIZABLE and retries up to 3 times on conflict. isolation catches this. At SERIALIZABLE, PostgreSQL detects that the two transactions read overlapping data and wrote based on conflicting assumptions, and it aborts one of them with a serialization failure.

-- Reset
UPDATE doctors SET on_call = true;

-- === TERMINAL A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- Returns: 2
-- PAUSE. Go to Terminal B.

-- === TERMINAL B ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- Returns: 2
UPDATE doctors SET on_call = false WHERE name = 'Dr. Bob';
COMMIT;  -- Succeeds.

-- === TERMINAL A === (come back)
UPDATE doctors SET on_call = false WHERE name = 'Dr. Alice';
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies
-- DETAIL: Reason code: Canceled on identification as a pivot...

-- PostgreSQL aborted Alice's transaction. She must retry.
-- When she retries, she'll see only 1 doctor on-call and knows she can't leave.
-- The hospital is safe.

*PostgreSQL silently upgrades READ UNCOMMITTED to READ COMMITTED. **PostgreSQL also prevents phantoms at REPEATABLE READ (goes beyond the SQL standard).

Here's the insight that separates juniors from seniors: not everything needs SERIALIZABLE. And not everything is safe at READ COMMITTED. The real skill is knowing which isolation level to use where.

Think of it like door locks in a building. The lobby has a simple latch — anyone can walk in. Your office has a key lock. The server room has a badge reader plus a deadbolt. And the vault has biometric scanning plus a time-locked combination. You don't put vault-level security on the lobby — nobody would get in. And you don't put a simple latch on the vault — everything would get stolen. Same with isolation levels: the cost of safety goes up at each level, and most of your data doesn't need the highest tier.

Let's check your database's default right now, and try switching levels:

-- Check your current default
SHOW transaction_isolation;
-- PostgreSQL: "read committed"
-- This is what every new transaction uses unless you say otherwise.

-- Change it for ONE transaction only:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your critical SQL here ...
COMMIT;
-- The NEXT transaction goes back to the default. No permanent change.

-- Change the default for your entire session:
SET default_transaction_isolation = 'serializable';
-- Now EVERY transaction in this connection uses SERIALIZABLE.
-- Useful for payment microservices that need it for every query.

-- Change the server-wide default (postgresql.conf):
-- default_transaction_isolation = 'read committed'
-- You almost NEVER change this. 99% of connections should use the default.

-- See your current transaction ID (useful for debugging):
SELECT txid_current();
-- Returns something like: 12847562
-- Every transaction gets a unique, sequential number.

The PostgreSQL vs MySQL Difference

This trips up a lot of developers who switch between databases. The two most popular open-source databases made different default choices:

This means the exact same application code behaves differently on PostgreSQL vs MySQL. If you develop on MySQL (REPEATABLE READ default) and deploy on PostgreSQL (READ COMMITTED default), you might introduce non-repeatable read bugs that never appeared in testing. Always check SHOW transaction_isolation; when you set up a new database.

The retry rate is the key number. At SERIALIZABLE, PostgreSQL will occasionally abort a transaction with error code 40001 (serialization failure). Your application must catch this and retry the entire transaction. This is not a bug — it's the database saying "I detected a potential anomaly and prevented it by killing your transaction. Try again." Stripe's codebase has retry wrappers around every database call for exactly this reason.

# The standard retry pattern for SERIALIZABLE transactions
# (Python + psycopg2, but the pattern is the same in any language)

import psycopg2
import time

def run_serializable(conn, sql_func, max_retries=3):
    """Execute a function inside a SERIALIZABLE transaction with retry."""
    for attempt in range(max_retries):
        try:
            with conn:  # auto-commit/rollback
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    return sql_func(cur)  # Your business logic here
        except psycopg2.errors.SerializationFailure:
            if attempt == max_retries - 1:
                raise  # Give up after max retries
            time.sleep(0.01 * (2 ** attempt))  # Exponential backoff: 10ms, 20ms, 40ms
            conn.rollback()
    # If we get here, all retries failed

# Usage:
def book_ticket(cur):
    cur.execute("SELECT available FROM tickets WHERE id = 1")
    available = cur.fetchone()[0]
    if available <= 0:
        raise Exception("Sold out!")
    cur.execute("UPDATE tickets SET available = available - 1 WHERE id = 1")
    return "Ticket booked!"

# This will retry up to 3 times on serialization conflict
result = run_serializable(conn, book_ticket)

Now you know what the four levels are, what anomalies they prevent, and when to use each one. But we've only covered half the story. Isolation levels are the automatic protection the database gives you. In Sections 6+, we'll dive into explicit locking — SELECT FOR UPDATE, SELECT FOR UPDATE SKIP LOCKED, advisory locks, deadlock detection — the tools you use when you want to take manual control of concurrency instead of relying on the database to figure it out.

Isolation levels are the automatic safety net. But sometimes you need to take the wheel yourself. You know exactly which row is going to be contested, and you want to lock it before anyone else touches it. That's explicit lockingWhen you manually tell the database to lock specific rows (or tables) rather than relying on the isolation level to handle concurrency automatically. Explicit locks give you fine-grained control: you decide which rows to lock, when to lock them, and how long to hold them. The trade-off is that you must think about lock ordering and deadlocks yourself — the database won't do it for you. — and there are four main strategies, each with different trade-offs.

-- Reset first
UPDATE tickets SET available = 100 WHERE id = 1;

-- Step 1: Begin and lock the row
BEGIN;
SELECT * FROM tickets WHERE id = 1 FOR UPDATE;
--  id |       event_name       | available | price
-- ----+------------------------+-----------+-------
--   1 | Summer Rooftop Concert |       100 | 75.00
-- The row is now LOCKED. Go to Terminal B.

-- (After Terminal B tries and blocks...)

-- Step 2: Decrement and commit
UPDATE tickets SET available = available - 1 WHERE id = 1;
COMMIT;
-- Terminal B unblocks now and sees available = 99

-- Step 1: Try to lock the SAME row
BEGIN;
SELECT * FROM tickets WHERE id = 1 FOR UPDATE;
-- ⏳ BLOCKS HERE. The cursor just hangs.
-- Terminal A holds the lock. We wait.

-- (After Terminal A commits, this query returns:)
--  id |       event_name       | available | price
-- ----+------------------------+-----------+-------
--   1 | Summer Rooftop Concert |        99 | 75.00
-- We see 99 (Alice's update), NOT 100. No lost update!

-- Step 2: Decrement from the CORRECT value
UPDATE tickets SET available = available - 1 WHERE id = 1;
COMMIT;
-- Final: available = 98. Both tickets properly counted!

-- While Terminal A holds the lock, open a THIRD terminal and run:
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'tickets'::regclass;

-- You'll see something like:
--  locktype | relation |       mode       | granted | pid
-- ----------+----------+------------------+---------+------
--  relation | tickets  | RowShareLock     | t       | 1234  ← Terminal A (holds it)
--  relation | tickets  | RowShareLock     | t       | 1235  ← Terminal B (waiting)
--  tuple    | tickets  | ExclusiveLock    | f       | 1235  ← The actual wait

-- "granted = f" means Terminal B is WAITING for that lock.
-- As soon as Terminal A commits, granted flips to "t".

-- Individual tickets (one row per seat) instead of a counter
CREATE TABLE seats (
    id        SERIAL PRIMARY KEY,
    event_id  INT NOT NULL,
    seat_num  TEXT NOT NULL,
    status    TEXT NOT NULL DEFAULT 'available'
        CHECK (status IN ('available', 'booked')),
    booked_by TEXT
);

-- Create 100 seats for our concert
INSERT INTO seats (event_id, seat_num)
SELECT 1, 'SEAT-' || generate_series(1, 100);

-- Verify
SELECT count(*) FROM seats WHERE status = 'available';
-- 100 seats ready to go

-- Step 1: Grab one available seat (lock it)
BEGIN;
SELECT id, seat_num FROM seats
WHERE status = 'available'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
--  id | seat_num
-- ----+----------
--   1 | SEAT-1
-- SEAT-1 is locked. Go to Terminal B immediately.

-- (After checking Terminal B got SEAT-2, not SEAT-1...)

-- Step 2: Book it
UPDATE seats SET status = 'booked', booked_by = 'Alice' WHERE id = 1;
COMMIT;

-- Step 1: Grab one available seat (skip locked rows)
BEGIN;
SELECT id, seat_num FROM seats
WHERE status = 'available'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
--  id | seat_num
-- ----+----------
--   2 | SEAT-2
-- NOT SEAT-1! That one is locked by Alice, so we SKIPPED it.
-- No waiting. No blocking. Instant response.

-- Step 2: Book it
UPDATE seats SET status = 'booked', booked_by = 'Bob' WHERE id = 2;
COMMIT;
-- Both Alice and Bob got different seats, zero waiting.

# Benchmark: 100 concurrent connections, each booking one seat
# Save this as book_seat.sql:
# BEGIN;
# SELECT id FROM seats WHERE status='available'
#   ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
# UPDATE seats SET status='booked' WHERE id = (
#   SELECT id FROM seats WHERE status='available'
#     ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED);
# COMMIT;

# Run with pgbench (built into PostgreSQL):
pgbench -c 100 -j 4 -t 1 -f book_seat.sql mydb

# Compare: WITHOUT SKIP LOCKED (just FOR UPDATE):
# ~1,700 ms for 100 bookings (everyone waits in line)

# WITH SKIP LOCKED:
# ~147 ms for 100 bookings (everyone grabs a different row)
# That's a 10× speedup.

-- Add a version column to products
CREATE TABLE products (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    stock   INT NOT NULL CHECK (stock >= 0),
    version INT NOT NULL DEFAULT 1
);

INSERT INTO products (name, stock) VALUES ('Limited Edition Sneakers', 50);

-- Application reads the product (no locks!)
SELECT id, stock, version FROM products WHERE id = 1;
--  id | stock | version
-- ----+-------+---------
--   1 |    50 |       1

-- Application tries to decrement stock
-- Notice: WHERE includes version = 1
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 1;
-- UPDATE 1  ← success! One row updated.

-- If another transaction already changed version to 2:
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 1;
-- UPDATE 0  ← ZERO rows updated. Version mismatch!
-- The application knows: "someone else updated first, I need to retry."

-- See the hidden MVCC columns in PostgreSQL
-- Every row has these, you just don't normally see them:
SELECT xmin, xmax, ctid, * FROM tickets WHERE id = 1;
--  xmin  | xmax | ctid  | id |       event_name       | available | price
-- -------+------+-------+----+------------------------+-----------+-------
--  12847 |    0 | (0,1) |  1 | Summer Rooftop Concert |       100 | 75.00

-- xmin = the transaction ID that CREATED this row version
-- xmax = the transaction ID that DELETED/REPLACED this version (0 = still alive)
-- ctid = physical location on disk (page 0, tuple 1)

-- Now update the row:
UPDATE tickets SET available = 99 WHERE id = 1;

-- Check again:
SELECT xmin, xmax, ctid, * FROM tickets WHERE id = 1;
--  xmin  | xmax | ctid  | id |       event_name       | available | price
-- -------+------+-------+----+------------------------+-----------+-------
--  12850 |    0 | (0,2) |  1 | Summer Rooftop Concert |        99 | 75.00
-- Notice: xmin changed (new transaction created this version)
-- Notice: ctid changed from (0,1) to (0,2) — NEW physical row!
-- The old version at (0,1) is now a "dead tuple" — it has xmax set.

-- See how many dead tuples exist:
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'tickets';
--  relname | n_live_tup | n_dead_tup
-- ---------+------------+------------
--  tickets |          1 |          1    ← one dead version lingering
-- VACUUM will clean this up.

The four strategies from Section 6 cover 90% of what you'll need. But production databases throw curveballs. Deadlocks happen at 3 AM. MySQL locks invisible gaps between index entries. Sometimes you need locks that aren't tied to any row at all. This section covers the advanced concepts that separate someone who can use databases from someone who can debug them under pressure.

-- Terminal A:
BEGIN;
UPDATE tickets SET price = 80.00 WHERE id = 1;  -- locks row 1
-- Now go to Terminal B...

-- Terminal B:
BEGIN;
UPDATE tickets SET price = 90.00 WHERE id = 2;  -- locks row 2
UPDATE tickets SET price = 85.00 WHERE id = 1;  -- BLOCKS (A has row 1)
-- Now go back to Terminal A...

-- Terminal A:
UPDATE tickets SET price = 95.00 WHERE id = 2;  -- DEADLOCK!
-- ERROR: deadlock detected
-- DETAIL: Process 1234 waits for ShareLock on transaction 12850;
--         blocked by process 1235.
--         Process 1235 waits for ShareLock on transaction 12849;
--         blocked by process 1234.

-- PostgreSQL killed Terminal A's transaction. Terminal B proceeds.

-- Find deadlocked queries:
SELECT pid, state, wait_event_type, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Set detection timeout (how long before PG checks for deadlocks):
SET deadlock_timeout = '1s';  -- default is 1 second

-- MySQL only (gap locks don't exist in PostgreSQL)
-- This query locks ALL rows with amount between 100-200
-- AND the gaps between them:
BEGIN;
SELECT * FROM orders
WHERE amount BETWEEN 100 AND 200
FOR UPDATE;

-- In another session, try inserting into the gap:
INSERT INTO orders (amount) VALUES (150);
-- ⏳ BLOCKS! The gap between 100 and 200 is locked.

-- This can cause surprising deadlocks in MySQL
-- when two transactions lock overlapping ranges.

-- See InnoDB lock info:
SELECT * FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'RECORD';

-- Acquire an advisory lock with ID 12345
-- (the number is arbitrary — your app decides what it means)
SELECT pg_advisory_lock(12345);
-- Returns immediately. You now hold lock #12345.

-- In another terminal:
SELECT pg_advisory_lock(12345);
-- ⏳ BLOCKS until the first session releases it.

-- Non-blocking version (returns true/false instead of waiting):
SELECT pg_try_advisory_lock(12345);
-- Returns FALSE if someone else holds it. Your app can decide what to do.

-- Release it:
SELECT pg_advisory_unlock(12345);

-- Session-level advisory lock (auto-released when connection closes):
SELECT pg_advisory_lock(42);
-- Even if your transaction rolls back, this lock stays until disconnect.

-- Transaction-level advisory lock (released on COMMIT/ROLLBACK):
SELECT pg_advisory_xact_lock(42);

-- See all advisory locks held right now:
SELECT * FROM pg_locks WHERE locktype = 'advisory';

The SQL standard defines four isolation levels. But every database implements them differently. The "same" isolation level can behave very differently depending on which database you're using. This section maps out the key differences so you don't get blindsided when switching databases or deploying to a different engine.

-- PostgreSQL: READ UNCOMMITTED is a lie
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
-- Output: "read committed"  ← PostgreSQL upgraded it silently!

-- Check if your transaction is SERIALIZABLE:
SELECT current_setting('transaction_isolation');

-- Monitor serialization failures in production:
SELECT count(*) FROM pg_stat_database
WHERE datname = current_database();
-- (Check your app logs for SQLSTATE 40001)

-- MySQL: check your default
SELECT @@transaction_isolation;
-- Output: REPEATABLE-READ  (note the hyphens, not spaces)

-- MySQL: actually allows dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Now you can see other transactions' uncommitted work.
-- PostgreSQL would refuse to do this.

-- See InnoDB lock waits:
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- See gap locks specifically:
SELECT * FROM performance_schema.data_locks
WHERE LOCK_TYPE = 'RECORD' AND LOCK_MODE LIKE '%GAP%';

-- SQL Server: the famous NOLOCK hint (dirty reads)
SELECT * FROM orders WITH (NOLOCK) WHERE status = 'pending';
-- Fast, but you might read rows that are mid-update and will be rolled back.
-- Equivalent to READ UNCOMMITTED for this table only.

-- Enable snapshot isolation (database-level, one-time):
ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Use it in a transaction:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM orders;  -- sees a consistent snapshot, no locks
COMMIT;

-- Better option for most apps: MVCC for READ COMMITTED:
ALTER DATABASE mydb SET READ_COMMITTED_SNAPSHOT ON;
-- Now all READ COMMITTED queries use MVCC (row versioning).
-- No code changes needed. Readers stop blocking writers.

Theory is nice. But what actually happens when you have millions of transactions per second, real money on the line, and an on-call engineer at 3 AM? Here's how four companies with very different workloads chose very different locking strategies — and why each choice was right for their situation.

Every production incident teaches something. Sometimes the lesson is "do this." More often, the lesson is "never do that again." These three anti-patterns show up repeatedly in codebases, Stack Overflow answers, and incident post-mortems. Learn them here so you don't learn them at 3 AM.

# ❌ BAD: Setting SERIALIZABLE globally for everything
# In Django settings.py:
DATABASES = {
    'default': {
        'OPTIONS': {
            'options': '-c default_transaction_isolation=serializable'
        }
    }
}
# Now your blog post listing page, your search page, your static content
# page — ALL run at SERIALIZABLE. Every read-only query participates
# in SSI tracking. Retries everywhere. Throughput drops 30-50%.

# ✅ GOOD: Use SERIALIZABLE only where it matters
# Keep the default at READ COMMITTED (PostgreSQL's default).
# Upgrade to SERIALIZABLE only for critical operations:

def process_payment(conn, user_id, amount):
    """Only payment transactions need SERIALIZABLE."""
    for attempt in range(3):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute(
                        "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
                    )
                    # ... payment logic ...
                    return "Payment successful"
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            time.sleep(0.01 * (2 ** attempt))
    raise Exception("Payment failed after retries")

# Regular reads stay at READ COMMITTED — fast, no retries needed.
def list_products(conn):
    """No special isolation needed for browsing."""
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM products WHERE active = true")
        return cur.fetchall()

-- ❌ BAD: Locking rows you're only reading
BEGIN;
SELECT * FROM products WHERE category = 'electronics' FOR UPDATE;
-- Locked 5,000 rows! Any other transaction trying to UPDATE
-- any of these rows must wait until we COMMIT.

-- Meanwhile, we're just building a product listing page...
-- We never UPDATE these rows.
COMMIT;  -- Finally releases 5,000 row locks.
-- Everyone waiting on those rows can now proceed.

-- ✅ GOOD: Only lock what you intend to update
-- For read-only queries, no lock needed:
SELECT * FROM products WHERE category = 'electronics';
-- MVCC handles this. No locks. Readers never block writers.

-- For read-then-update (like buying a ticket), lock JUST that row:
BEGIN;
SELECT * FROM tickets WHERE id = 1 FOR UPDATE;
-- Lock ONE row that we WILL update.
UPDATE tickets SET available = available - 1 WHERE id = 1;
COMMIT;

-- Even better for high concurrency: SKIP LOCKED
SELECT * FROM seats WHERE available = true
LIMIT 1 FOR UPDATE SKIP LOCKED;

# ❌ BAD: Treating deadlocks as fatal errors
try:
    cursor.execute("UPDATE accounts SET balance = ...")
except psycopg2.errors.DeadlockDetected:
    logger.error("CRITICAL: Deadlock! Something is very wrong!")
    send_pagerduty_alert("DATABASE DEADLOCK - WAKE UP")
    raise  # Crash the request, show error to user

# This turns a routine database event into a P0 incident.

# ✅ GOOD: Deadlocks are expected — just retry
def with_retry(conn, func, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    return func(cur)
        except psycopg2.errors.DeadlockDetected:
            conn.rollback()
            logger.info(f"Deadlock on attempt {attempt + 1}, retrying...")
            time.sleep(0.01 * (2 ** attempt))
    logger.warning("Transaction failed after max retries")
    raise Exception("Transaction failed — please try again")

# Monitor deadlock FREQUENCY, not individual deadlocks.
# A few per hour = normal. Hundreds per minute = investigate.

# Minimize deadlocks (but don't expect zero):
# 1. Lock rows in consistent order (ascending PK)
# 2. Keep transactions short (less time holding locks)
# 3. SET deadlock_timeout = '1s'; (detect faster)

These aren't hypothetical. Every single one of these mistakes has caused real production incidents at real companies. The scary part: most of them are silent. Your app works fine in development, passes all tests, then starts producing wrong numbers under production traffic. Here are six mistakes that catch even experienced engineers off guard.

-- ALWAYS check your database's default before writing transactions
-- PostgreSQL:
SHOW transaction_isolation;       -- read committed

-- MySQL:
SELECT @@transaction_isolation;   -- REPEATABLE-READ

-- Rule: never assume. Check. Then decide if the default
-- is safe enough for YOUR specific query.

import psycopg2

def run_with_retry(conn, query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    conn.set_session(
                        isolation_level='SERIALIZABLE'
                    )
                    cur.execute(query, params)
                    return cur.fetchall()
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            if attempt == max_retries - 1:
                raise
            # Exponential backoff before retry
            time.sleep(0.01 * (2 ** attempt))

# Key: catch SerializationFailure (40001), NOT generic Exception

-- Find who's blocking whom RIGHT NOW
SELECT
    blocked.pid     AS blocked_pid,
    blocked.query   AS blocked_query,
    blocking.pid    AS blocking_pid,
    blocking.query  AS blocking_query,
    NOW() - blocked.query_start AS waiting_time
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.pid != blocked.pid
    AND gl.locktype = bl.locktype
    AND gl.database IS NOT DISTINCT FROM bl.database
    AND gl.relation IS NOT DISTINCT FROM bl.relation
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
WHERE NOT bl.granted
ORDER BY waiting_time DESC;

-- Check current setting
SHOW deadlock_timeout;  -- default: 1s

-- For high-concurrency OLTP, consider lowering it
ALTER SYSTEM SET deadlock_timeout = '200ms';
SELECT pg_reload_conf();

-- Trade-off: lower timeout = faster deadlock detection
-- but more CPU spent on deadlock checking.
-- 200ms is a good starting point for busy OLTP systems.

-- ❌ BAD: NOLOCK on financial data
SELECT SUM(balance) FROM accounts WITH (NOLOCK);
-- Can read mid-transfer balances: money appears to vanish or duplicate

-- ✅ BETTER: Use READ COMMITTED SNAPSHOT (SQL Server's MVCC equivalent)
ALTER DATABASE MyApp SET READ_COMMITTED_SNAPSHOT ON;
-- Now reads get a consistent snapshot without blocking writers
-- No NOLOCK needed, no dirty reads, no page-split weirdness

-- NOLOCK is ONLY acceptable for:
-- 1. Rough estimates (dashboard "approximately 1M users")
-- 2. Data that never changes (lookup tables, enum references)
-- NEVER use it for financial, inventory, or booking data.

This is a classic system design interview question because it tests whether you understand the trade-offs between correctness and performance. There's no single right answer — the interviewer wants to see you reason through options, identify failure modes, and pick the right tool for each sub-problem.

BEGIN;
-- Lock the specific seat row
SELECT * FROM seats WHERE id = 42 AND status = 'available' FOR UPDATE;
-- If we got the row, mark it as booked
UPDATE seats SET status = 'booked', user_id = 7 WHERE id = 42;
COMMIT;

-- Browsing: no locks at all, just READ COMMITTED
SELECT id, status FROM seats WHERE event_id = 1;

-- Booking: optimistic locking
UPDATE seats
SET status = 'booked', user_id = 7, version = version + 1
WHERE id = 42 AND status = 'available' AND version = 3;
-- If 0 rows updated → seat was taken, show "sorry" immediately

-- TIER 1: Browsing — no locks, cached reads, eventual consistency OK
SELECT id, status FROM seats WHERE event_id = 1;
-- Served from a read replica or Redis cache. Slight staleness is fine.

-- TIER 2: Seat assignment — SKIP LOCKED (no waiting, no queueing)
BEGIN;
SELECT id FROM seats
WHERE event_id = 1 AND status = 'available'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Got a seat? Reserve it with a 5-minute TTL
UPDATE seats SET status = 'reserved', user_id = 7,
    reserved_until = NOW() + INTERVAL '5 minutes'
WHERE id = <selected_id>;
COMMIT;

-- TIER 3: Payment — SERIALIZABLE (correctness over speed)
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE seats SET status = 'booked'
WHERE id = <selected_id> AND user_id = 7
    AND status = 'reserved'
    AND reserved_until > NOW();
-- Process payment via Stripe (idempotency key!)
COMMIT;
-- On serialization failure: retry with backoff
-- On payment failure: seat auto-expires via reserved_until TTL

Reading about isolation levels is one thing. Seeing them break is what actually builds intuition. Every exercise below uses two terminal windows so you can watch the conflict happen in real time. You'll need a local PostgreSQL or MySQL instance — don't run these on production.

-- Setup
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO accounts VALUES (1, 1000.00);

-- Terminal A:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- DON'T commit yet!

-- Terminal B (while A is still open):
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Result: 0.00 ← dirty read! A hasn't committed!

-- Terminal A:
ROLLBACK;  -- balance goes back to 1000.00
-- But B already "saw" 0.00 and may have acted on it

-- Setup
CREATE TABLE orders (id SERIAL PRIMARY KEY, status TEXT);
INSERT INTO orders (status) VALUES ('pending'), ('pending'), ('pending');

-- Terminal A (READ COMMITTED — the default):
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Result: 3

-- Terminal B:
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Terminal A (same transaction):
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Result: 4 ← PHANTOM! A new row appeared mid-transaction!
COMMIT;

-- Now try REPEATABLE READ:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- (Terminal B inserts another row and commits)
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Same count! PostgreSQL's snapshot isolates you from phantoms.

#!/bin/bash
# Setup
psql mydb -c "DROP TABLE IF EXISTS tickets;"
psql mydb -c "CREATE TABLE tickets (id SERIAL PRIMARY KEY, status TEXT DEFAULT 'available');"
psql mydb -c "INSERT INTO tickets (status) SELECT 'available' FROM generate_series(1, 100);"

# Test 1: FOR UPDATE (queue-style)
cat > /tmp/for-update.sql <<'EOF'
BEGIN;
SELECT id FROM tickets WHERE status = 'available' ORDER BY id LIMIT 1 FOR UPDATE;
UPDATE tickets SET status = 'sold' WHERE id = :id;
COMMIT;
EOF
psql mydb -c "UPDATE tickets SET status = 'available';"
pgbench -c 50 -j 4 -t 100 -f /tmp/for-update.sql mydb

# Test 2: SKIP LOCKED (skip-style)
cat > /tmp/skip-locked.sql <<'EOF'
BEGIN;
SELECT id FROM tickets WHERE status = 'available' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE tickets SET status = 'sold' WHERE id = :id;
COMMIT;
EOF
psql mydb -c "UPDATE tickets SET status = 'available';"
pgbench -c 50 -j 4 -t 100 -f /tmp/skip-locked.sql mydb

# Compare the "tps" (transactions per second) output
# SKIP LOCKED will be significantly faster under contention

import psycopg2, time

def buy_product(conn, product_id):
    max_retries = 3
    for attempt in range(max_retries):
        with conn.cursor() as cur:
            # Step 1: Read current state (no locks)
            cur.execute(
                "SELECT stock, version FROM products WHERE id = %s",
                (product_id,)
            )
            stock, version = cur.fetchone()
            print(f"  Read: stock={stock}, version={version}")

            if stock <= 0:
                print("  Out of stock!")
                return False

            # Step 2: Simulate processing time
            time.sleep(2)

            # Step 3: Write with version check
            cur.execute(
                """UPDATE products
                   SET stock = stock - 1, version = version + 1
                   WHERE id = %s AND version = %s""",
                (product_id, version)
            )
            conn.commit()

            if cur.rowcount == 1:
                print(f"  ✓ Purchased! New version={version + 1}")
                return True
            else:
                print(f"  ✗ Version conflict (attempt {attempt + 1})")
                # Re-read and retry

    print("  Failed after max retries")
    return False

# Run this in two separate terminals simultaneously:
conn = psycopg2.connect("dbname=mydb")
conn.autocommit = False
buy_product(conn, 1)

-- Setup
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(10,2));
INSERT INTO accounts VALUES (1, 500.00), (2, 500.00);

-- ❌ CAUSES DEADLOCK (opposite lock order):
-- Terminal 1:                    Terminal 2:
-- BEGIN;                         BEGIN;
-- UPDATE accounts SET            UPDATE accounts SET
--   balance = balance - 10       balance = balance - 10
--   WHERE id = 1;  ← locks 1    WHERE id = 2;  ← locks 2
-- UPDATE accounts SET            UPDATE accounts SET
--   balance = balance + 10       balance = balance + 10
--   WHERE id = 2;  ← WAITS      WHERE id = 1;  ← DEADLOCK!

-- ✅ FIX: Always lock in ascending ID order
-- Terminal 1:                    Terminal 2:
-- BEGIN;                         BEGIN;
-- UPDATE ... WHERE id = 1;       UPDATE ... WHERE id = 1; ← waits
-- UPDATE ... WHERE id = 2;       (resumes after T1 commits)
-- COMMIT;                        UPDATE ... WHERE id = 2;
--                                COMMIT;

Isolation levels and locking are one piece of the database puzzle. Here's how they connect to the bigger picture of system design and data architecture.