SQL transactions in practice: isolation levels, deadlocks, and race conditions

-- Session A                          -- Session B
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1;          -- 100
                                      UPDATE accounts SET balance = 200
                                        WHERE id = 1;
                                      COMMIT;
SELECT balance FROM accounts
  WHERE id = 1;          -- 200 under Read Committed
                         -- 100 under Repeatable Read
COMMIT;

-- Both sessions run this application logic concurrently
BEGIN;
SELECT stock FROM products WHERE id = 9;   -- both read 10
-- application computes 10 - 1 = 9
UPDATE products SET stock = 9 WHERE id = 9;
COMMIT;

-- Two units sold. Stock is 9, not 8.

-- Rule: at least one doctor must remain on call.
-- Andy and Brad both try to go off call at the same moment.

-- Session A                              -- Session B
BEGIN;                                    BEGIN;
SELECT count(*) FROM doctors
  WHERE oncall = true;      -- 2
                                          SELECT count(*) FROM doctors
                                            WHERE oncall = true;   -- 2
-- 2 > 1, so it is safe for me to leave
UPDATE doctors SET oncall = false
  WHERE name = 'Andy';
                                          -- 2 > 1, so it is safe for me to leave
                                          UPDATE doctors SET oncall = false
                                            WHERE name = 'Brad';
COMMIT;                                   COMMIT;

-- Nobody is on call. Both transactions were individually correct.

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- … your work …
COMMIT;

-- On conflict:
-- ERROR:  could not serialize access due to read/write dependencies
--         among transactions
-- SQLSTATE: 40001

-- The difference that surprises people most, at Repeatable Read

-- Session A                          -- Session B
BEGIN;                                BEGIN;
UPDATE list SET x = x - 1;
                                      SELECT * FROM list;  -- sees old snapshot
                                      DELETE FROM list WHERE x = 4;  -- blocks
COMMIT;
                                      -- PostgreSQL: ERROR 40001,
                                      --   could not serialize access
                                      --   due to concurrent update
                                      -- MySQL: no error, but the row is
                                      --   not deleted - it still looks
                                      --   present to this transaction

-- Not this: read, compute in the app, write back
SELECT stock FROM products WHERE id = 9;
UPDATE products SET stock = 9 WHERE id = 9;

-- This: one atomic statement, with the guard in the WHERE clause
UPDATE products
SET stock = stock - 1
WHERE id = 9 AND stock >= 1
RETURNING stock;

-- Zero rows returned means the guard failed. Check the row count.

BEGIN;
SELECT stock FROM products WHERE id = 9 FOR UPDATE;   -- row lock held to commit
-- concurrent transactions block here
UPDATE products SET stock = $1 WHERE id = 9;
COMMIT;

-- Add a version column
ALTER TABLE products ADD COLUMN version integer NOT NULL DEFAULT 0;

-- Read it with the row
SELECT stock, version FROM products WHERE id = 9;   -- stock 10, version 7

-- Write only if nobody else has
UPDATE products
SET stock = 9, version = 8
WHERE id = 9 AND version = 7;

-- 0 rows updated = someone else won. Re-read and retry, or tell the user.

-- Uniqueness enforced in application code has a race window.
-- A constraint does not.
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);

-- Non-overlapping bookings, enforced by the database
ALTER TABLE room_bookings
  ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (room_id WITH =, period WITH &&);

-- Option 1: Serializable, and let SSI detect it
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE oncall = true;
UPDATE doctors SET oncall = false WHERE name = 'Andy';
COMMIT;   -- one of the two transactions gets 40001

-- Option 2: lock the rows you READ, not just the ones you write
BEGIN;
SELECT count(*) FROM doctors WHERE oncall = true FOR UPDATE;
UPDATE doctors SET oncall = false WHERE name = 'Andy';
COMMIT;   -- the second transaction now blocks on the same rows

-- Option 3: give the invariant a row of its own to contend on
BEGIN;
UPDATE shift_invariants SET oncall_count = oncall_count - 1
WHERE shift_id = $1 AND oncall_count > 1;
-- 0 rows means the rule would be violated. Abort.
COMMIT;

-- Session A                          -- Session B
BEGIN;                                BEGIN;
UPDATE accounts SET balance =
  balance - 100 WHERE id = 1;
                                      UPDATE accounts SET balance =
                                        balance - 50 WHERE id = 2;
UPDATE accounts SET balance =
  balance + 100 WHERE id = 2;  -- waits for B
                                      UPDATE accounts SET balance =
                                        balance + 50 WHERE id = 1;  -- waits for A

-- Deadlock. One transaction is aborted:
-- PostgreSQL: ERROR 40P01  deadlock detected
-- MySQL:      ERROR 1213   Deadlock found when trying to get lock

-- Consistent lock ordering, in one statement
SELECT * FROM accounts
WHERE id = ANY($1)
ORDER BY id
FOR UPDATE;

-- Bound the wait
SET lock_timeout = '3s';
SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';

-- PostgreSQL: log every deadlock with both queries involved
SET log_lock_waits = on;
SET deadlock_timeout = '1s';   -- how long to wait before running detection

-- What is blocking what, right now
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- MySQL: the most recent deadlock, in full
SHOW ENGINE INNODB STATUS;

const RETRYABLE = new Set([
  '40001',   // serialization_failure
  '40P01',   // deadlock_detected
])

async function withRetry<T>(
  fn: (tx: Tx) => Promise<T>,
  maxAttempts = 5,
): Promise<T> {
  for (let attempt = 0; ; attempt++) {
    try {
      return await db.transaction({ isolationLevel: 'serializable' }, fn)
    } catch (err) {
      if (!RETRYABLE.has(err.code) || attempt >= maxAttempts - 1) throw err

      // Full jitter, so retrying transactions do not collide again
      const base = Math.min(10 * 2 ** attempt, 500)
      await sleep(Math.random() * base)
      metrics.increment('tx.retry', { code: err.code })
    }
  }
}

-- Find them before they find you
SELECT pid, state, now() - xact_start AS duration, left(query, 80)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start < now() - interval '1 minute'
ORDER BY xact_start;