Transactions & ACID - Atomicity, Consistency, Isolation, Durability

Module 3: Transactions & ACID

Understanding transactions is what separates developers who write code that “usually works” from those who build systems that are reliable under all conditions. This module gives you deep intuition for how transactions work.

Estimated Time: 8-10 hours
Hands-On: Concurrency simulation labs
Key Skill: Choosing the right isolation level for your use case

3.1 What is a Transaction?

A transaction is a sequence of database operations that are treated as a single, indivisible unit of work.

-- Classic example: Money transfer
BEGIN;  -- Start transaction

-- 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;  -- Make changes permanent
-- If anything fails, ROLLBACK undoes everything

Why Transactions Matter

Without transactions, partial failures leave your database in an inconsistent state:

Concept
How It Works
Common Pitfalls

PostgreSQL implements atomicity through Write-Ahead Logging (WAL):

Before modifying data, changes are written to the WAL
If crash occurs, WAL is replayed on recovery
Uncommitted transactions are rolled back
Committed transactions are guaranteed to be applied

-- Even complex operations are atomic
BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 500.00)
RETURNING id INTO order_id;

INSERT INTO order_items (order_id, product_id, qty)
SELECT order_id, product_id, quantity
FROM cart_items WHERE cart_id = 42;

UPDATE inventory 
SET stock = stock - ci.quantity
FROM cart_items ci
WHERE inventory.product_id = ci.product_id AND ci.cart_id = 42;

DELETE FROM cart_items WHERE cart_id = 42;

COMMIT;
-- All 4 operations succeed together, or none do

❌ Not using transactions for multi-step operations:

# BAD: If step 2 fails, step 1 already happened
db.execute("INSERT INTO orders ...")
db.execute("UPDATE inventory ...")  # This might fail!

✅ Wrap related operations in a transaction:

# GOOD: All or nothing
with db.transaction():
    db.execute("INSERT INTO orders ...")
    db.execute("UPDATE inventory ...")

❌ Long-running transactions:

# BAD: Holds locks while waiting for external service
with db.transaction():
    db.execute("UPDATE accounts SET balance -= 100 WHERE id = 1")
    response = external_api.charge_card(100)  # Could take seconds!
    db.execute("INSERT INTO payments ...")

✅ Keep transactions short:

# GOOD: External call outside transaction
response = external_api.charge_card(100)
if response.success:
    with db.transaction():
        db.execute("UPDATE accounts ...")
        db.execute("INSERT INTO payments ...")

Consistency: Valid State Transitions

Concept

Consistency ensures the database moves from one valid state to another. Transactions cannot violate database constraints or rules.

-- Constraints enforce consistency
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL(12, 2) NOT NULL,
    CONSTRAINT positive_balance CHECK (balance >= 0)
);

3.3 Isolation Levels

┌─────────────────────────────────────────────────────────────────────────────┐
│                          ISOLATION LEVELS                                   │
├────────────────────┬─────────────┬──────────────────┬─────────────────────┤
│ Isolation Level    │ Dirty Read  │ Non-Repeatable   │ Phantom Read        │
│                    │             │ Read             │                     │
├────────────────────┼─────────────┼──────────────────┼─────────────────────┤
│ READ UNCOMMITTED   │ Possible    │ Possible         │ Possible            │
│ READ COMMITTED     │ Prevented   │ Possible         │ Possible            │
│ REPEATABLE READ    │ Prevented   │ Prevented        │ Possible*           │
│ SERIALIZABLE       │ Prevented   │ Prevented        │ Prevented           │
├────────────────────┴─────────────┴──────────────────┴─────────────────────┤
│ * PostgreSQL's REPEATABLE READ also prevents phantoms (uses MVCC)         │
│                                                                            │
│ Higher isolation = More protection = More overhead/locking                 │
└────────────────────────────────────────────────────────────────────────────┘

READ COMMITTED (PostgreSQL Default)

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- (Transaction 2 updates and commits here)
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1200 (changed!)
COMMIT;

Behavior:

Each statement sees a fresh snapshot of committed data
Same query can return different results within a transaction
No locks held between statements

Use when:

Most OLTP workloads
You don’t need consistent reads across multiple queries
Maximum concurrency is important

REPEATABLE READ

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- (Transaction 2 updates and commits here)
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000!
-- We see the snapshot from when our transaction started
COMMIT;

Behavior:

Snapshot taken at first query in transaction
All reads see that same snapshot
Write conflicts cause serialization errors

-- Write conflict example
-- Transaction 1                    -- Transaction 2
BEGIN;                              BEGIN;
SET TRANSACTION ISOLATION           SET TRANSACTION ISOLATION
  LEVEL REPEATABLE READ;              LEVEL REPEATABLE READ;
  
