Week 7: Transactions & ACID Guarantees

1. BEGIN, COMMIT & ROLLBACK

BEGIN;

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

COMMIT; -- both changes become permanent together, or...
-- ROLLBACK; -- ...neither does, if something went wrong

Without wrapping both statements in a transaction, a crash or error between the two UPDATEs leaves $100 debited from account 1 with nowhere credited — money genuinely vanishes. BEGIN starts a transaction; COMMIT makes every change within it permanent, all at once; ROLLBACK discards every change within it, as if none of it ever happened.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accoutns SET balance = balance + 100 WHERE id = 2; -- typo — errors out
-- Postgres aborts the transaction here; the first UPDATE is NOT committed
COMMIT; -- has nothing to commit — the whole transaction already failed

2. What ACID Actually Guarantees

• Atomicity — a transaction's statements succeed or fail together, exactly as shown above; there's no partial state.
• Consistency — a transaction can only move the database from one valid state to another; every constraint from Week 6 still holds after it commits.
• Isolation — concurrent transactions don't see each other's uncommitted changes (the exact degree is configurable — next section).
• Durability — once COMMIT returns successfully, the change survives a crash immediately afterward; it's written to disk, not just held in memory.

These four properties, together, are the actual reason "just use a real database" is meaningful advice — a hand-rolled file-based storage system has to rebuild every one of these guarantees from scratch, and getting even one subtly wrong reintroduces exactly the kind of bug this week exists to prevent.

3. Isolation Levels & Race Conditions

Two transactions running at the same time, touching the same rows, can interact in ways a single transaction never would — a race condition.

-- Transaction A                          -- Transaction B
BEGIN;                                     BEGIN;
SELECT stock FROM products WHERE id = 1;   SELECT stock FROM products WHERE id = 1;
-- sees stock = 1                          -- also sees stock = 1
UPDATE products SET stock = 0 WHERE id=1;
COMMIT;
                                            UPDATE products SET stock = -1 WHERE id=1; -- !
                                            COMMIT;

Both transactions read stock = 1 before either committed — without protection, both proceed as if they alone are buying the last item, and stock goes negative. Postgres's default isolation level, Read Committed, does not prevent this specific race on its own; the fix (next section) is explicit locking, not just wrapping the statements in a transaction.

4. Fixing a Real Concurrency Bug

BEGIN;

SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- locks this row
-- any other transaction's FOR UPDATE on the same row now blocks and waits

-- application code checks: is stock > 0?
UPDATE products SET stock = stock - 1 WHERE id = 1;

COMMIT; -- releases the lock; the waiting transaction proceeds and re-reads fresh data

FOR UPDATE makes the second transaction genuinely wait for the first to finish, rather than both reading the same stale value — it re-reads stock only after acquiring the lock, at which point it correctly sees 0 and can reject the purchase instead of driving stock negative. This is the standard, correct pattern for "check then act" logic on shared, contended rows.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.