SQL Transactions & ACID

Transactions — Basics

BEGIN;   -- or START TRANSACTION

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;   -- make changes permanent

-- On error:
ROLLBACK;  -- undo everything since BEGIN

Autocommit: most clients run each statement in its own implicit transaction. Explicit BEGIN groups multiple statements.

-- Savepoints — partial rollback within a transaction
BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT before_payment;
INSERT INTO payments VALUES (...);  -- fails
ROLLBACK TO before_payment;         -- undo payment insert, keep order insert
COMMIT;

---

ACID

Atomicity — All or Nothing

Transaction either fully commits or fully rolls back. No partial state persists.

-- If second UPDATE fails (no money), both UPDATE statements are rolled back
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Consistency — Constraints Always Satisfied

DB moves from one valid state to another. Constraints (FK, UNIQUE, CHECK) enforced at commit time.

-- CHECK constraint — enforced transactionally
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- Any transaction that would cause negative balance → rolls back

Isolation — Concurrent Transactions Don't Interfere

Each transaction sees a consistent snapshot. Level of interference depends on isolation level.

Durability — Committed Data Survives Crash

Committed data written to WAL (Write-Ahead Log) before returning success. Survives power failure.

---

Isolation Levels

From least to most isolated:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
...
COMMIT;

Phenomena Explained

Dirty Read — read uncommitted data from another transaction (that might roll back).

T1: UPDATE balance = 1000 (not committed)
T2: SELECT balance → sees 1000 (dirty!)
T1: ROLLBACK

Non-Repeatable Read — same row read twice gives different results (another transaction committed between reads).

T1: SELECT balance → 500
T2: UPDATE balance = 800; COMMIT
T1: SELECT balance → 800 (changed!)

Phantom Read — same query returns different set of rows (another transaction inserted/deleted rows).

T1: SELECT COUNT(*) WHERE status='active' → 10
T2: INSERT new active record; COMMIT
T1: SELECT COUNT(*) WHERE status='active' → 11 (phantom!)

PostgreSQL default: READ COMMITTED. Upgrade to SERIALIZABLE for critical financial logic.

---

Locking

Row-Level Locks

-- SELECT FOR UPDATE — lock rows for modification (pessimistic locking)
BEGIN;
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE;
-- Other transactions trying to SELECT FOR UPDATE on same row → wait
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;

-- SELECT FOR SHARE — allow concurrent reads, block writes
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- NOWAIT — fail immediately if can't acquire lock
SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE NOWAIT;

-- SKIP LOCKED — skip locked rows (queue pattern)
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;

Deadlock

Two transactions each hold a lock the other needs.

T1: locks row A, wants row B
T2: locks row B, wants row A
→ deadlock — DB detects and kills one transaction

Prevention: always acquire locks in the same order across transactions.

-- Consistent order prevents deadlock
BEGIN;
SELECT * FROM accounts WHERE id = MIN(id1, id2) FOR UPDATE;
SELECT * FROM accounts WHERE id = MAX(id1, id2) FOR UPDATE;
...
COMMIT;

---

Optimistic vs Pessimistic Locking

Pessimistic (SELECT FOR UPDATE) — lock on read, prevent conflicts upfront. Good for high-contention rows.

Optimistic — no locks. Read + remember version. On write, check version hasn't changed; retry if it has.

-- Optimistic locking with version column
SELECT id, balance, version FROM accounts WHERE id = 1;
-- balance = 500, version = 3

-- On update, check version matches
UPDATE accounts
SET balance = 400, version = 4
WHERE id = 1 AND version = 3;
-- If 0 rows updated → someone else modified it → retry

---

MVCC (Multi-Version Concurrency Control)

PostgreSQL uses MVCC: each transaction sees a snapshot of the DB at transaction start. Old row versions kept until no transaction needs them (vacuumed by autovacuum).

Benefits:

• Readers don't block writers
• Writers don't block readers
• No read locks needed for normal SELECT

---

Two-Phase Commit (Distributed Transactions)

Coordinate commit across multiple databases.

Phase 1 — Prepare: coordinator asks all participants "can you commit?"
Phase 2 — Commit: if all say yes → coordinator sends COMMIT to all

If any participant fails Phase 1 → coordinator sends ROLLBACK to all

-- PostgreSQL
PREPARE TRANSACTION 'tx_id_123';  -- phase 1
COMMIT PREPARED 'tx_id_123';      -- phase 2
-- or:
ROLLBACK PREPARED 'tx_id_123';

---

Interview Answers

Q: Explain ACID in 4 sentences.

Atomicity: transaction completes fully or not at all — no partial state. Consistency: DB moves from one valid state to another, constraints always hold. Isolation: concurrent transactions don't see each other's in-progress changes. Durability: once committed, changes survive crashes via write-ahead log.

Q: What isolation level does PostgreSQL use by default?

READ COMMITTED — each statement sees the latest committed data at the time it runs. Non-repeatable reads are possible within a transaction.

Q: When would you use SELECT FOR UPDATE?

When you need to read a row and then modify it, and you need to prevent other transactions from modifying it between your read and write. Classic example: inventory decrement, bank transfer.

---

Related

• [[Indexes & Performance]] — locking and index interaction
• [[SQL for Interviews]] — ACID verbal explanation