UPDATE accounts                     
SET balance = balance + 100         
WHERE id = 1;                       
                                    UPDATE accounts
                                    SET balance = balance + 50
                                    WHERE id = 1;
                                    -- ERROR: could not serialize access
                                    -- due to concurrent update
COMMIT;
                                    ROLLBACK;  -- Must retry

Use when:

Reports that need consistent point-in-time data
Complex transactions reading related data
When you can handle retry logic for write conflicts

SERIALIZABLE

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transactions behave as if run one after another
-- Any anomaly causes serialization failure
COMMIT;

Behavior:

Strongest isolation: transactions appear to run serially
Uses Serializable Snapshot Isolation (SSI) in PostgreSQL
Detects anomalies and aborts conflicting transactions

Use when:

Financial calculations
When correctness is more important than performance
Complex business logic with multiple dependencies

3.4 Locking Mechanisms

PostgreSQL uses locks to manage concurrent access.

Row-Level Locks

-- FOR UPDATE: Lock rows for modification
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions block on UPDATE to this row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE: Lock rows for reading (prevents updates)
BEGIN;
SELECT * FROM products WHERE id = 10 FOR SHARE;
-- Allows other SELECTs, blocks UPDATEs
COMMIT;

-- SKIP LOCKED: Don't wait, skip locked rows
SELECT * FROM jobs 
WHERE status = 'pending' 
ORDER BY created_at 
LIMIT 1 
FOR UPDATE SKIP LOCKED;
-- Perfect for job queues: take next available job

-- NOWAIT: Fail immediately if locked
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row

Advisory Locks

Application-controlled locks for custom synchronization.

-- Session-level advisory lock (held until session ends or explicitly released)
SELECT pg_advisory_lock(12345);  -- Acquire lock on key 12345
-- ... do work ...
SELECT pg_advisory_unlock(12345);

-- Transaction-level advisory lock (released at COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);
-- No explicit unlock needed

