Core idea: Don't change the default isolation level globally.
Use `READ COMMITTED` (default) + the right lock technique per situation.
This gives you safety where needed without sacrificing performance everywhere.

1. 1. Golden Rules
2. 2. Dirty Read — Already Solved by Default
3. 3. Non-repeatable Read — Use SELECT FOR SHARE
4. 4. Phantom Read — Use SELECT FOR SHARE + Range Lock
5. 5. Lost Update — Use Atomic Update or SELECT FOR UPDATE
6. 6. Write Skew — Use SELECT FOR UPDATE on All Involved Rows
7. 7. Quick Reference

Rule 1 — Keep READ COMMITTED as the default for everything.
Rule 2 — Add SELECT FOR UPDATE only where race conditions can occur.
Rule 3 — Use atomic updates (SET col = col + n) for simple counters.
Rule 4 — Use UNIQUE constraints to enforce uniqueness at the database level.
Rule 5 — Only raise to SERIALIZABLE for genuinely complex invariants.
Rule 6 — Always lock rows in a consistent order to avoid deadlocks.
Rule 7 — Always wrap transactions in try/catch and rollback on error.

Transaction A reads data that Transaction B modified but has not yet committed. If B rolls back, A was working with a value that never officially existed.

balance = 100

Txn B:  UPDATE balance = 200  ← not committed
Txn A:  SELECT balance        → reads 200  ← DIRTY
Txn B:  ROLLBACK              → balance back to 100
Txn A:  already used 200 — WRONG

Both PostgreSQL and MySQL default to an isolation level that prevents dirty reads:

At `READ COMMITTED`, Transaction A can only see committed data. B's uncommitted change stays invisible to everyone else until B commits.

-- PostgreSQL / SQL Server: already safe, no changes needed
 
BEGIN;
  SELECT balance FROM accounts WHERE id = 1;
  -- Always returns the last committed value
  -- B's uncommitted changes are never visible here
COMMIT;

Never use READ UNCOMMITTED in production.
It is the only isolation level where dirty reads can happen.

-- NEVER do this in production
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Within the same transaction, reading the same row twice returns different values because another transaction updated and committed that row in between.

balance = 1000

Txn A:  SELECT balance  →  1000
Txn B:  UPDATE balance = 500, COMMIT
Txn A:  SELECT balance  →  500   ← different result, same transaction!

Real scenario: A generates an audit report — reads balance twice to cross-check. The two reads disagree because someone transferred money in between.

At `READ COMMITTED`, each `SELECT` statement gets a fresh snapshot of committed data. So the second `SELECT` sees B's committed update — result differs from the first read.

Lock the row on first read so no one can update it until your transaction finishes.

BEGIN;
 
  -- Lock the row for reading — others can still read, but cannot update
  SELECT balance
  FROM accounts
  WHERE id = 1
  FOR SHARE;                    -- ← key addition
  -- → 1000
 
  -- Txn B tries UPDATE balance = 500
  -- → B is BLOCKED here, must wait for A to finish
 
  -- ... do calculations, generate report ...
 
  SELECT balance
  FROM accounts
  WHERE id = 1
  FOR SHARE;
  -- → still 1000  ✓  (B could not commit while A held the share lock)
 
COMMIT;
-- B is now unblocked and can proceed

-- Use FOR SHARE when you only READ and want consistency
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
 
-- Use FOR UPDATE when you READ then WRITE
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

BEGIN;
 
  -- Lock all accounts being audited
  SELECT id, balance
  FROM accounts
  WHERE department = 'engineering'
  FOR SHARE;
 
  -- Calculate total from first read
  -- ... application logic ...
 
  -- Read again to verify — guaranteed same values
  SELECT id, balance
  FROM accounts
  WHERE department = 'engineering'
  FOR SHARE;
  -- All balances identical to first read ✓
 
COMMIT;

Within the same transaction, running the same query twice returns a different number of rows because another transaction inserted or deleted a matching row in between.

orders table: 3 rows with amount > 100

Txn A:  SELECT COUNT(*) WHERE amount > 100  →  3
Txn B:  INSERT order amount=500, COMMIT
Txn A:  SELECT COUNT(*) WHERE amount > 100  →  4  ← phantom row appeared!

Real scenario: A calculates the total value of large orders for a report. Between two reads, a new large order is inserted — the totals no longer match.

Each `SELECT` sees a fresh snapshot. A newly committed row from B becomes visible on A's second `SELECT` even though A's transaction started before B's insert.

