Transactions, Isolation & Locks: A PostgreSQL and MySQL Guide

-- This is the default, but you can set it explicitly:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
— Each SELECT gets a FRESH snapshot of committed data.
— Between two SELECTs in this txn, another txn can commit changes.
COMMIT;

-- Session 1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- → 10000 (snapshot taken here)
— [Session 2 commits: UPDATE accounts SET balance=3000 WHERE id=1;]
SELECT balance FROM accounts WHERE id = 1;
— → 10000  ← Same snapshot! Non-repeatable read prevented.
— But if Session 1 now tries to UPDATE the same row:
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
— ERROR: could not serialize access due to concurrent update
— PostgreSQL detects that the row it would update was already
— modified by a committed transaction. Safe failure!
ROLLBACK;
— Application must retry the entire transaction.

-- Fixing the on-call doctor write skew from earlier:
— Session 1
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = TRUE;
— → 2 (SSI tracks: “this txn read the on-call set”)
— [Session 2 (also SERIALIZABLE) reads count=2, updates Dr. Sharma → off-call, commits]
UPDATE doctors SET on_call = FALSE WHERE name = ‘Dr. Mehta’;
COMMIT;
— ERROR: could not serialize access due to read/write dependencies
—        among transactions
— DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
— Hospital is saved. Application must retry.

-- 1. Per transaction (most common)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or equivalent:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
— 2. For current session
SET default_transaction_isolation = ‘repeatable read’;
— 3. PostgreSQL global default (postgresql.conf)
— default_transaction_isolation = ‘read committed’
— 4. MySQL equivalent
— SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  — before BEGIN
— SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- STRONGEST: Exclusive lock
-- Blocks: all other FOR UPDATE/SHARE/NO KEY UPDATE/KEY SHARE, UPDATE, DELETE
-- Does NOT block: plain SELECT (reads without locks)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
— Weaker exclusive (good for non-PK updates)
— Blocks: FOR UPDATE, FOR SHARE, DELETE, updates that change primary key
— Allows: FOR KEY SHARE (FK checks can still proceed)
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;
— Shared lock (I’m reading this; no one may write)
— Blocks: FOR UPDATE, FOR NO KEY UPDATE, UPDATE, DELETE
— Allows: other FOR SHARE and FOR KEY SHARE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
— WEAKEST: For FK integrity checks
— Blocks: FOR UPDATE and DELETE only (key-changing operations)
— Allows: everything else including other writes
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;

CREATE OR REPLACE FUNCTION transfer_funds(
p_from_id   INT,
p_to_id     INT,
p_amount    NUMERIC
) RETURNS VOID AS $$
DECLARE
v_balance NUMERIC;
BEGIN
-- Always lock in a consistent order (lower id first) to avoid deadlocks
SELECT balance INTO v_balance
FROM accounts
WHERE id = p_from_id
FOR UPDATE;  -- exclusive lock acquired here
IF v_balance < p_amount THEN
RAISE EXCEPTION ‘Insufficient funds: % available, % requested’,
v_balance, p_amount;
END IF;
UPDATE accounts
SET balance = balance - p_amount
WHERE id = p_from_id;
UPDATE accounts
SET balance = balance + p_amount
WHERE id = p_to_id;
END;
$$ LANGUAGE plpgsql;
— Usage
BEGIN;
SELECT transfer_funds(1, 2, 2000.00);
COMMIT;

-- NOWAIT: fail immediately if row is locked
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"
-- Application can immediately try another row or strategy.
— SKIP LOCKED: useful for job queues
— Multiple workers pick up different jobs without contention
CREATE TABLE jobs (
id       SERIAL PRIMARY KEY,
payload  JSONB,
status   TEXT DEFAULT ‘pending’
);
— Worker process (run this in multiple concurrent sessions)
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = ‘pending’
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
— Each worker atomically picks a different job.
— No polling, no duplicate processing, no waiting.
UPDATE jobs SET status = ‘processing’ WHERE id = <returned_id>;
COMMIT;

-- 1. Exclusive row lock (blocks all other writers and lockers)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock is now HELD on row id=1
-- It will be released automatically on COMMIT or ROLLBACK
— 2. Shared row lock (others can read but not write)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
— 3. Exclusive lock without blocking FK checks
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;
— 4. Weakest — only blocks key-changing operations
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;
— 5. Lock multiple rows at once
BEGIN;
SELECT * FROM accounts
WHERE owner = ‘Alice’
FOR UPDATE;  — locks ALL matching rows simultaneously
— 6. Lock rows from multiple tables in one query (PostgreSQL)
BEGIN;
SELECT a.id, o.amount
FROM accounts a
JOIN orders o ON o.account_id = a.id
WHERE a.id = 1
FOR UPDATE OF a          — only lock rows in accounts table
FOR SHARE OF o;           — shared lock on orders rows

