Transactions & ACID

Difficulty: Intermediate

Question

Explain ACID properties. What are isolation levels and when would you choose each?

Answer

ACID properties guarantee reliable database transactions:

A - Atomicity: All operations in a transaction succeed or all fail (no partial updates) C - Consistency: Transaction brings DB from one valid state to another I - Isolation: Concurrent transactions don't interfere with each other D - Durability: Once committed, data survives crashes

Isolation Levels (from least to most strict): 1. Read Uncommitted: can see uncommitted changes (dirty reads) 2. Read Committed: only sees committed data (default in PostgreSQL) 3. Repeatable Read: same query returns same results within transaction 4. Serializable: transactions execute as if serial (slowest, safest)

Code examples

Transaction: Bank Transfer

-- Without transaction: DANGEROUS
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- What if the server crashes HERE?
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Account 1 lost $100, Account 2 didn't receive it!

-- With transaction: SAFE (atomic)
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  
  -- Check invariant: no negative balances
  DO $
  BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;
  END $;
COMMIT;
-- Both updates succeed or both are rolled back

Atomicity ensures the transfer is all-or-nothing. If anything fails between BEGIN and COMMIT, all changes are rolled back.

Isolation Level Problems

-- Dirty Read (Read Uncommitted)
-- Transaction A: UPDATE balance SET amount = 0 (not committed)
-- Transaction B: SELECT amount → reads 0 (uncommitted!)
-- Transaction A: ROLLBACK
-- Transaction B used incorrect data

-- Non-Repeatable Read (Read Committed)
-- Transaction A: SELECT balance → 100
-- Transaction B: UPDATE balance = 50; COMMIT;
-- Transaction A: SELECT balance → 50 (different!)

-- Phantom Read (Repeatable Read)
-- Transaction A: SELECT COUNT(*) FROM orders WHERE status='pending' → 5
-- Transaction B: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
-- Transaction A: SELECT COUNT(*) → 6 (new row appeared!)

-- Fix: use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  -- Now fully isolated from concurrent changes
  SELECT COUNT(*) FROM orders WHERE status = 'pending';
COMMIT;

Higher isolation levels prevent more anomalies but reduce concurrency. Most applications use Read Committed (the default) and handle conflicts at the application level.

Optimistic vs Pessimistic Locking

-- Pessimistic Locking: lock the row
-- Use when conflicts are likely
BEGIN;
  SELECT * FROM products WHERE id = 1 FOR UPDATE;
  -- Row is locked until COMMIT/ROLLBACK
  -- Other transactions wait
  UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Optimistic Locking: version check
-- Use when conflicts are rare
-- Add a 'version' column to the table

-- Read with version
SELECT id, name, stock, version FROM products WHERE id = 1;
-- Returns: id=1, stock=10, version=3

-- Update only if version hasn't changed
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 3;
-- If another transaction changed it, this updates 0 rows
-- Application checks affected rows and retries if needed

Pessimistic locking blocks other transactions (safe but slow). Optimistic locking assumes no conflict and checks at update time (fast but needs retry logic).

Key points

• PostgreSQL default is Read Committed; MySQL InnoDB default is Repeatable Read
• Serializable is the safest but can cause serialization failures (need retry logic)
• SELECT FOR UPDATE is pessimistic locking at the row level

Concepts covered

ACID, Transaction, Isolation Levels, Deadlock, Optimistic Locking