-- Try lock (don't wait)
SELECT pg_try_advisory_lock(12345);  -- Returns true/false

-- Use case: Prevent duplicate processing
SELECT pg_try_advisory_lock(hashtext('process-order-' || order_id::text));

Deadlock Prevention

PostgreSQL detects deadlocks and aborts one transaction: ERROR: deadlock detected DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.

**Preventing Deadlocks**:

```sql
-- Strategy 1: Lock in consistent order
-- Always lock lower ID first
BEGIN;
UPDATE accounts SET balance = balance - 100 
WHERE id = LEAST(1, 2);  -- Lock id=1 first
UPDATE accounts SET balance = balance + 100 
WHERE id = GREATEST(1, 2);  -- Then id=2
COMMIT;

-- Strategy 2: Lock all rows at once
BEGIN;
SELECT * FROM accounts 
WHERE id IN (1, 2) 
ORDER BY id 
FOR UPDATE;  -- Lock both in consistent order

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

-- Strategy 3: Use short transactions
-- Don't hold locks while doing slow operations

3.5 Practical Transaction Patterns

Pattern 1: Retry on Serialization Failure

-- PostgreSQL function with retry logic
CREATE OR REPLACE FUNCTION transfer_money(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INTEGER := 0;
    max_retries INTEGER := 3;
BEGIN
    LOOP
        BEGIN
            -- Check sufficient balance
            IF (SELECT balance FROM accounts WHERE id = p_from_account) < p_amount THEN
                RAISE EXCEPTION 'Insufficient funds';
            END IF;
            
            -- Perform transfer
            UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
            UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
            
            -- Log transfer
            INSERT INTO transfers (from_account, to_account, amount, transferred_at)
            VALUES (p_from_account, p_to_account, p_amount, NOW());
            
            RETURN TRUE;
            
        EXCEPTION 
            WHEN serialization_failure OR deadlock_detected THEN
                retry_count := retry_count + 1;
                IF retry_count > max_retries THEN
                    RAISE;
                END IF;
                -- Exponential backoff
                PERFORM pg_sleep(0.1 * power(2, retry_count));
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Pattern 2: Idempotent Operations

Design operations that can be safely retried.

-- Use idempotency key to prevent duplicate processing
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key VARCHAR(255) UNIQUE,  -- Client-provided key
    user_id INTEGER NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Insert with conflict handling
INSERT INTO payments (idempotency_key, user_id, amount, status)
VALUES ('client-req-12345', 1, 100.00, 'completed')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING *;

-- If duplicate, return existing record
-- Application checks if RETURNING gave a row or needs to SELECT

Pattern 3: Optimistic Locking

Check version before updating, no locks held during read.

-- Add version column
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Read current state
SELECT id, name, price, stock, version FROM products WHERE id = 123;
-- Returns: version = 5

-- Update with version check
UPDATE products 
SET price = 99.99, 
    version = version + 1
WHERE id = 123 AND version = 5;  -- Expected version

-- Check if update succeeded
-- If 0 rows affected: someone else updated, retry
-- If 1 row affected: success

Pattern 4: Queue Processing with SELECT FOR UPDATE SKIP LOCKED

-- Job queue table
CREATE TABLE jobs (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    attempts INTEGER NOT NULL DEFAULT 0,
    locked_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending';

-- Worker: Get next job (doesn't block other workers)
BEGIN;

UPDATE jobs 
SET status = 'processing', 
    locked_at = NOW(),
    attempts = attempts + 1
WHERE id = (
    SELECT id FROM jobs 
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- Skip jobs other workers are processing
)
RETURNING *;

-- Process the job...

-- Mark complete
UPDATE jobs SET status = 'completed', completed_at = NOW() WHERE id = ?;

COMMIT;

3.6 Common Transaction Mistakes

Mistake 1: Transaction Too Long

# ❌ BAD: Long transaction holding locks
with db.transaction():
    order = db.query("SELECT * FROM orders WHERE id = 1 FOR UPDATE")
    
    # External API call - could take seconds!
    payment_result = stripe.charge(order.total)
    
    if payment_result.success:
        db.execute("UPDATE orders SET status = 'paid' WHERE id = 1")
    
# Locks held for entire Stripe call duration!

# ✅ GOOD: Short transactions
order = db.query("SELECT * FROM orders WHERE id = 1")

# External call outside transaction
payment_result = stripe.charge(order.total)

if payment_result.success:
    with db.transaction():
        db.execute("""
            UPDATE orders 
            SET status = 'paid', payment_id = %s 
            WHERE id = 1 AND status = 'pending'
        """, [payment_result.id])

Mistake 2: Not Handling Conflicts

# ❌ BAD: Assumes success
db.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = 1")

# ✅ GOOD: Check result and handle conflicts
result = db.execute("""
    UPDATE inventory 
    SET stock = stock - 1 
    WHERE product_id = 1 AND stock > 0
    RETURNING stock
""")

if result.rowcount == 0:
    raise OutOfStockError("Product is out of stock")

Mistake 3: Ignoring Isolation Level

# ❌ BAD: Inconsistent read without proper isolation
with db.transaction():
    total = db.query("SELECT SUM(amount) FROM line_items WHERE order_id = 1")
    # Another transaction adds a line item here!
    items = db.query("SELECT * FROM line_items WHERE order_id = 1")
    # items and total don't match!

# ✅ GOOD: Use REPEATABLE READ for consistent snapshot
with db.transaction(isolation_level='REPEATABLE READ'):
    total = db.query("SELECT SUM(amount) FROM line_items WHERE order_id = 1")
    items = db.query("SELECT * FROM line_items WHERE order_id = 1")
    # Both queries see the same snapshot

3.7 Practice Exercises

Exercise 1: Money Transfer

Implement a safe money transfer function that:

Validates sufficient balance
Handles concurrent transfers correctly
Logs all transfers
Prevents negative balances

Solution

CREATE OR REPLACE FUNCTION safe_transfer(
    p_from INTEGER,
    p_to INTEGER,
    p_amount DECIMAL
) RETURNS TABLE(success BOOLEAN, message TEXT) AS $$
DECLARE
    v_from_balance DECIMAL;
BEGIN
    -- Lock both accounts in consistent order to prevent deadlock
    PERFORM * FROM accounts 
    WHERE id IN (p_from, p_to) 
    ORDER BY id 
    FOR UPDATE;
    
    -- Check balance
    SELECT balance INTO v_from_balance 
    FROM accounts WHERE id = p_from;
    
    IF v_from_balance < p_amount THEN
        RETURN QUERY SELECT false, 'Insufficient funds';
        RETURN;
    END IF;
    
    -- Perform transfer
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    
    -- Log transfer
    INSERT INTO transfer_log (from_account, to_account, amount)
    VALUES (p_from, p_to, p_amount);
    
    RETURN QUERY SELECT true, 'Transfer successful';
END;
$$ LANGUAGE plpgsql;

Exercise 2: Inventory Reservation

Design a system to reserve inventory for a shopping cart that:

Holds items for 15 minutes
Releases reservation if cart is abandoned
Prevents overselling
Handles concurrent checkout

Solution

-- Reservations table
CREATE TABLE inventory_reservations (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id),
    cart_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (product_id, cart_id)
);

-- Reserve inventory
CREATE OR REPLACE FUNCTION reserve_inventory(
    p_cart_id UUID,
    p_product_id INTEGER,
    p_quantity INTEGER
) RETURNS BOOLEAN AS $$
DECLARE
    v_available INTEGER;
BEGIN
    -- Calculate available (stock - active reservations)
    SELECT 
        p.stock - COALESCE(SUM(r.quantity), 0)
    INTO v_available
    FROM products p
    LEFT JOIN inventory_reservations r 
        ON r.product_id = p.id AND r.expires_at > NOW()
    WHERE p.id = p_product_id
    GROUP BY p.id
    FOR UPDATE OF p;  -- Lock product row
    
    IF v_available < p_quantity THEN
        RETURN FALSE;
    END IF;
    
    -- Create or update reservation
    INSERT INTO inventory_reservations (product_id, cart_id, quantity, expires_at)
    VALUES (p_product_id, p_cart_id, p_quantity, NOW() + INTERVAL '15 minutes')
    ON CONFLICT (product_id, cart_id) DO UPDATE
    SET quantity = EXCLUDED.quantity,
        expires_at = EXCLUDED.expires_at;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- Cleanup expired reservations (run periodically)
DELETE FROM inventory_reservations WHERE expires_at < NOW();

-- On checkout: Convert reservation to order
CREATE OR REPLACE FUNCTION checkout(p_cart_id UUID) RETURNS INTEGER AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    -- Verify all reservations still valid
    IF EXISTS (
        SELECT 1 FROM inventory_reservations 
        WHERE cart_id = p_cart_id AND expires_at < NOW()
    ) THEN
        RAISE EXCEPTION 'Some reservations have expired';
    END IF;
    
    -- Create order and reduce stock atomically
    INSERT INTO orders (cart_id, status) VALUES (p_cart_id, 'confirmed')
    RETURNING id INTO v_order_id;
    
    -- Reduce stock
    UPDATE products p
    SET stock = stock - r.quantity
    FROM inventory_reservations r
    WHERE r.product_id = p.id AND r.cart_id = p_cart_id;
    
    -- Remove reservations
    DELETE FROM inventory_reservations WHERE cart_id = p_cart_id;
    
    RETURN v_order_id;
END;
$$ LANGUAGE plpgsql;

Next Module

Module 4: Indexing Deep Dive

Master the art of database indexing for blazing-fast queries

Testing & Code Quality

Crash Courses

AI Engineering

Math for ML - Understanding Linear Algebra

Probability & Statistics for ML

Math for ML - Understanding Calculus

ML Mastery

Deep Learning Mastery

NestJS Mastery

Microservices Mastery

Low Level Design

OOP Concepts

SOLID Principles

Design Patterns

LLD Case Studies

System Design (HLD)

Senior Level (L5+/Staff)

HLD Case Studies

Engineering Fundamentals

DevOps & Operations

AWS Cloud

AWS Monitoring & Observability

AWS Security Services

AWS Serverless

AWS Operations

AWS Advanced

AWS Case Studies

DevOps Tools

Database Engineering

HIPAA Compliance Mastery

Operating Systems

Linux Internals

Distributed Systems

Networking Mastery

Build Your Own X

Go Lang Mastery

C Programming

Distributed System Tools

​Module 3: Transactions & ACID

​3.1 What is a Transaction?

​Why Transactions Matter

​Consistency: Valid State Transitions

​3.3 Isolation Levels

​READ COMMITTED (PostgreSQL Default)

​REPEATABLE READ

​SERIALIZABLE

​3.4 Locking Mechanisms

​Row-Level Locks

​Advisory Locks

​Deadlock Prevention

​3.5 Practical Transaction Patterns

​Pattern 1: Retry on Serialization Failure

​Pattern 2: Idempotent Operations

​Pattern 3: Optimistic Locking

​Pattern 4: Queue Processing with SELECT FOR UPDATE SKIP LOCKED

​3.6 Common Transaction Mistakes

​Mistake 1: Transaction Too Long

​Mistake 2: Not Handling Conflicts

​Mistake 3: Ignoring Isolation Level

​3.7 Practice Exercises

​Exercise 1: Money Transfer

​Exercise 2: Inventory Reservation

​Next Module

Module 4: Indexing Deep Dive

Module 3: Transactions & ACID

3.1 What is a Transaction?

Why Transactions Matter

Consistency: Valid State Transitions

3.3 Isolation Levels

READ COMMITTED (PostgreSQL Default)

REPEATABLE READ

SERIALIZABLE

3.4 Locking Mechanisms

Row-Level Locks

Advisory Locks

Deadlock Prevention

3.5 Practical Transaction Patterns

Pattern 1: Retry on Serialization Failure

Pattern 2: Idempotent Operations

Pattern 3: Optimistic Locking

Pattern 4: Queue Processing with SELECT FOR UPDATE SKIP LOCKED

3.6 Common Transaction Mistakes

Mistake 1: Transaction Too Long

Mistake 2: Not Handling Conflicts

Mistake 3: Ignoring Isolation Level

3.7 Practice Exercises

Exercise 1: Money Transfer

Exercise 2: Inventory Reservation

Next Module