-- Row-level locks CANNOT be released mid-transaction.
-- They are held until the transaction ends.
— Release via COMMIT (success path)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
— … do your work …
COMMIT;  — ← lock released here
— Release via ROLLBACK (abort path)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
— something goes wrong…
ROLLBACK;  — ← lock also released here
— SAVEPOINT: partial rollback releases locks on rolled-back work
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  — held
SAVEPOINT sp1;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  — held
ROLLBACK TO SAVEPOINT sp1;  — releases lock on id=2, keeps lock on id=1
COMMIT;  — releases lock on id=1

-- Use table locks sparingly — they block the entire table.
-- Most use cases are better served by row-level locks.
— ACCESS SHARE: taken automatically by plain SELECT
— Blocks only: ACCESS EXCLUSIVE (e.g. DROP TABLE)
BEGIN;
LOCK TABLE accounts IN ACCESS SHARE MODE;
— ROW SHARE: taken automatically by SELECT FOR UPDATE/FOR SHARE
BEGIN;
LOCK TABLE accounts IN ROW SHARE MODE;
— SHARE: prevents ALL writes; allows concurrent reads
— Use case: “freeze” a table while rebuilding an index manually
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
— SHARE ROW EXCLUSIVE: prevents writes + other SHARE locks
BEGIN;
LOCK TABLE accounts IN SHARE ROW EXCLUSIVE MODE;
— EXCLUSIVE: allows only reads (ACCESS SHARE)
BEGIN;
LOCK TABLE accounts IN EXCLUSIVE MODE;
— ACCESS EXCLUSIVE: the nuclear option — blocks everything
— Automatically taken by: ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL
— Use case: when you need exclusive control for schema migrations
BEGIN;
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;
— No other transaction can read OR write this table until COMMIT
— MySQL equivalent:
— LOCK TABLES accounts READ;   — shared
— LOCK TABLES accounts WRITE;  — exclusive
— UNLOCK TABLES;               — manual release (MySQL only)

-- PostgreSQL: set per session or per transaction
SET lock_timeout = '2s';    -- fail if lock not acquired within 2 seconds
SET lock_timeout = '500ms'; -- milliseconds also work
SET lock_timeout = 0;        -- 0 = wait forever (default)
— Apply only for the current transaction:
BEGIN;
SET LOCAL lock_timeout = ‘3s’;  — resets after COMMIT/ROLLBACK
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
— ERROR: canceling statement due to lock timeout  (if locked > 3s)
COMMIT;
— statement_timeout: cancel any query that takes too long (not just locking)
SET statement_timeout = ’10s’;
— Safe migration pattern: try lock, bail fast if table is busy
BEGIN;
SET LOCAL lock_timeout = ‘1s’;
ALTER TABLE accounts ADD COLUMN region TEXT DEFAULT ‘IN’;
COMMIT;
— If the lock isn’t granted in 1s, the migration aborts cleanly
— instead of holding up the entire connection pool.
— MySQL equivalent:
— SET innodb_lock_wait_timeout = 5;  — seconds (default: 50)
— SET innodb_lock_wait_timeout = 0;  — fail immediately if locked

-- Session-scoped: must be explicitly released
SELECT pg_advisory_lock(42);          -- blocks until acquired (exclusive)
SELECT pg_advisory_lock_shared(42);   -- blocks until acquired (shared)
SELECT pg_try_advisory_lock(42);      -- returns true/false immediately
SELECT pg_try_advisory_lock_shared(42); -- non-blocking shared
— RELEASE session advisory locks explicitly:
SELECT pg_advisory_unlock(42);        — release exclusive
SELECT pg_advisory_unlock_shared(42); — release shared
SELECT pg_advisory_unlock_all();       — release ALL session advisory locks
— Transaction-scoped: auto-released on COMMIT or ROLLBACK (preferred)
BEGIN;
SELECT pg_advisory_xact_lock(42);          — exclusive, auto-released
SELECT pg_advisory_xact_lock_shared(42);  — shared, auto-released
SELECT pg_try_advisory_xact_lock(42);     — non-blocking, auto-released
— No explicit unlock needed — COMMIT/ROLLBACK handles it
COMMIT;
— Two-argument variant: namespace your locks to avoid key collisions
— pg_advisory_lock(namespace_int, key_int)
SELECT pg_advisory_lock(1001, 42);  — app_id=1001, resource_id=42
SELECT pg_advisory_unlock(1001, 42);
— See all currently held advisory locks:
SELECT pid, classid, objid, mode, granted
FROM pg_locks
WHERE locktype = ‘advisory’;

-- Plain SELECT takes no row-level locks at all (by design in PG).
-- You cannot "disable" this; it's already the default.
-- Just don't add FOR UPDATE / FOR SHARE if you don't need it.
— Prevent long-running transactions from holding locks:
SET idle_in_transaction_session_timeout = ’30s’;
— Kills sessions that sit idle INSIDE a transaction for > 30s.
— This releases their locks automatically. Critical for production.
— For DDL on large tables: avoid full ACCESS EXCLUSIVE with
— PostgreSQL’s concurrent index build (no table lock held):
CREATE INDEX CONCURRENTLY idx_accounts_owner
ON accounts (owner);
— Takes only ShareUpdateExclusiveLock — reads and writes continue.
— Trade-off: slower, cannot run inside a transaction block.
— MySQL: disable the default gap locking (use with caution)
— SET innodb_locks_unsafe_for_binlog = 1;  — deprecated in 8.0
— Instead, use READ COMMITTED which naturally skips gap locks:
— SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
— Inspect all currently held locks in your PostgreSQL instance:
SELECT
l.pid,
l.locktype,
l.relation::regclass   AS table_name,
l.mode,
l.granted,
a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

