Databases Lesson 5: Transactions

Transactions ensure that a series of operations either ALL succeed or ALL fail. Essential for financial systems, inventory, or any operation that can’t be partially completed.

ACID Properties

-- A: Atomicity — all or nothing
-- C: Consistency — data always in valid state
-- I: Isolation — transactions don't see each other's changes
-- D: Durability — committed changes survive crashes

-- Without transaction (DANGEROUS!):
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- CRASH HERE — money disappears!
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- With transaction (SAFE):
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  -- If this fails, rollback undoes everything
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- both succeed, changes saved
-- If anything fails between BEGIN and COMMIT: ROLLBACK (undo all)

Savepoints & Error Handling

BEGIN;
  UPDATE products SET stock = stock - 1 WHERE id = 5;
  SAVEPOINT after_stock;
  
  INSERT INTO orders (user_id, product_id, qty) VALUES (1, 5, 1);
  
  -- If payment fails:
  ROLLBACK TO SAVEPOINT after_stock;
  -- Stock decrease is kept but order is cancelled
  
COMMIT;