Lock the range of rows matching your condition. No one can insert into that range while your transaction holds the lock.

BEGIN;
 
  -- Lock all rows matching the condition
  SELECT COUNT(*), SUM(amount)
  FROM orders
  WHERE amount > 100
  FOR SHARE;                    -- ← locks the range
  -- → 3 rows, total = 1500
 
  -- Txn B tries to INSERT order amount=500
  -- → B is BLOCKED, cannot insert into this range
 
  -- ... application logic ...
 
  SELECT COUNT(*), SUM(amount)
  FROM orders
  WHERE amount > 100
  FOR SHARE;
  -- → still 3 rows, total = 1500  ✓
 
COMMIT;
-- B is now unblocked and inserts successfully

-- Without an index, the database may lock the ENTIRE table
-- instead of just the matching range — very bad for performance
 
-- Always ensure an index exists on the column used in WHERE
CREATE INDEX idx_orders_amount ON orders(amount);
 
-- Now FOR SHARE only locks rows where amount > 100
-- Not the whole table

BEGIN;
 
  -- Lock the exact range needed for the report
  SELECT
    category,
    COUNT(*)       AS order_count,
    SUM(amount)    AS total_amount
  FROM orders
  WHERE
    created_at BETWEEN '2026-01-01' AND '2026-03-31'
    AND STATUS = 'completed'
  FOR SHARE;
  -- → consistent results, no phantom rows can appear
 
COMMIT;

Two transactions both read the same value, both modify it, and one overwrites the other. The first update is silently lost.

qty = 10

Txn A:  read qty = 10
Txn B:  read qty = 10
Txn B:  write qty = 10 - 3 = 7,  COMMIT
Txn A:  write qty = 10 - 2 = 8,  COMMIT   ← overwrites B's result!

Final:  qty = 8   (should be 5)   ← B's deduction of 3 is LOST

Real scenarios: Inventory deduction, bank transfers, like/view counters.

Combine read + calculate + write into one SQL statement. The database handles it atomically — no race condition possible.

-- WRONG: read in application, calculate, then write back
-- (race condition between read and write)
current_qty = SELECT qty FROM products WHERE id = 1;   -- 10
UPDATE products SET qty = current_qty - 2 WHERE id = 1;
 
-- CORRECT: let the database do it atomically
UPDATE products
SET qty = qty - 2
WHERE id = 1;
-- Database reads, subtracts, and writes in one atomic step
-- No other transaction can interfere in between

-- More examples of atomic updates
 
-- Counter: increment page views
UPDATE pages SET views = views + 1 WHERE id = 42;
 
-- Balance: add interest
UPDATE accounts SET balance = balance * 1.05 WHERE TYPE = 'savings';
 
-- Stock: reserve items
UPDATE products SET reserved = reserved + 1 WHERE id = 7;

• Simple arithmetic only (add, subtract, multiply)
• No need to check the current value before writing

• Need to check balance before deducting (e.g. “is there enough stock?”)
• Need the current value in application logic before writing

Lock the row on read so no other transaction can touch it until you commit.

BEGIN;
 
  -- Step 1: read AND lock the row
  SELECT qty
  FROM products
  WHERE id = 1
  FOR UPDATE;                   -- ← row is now locked
  -- → qty = 10
 
  -- Step 2: check business rule in application code
  IF qty < 2 THEN
    ROLLBACK;
    RAISE 'Insufficient stock';
  END IF;
 
  -- Step 3: write safely — no one else could have changed qty
  UPDATE products
  SET qty = qty - 2
  WHERE id = 1;
 
COMMIT;
-- Lock released — other transactions can now proceed

Txn A:  SELECT qty FOR UPDATE  →  locks row, reads 10
Txn B:  SELECT qty FOR UPDATE  →  BLOCKED, waiting for A

Txn A:  UPDATE qty = 8, COMMIT
        lock released