CREATE TABLE products (
id       SERIAL PRIMARY KEY,
name     TEXT NOT NULL,
price    NUMERIC(10,2),
stock    INT NOT NULL DEFAULT 0,
version  INT NOT NULL DEFAULT 1  -- optimistic lock version
);
INSERT INTO products (name, price, stock) VALUES (‘Laptop’, 75000, 10);
— Step 1: Read the row and note the version
SELECT id, name, stock, version FROM products WHERE id = 1;
— → id=1, name=‘Laptop’, stock=10, version=1
— Step 2: Application logic (no db connection held)
— User sees stock=10, wants to buy 2 units
— Step 3: Update with version check (the critical step)
UPDATE products
SET
stock   = stock - 2,
version = version + 1      — bump version on every write
WHERE
id      = 1
AND version = 1;           — optimistic check: “has someone else written?”
— Check affected rows in your application:
— rows_affected = 1 → success, proceed
— rows_affected = 0 → conflict! Someone else updated first.
—                     Re-read the row and retry.
— PostgreSQL: use RETURNING to get the new state in one round-trip
UPDATE products
SET    stock = stock - 2, version = version + 1
WHERE  id = 1 AND version = 1
RETURNING id, stock, version;
— If no row returned → conflict detected.

ALTER TABLE products ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW();
— Client reads: id=1, updated_at=‘2024-01-15 10:30:00’
UPDATE products
SET    price = 72000, updated_at = NOW()
WHERE  id = 1
AND    updated_at = ‘2024-01-15 10:30:00’;  — timestamp as version
— Caution: clock resolution (microseconds in PG) makes this
— theoretically weaker than an integer version counter.
— Prefer integer versions in high-concurrency systems.

-- WRONG: Each transaction locks its "from" account first
-- → deadlock when both run simultaneously
— CORRECT: Always lock accounts in ascending ID order
CREATE OR REPLACE FUNCTION safe_transfer(from_id INT, to_id INT, amount NUMERIC)
RETURNS VOID AS $$
DECLARE
first_id  INT := LEAST(from_id, to_id);
second_id INT := GREATEST(from_id, to_id);
BEGIN
— Always acquire locks in ascending order regardless of direction
PERFORM id FROM accounts WHERE id = first_id  FOR UPDATE;
PERFORM id FROM accounts WHERE id = second_id FOR UPDATE;
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$ LANGUAGE plpgsql;
— Now Alice→Bob and Bob→Alice both lock id=1 first, then id=2.
— One waits. No cycle. No deadlock.

-- Find currently waiting transactions
SELECT
pid,
now() - query_start AS wait_duration,
state,
wait_event_type,
wait_event,
LEFT(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY wait_duration DESC;
— See who’s blocking who
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))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
— Configure deadlock timeout (default: 1 second)
— In postgresql.conf:
— deadlock_timeout = 1s
— log_lock_waits = on   ← log if a lock wait exceeds deadlock_timeout

-- Use case: Only one instance of a cron job should run at a time
-- even across multiple app servers connected to the same PostgreSQL.
SELECT pg_try_advisory_lock(12345) AS got_lock;
— → true  : this session owns the lock, proceed
— → false : another session holds it, skip this run
— Transaction-scoped advisory lock (auto-released on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(12345);
— … do your critical work …
COMMIT;
— Lock released automatically.
— Use a hash of meaningful string as the lock key:
SELECT pg_try_advisory_lock(hashtext(‘daily-report-job’));

-- Q: Will conflicts between concurrent transactions be common?
--    YES → pessimistic (FOR UPDATE)
--    NO  → optimistic (version column)
— Q: Do I need a consistent multi-row snapshot for reads?
—    YES → REPEATABLE READ or SERIALIZABLE
—    NO  → READ COMMITTED (default)
— Q: Am I doing read-modify-write on the same row?
—    YES → FOR UPDATE on the initial SELECT
—    NO  → plain SELECT is fine
— Q: Do I need to prevent FK cascades from interfering?
—    YES → FOR KEY SHARE (weaker, allows FK checks to bypass)
—    NO  → FOR UPDATE
— Q: Building a job queue?
—    USE → SELECT … FOR UPDATE SKIP LOCKED LIMIT N
— Q: Need a global mutex (one job at a time, across servers)?
—    USE → pg_try_advisory_lock(key) — PostgreSQL only
— Q: Am I getting deadlocks?
—    FIX → always lock rows in ascending primary key order
—    MONITOR → log_lock_waits = on, pg_blocking_pids()