Txn B:  unblocked, reads qty = 8  (A's result)
Txn B:  UPDATE qty = 5, COMMIT   ✓  correct result

-- Transfer $100 from account 1 to account 2
BEGIN;
 
  -- Lock BOTH accounts before reading
  -- Always lock in ascending ID order to prevent deadlocks
  SELECT id, balance
  FROM accounts
  WHERE id IN (1, 2)
  ORDER BY id                   -- ← consistent lock order, prevents deadlock
  FOR UPDATE;
 
  -- Check sender has enough balance (in application code)
  -- IF balance_of_1 < 100 THEN ROLLBACK
 
  -- Deduct from sender
  UPDATE accounts
  SET balance = balance - 100
  WHERE id = 1;
 
  -- Add to receiver
  UPDATE accounts
  SET balance = balance + 100
  WHERE id = 2;
 
COMMIT;

-- RISK OF DEADLOCK: inconsistent order
-- Txn A locks account 1, then needs account 2
-- Txn B locks account 2, then needs account 1
-- → both wait forever
 
-- SAFE: always lock by ascending ID
SELECT id, balance FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

No real lock — add a `version` column. When writing, check that version hasn't changed. If it has, someone else updated first — retry.

-- Schema
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
 
-- Step 1: read with version
SELECT qty, version FROM products WHERE id = 1;
-- → qty=10, version=5
 
-- Step 2: write with version check
UPDATE products
SET
  qty     = qty - 2,
  version = version + 1
WHERE
  id      = 1
  AND version = 5;
 
-- Check rows affected:
--   1 row → success
--   0 rows → conflict, someone else updated first → retry from step 1

• Conflicts are rare (most updates succeed on the first try)
• High-concurrency reads, occasional writes
• REST APIs, user profile updates, document editing

Do you need to check the current value before writing?
    │
    ├── No  →  Use ATOMIC UPDATE
    │          UPDATE table SET col = col + n WHERE id = x
    │
    └── Yes →  Are conflicts frequent (many users, same row)?
                   │
                   ├── Yes  →  Use SELECT FOR UPDATE
                   │
                   └── No   →  Use OPTIMISTIC LOCK (version column)

Two transactions both read a shared condition, both decide it is safe to proceed, and both write to different rows.

Rule: at least 1 doctor must be on call

Alice on_call=true, Bob on_call=true  →  count = 2

Txn A (Alice):  SELECT COUNT(*) on_call=true  →  2
Txn B (Bob):    SELECT COUNT(*) on_call=true  →  2

Txn A: UPDATE Alice on_call=false
Txn B: UPDATE Bob  on_call=false

Final: count = 0  ← RULE VIOLATED

BEGIN;
 
  SELECT id, name
  FROM doctors
  WHERE on_call = TRUE
  FOR UPDATE;
 
  UPDATE doctors
  SET on_call = FALSE
  WHERE id = 1;
 
COMMIT;

ALTER TABLE bookings
ADD CONSTRAINT uq_room_slot UNIQUE (room_id, slot);

-- Read consistently
SELECT ... FOR SHARE;
 
-- Read then write
SELECT ... FOR UPDATE;
 
-- Atomic write
UPDATE TABLE SET col = col + n WHERE id = x;
 
-- Prevent duplicate inserts
ALTER TABLE t ADD CONSTRAINT uq_name UNIQUE (col1, col2);

✓ Always lock multiple rows in the same order
✓ Keep transactions short
✓ Do not lock more rows than necessary
✓ Do not perform external calls inside a transaction
✓ Always retry on deadlock/serialization errors

BEGIN;
 
  -- 1. Lock what you need
  SELECT col FROM TABLE WHERE id = x FOR UPDATE;
 
  -- 2. Check business rules
 
  -- 3. Write changes
  UPDATE TABLE SET col = new_value WHERE id = x;
 
COMMIT;

def run_transaction(conn, fn):
    MAX_RETRY = 3
    for attempt in range(MAX_RETRY):
        try:
            with conn.cursor() as cur:
                cur.execute("BEGIN")
                result = fn(cur)
                cur.execute("COMMIT")
                return result
        except Exception as e:
            conn.rollback()
 
            if "deadlock" in str(e).lower() or "40001" in str(e):
                continue
 
            raise
 
    raise Exception("Transaction failed after max retries")

async function runTransaction(pool, fn) {
  const MAX_RETRY = 3;
 
  for (let attempt = 0; attempt < MAX_RETRY; attempt++) {
    const client = await pool.connect();
 
    try {
      await client.query('BEGIN');
 
      const result = await fn(client);
 
      await client.query('COMMIT');
 
      return result;
 
    } catch (err) {
 
      await client.query('ROLLBACK');
 
      if (['40P01', '40001'].includes(err.code)) {
        continue;
      }
 
      throw err;
 
    } finally {
      client.release();
    }
  }
 
  throw new Error('Transaction failed after max retries');
}

For background theory, see: SQL Transaction Anomalies, SQL Isolation Levels.