Transactions, Performance, and Production SQLLesson 6.1

Transactions and ACID — what developers need to know

ACID properties, BEGIN, COMMIT, ROLLBACK, transaction isolation, savepoints, implicit transactions

A Transaction Is an All-or-Nothing Block

ACID stands for Atomicity, Consistency, Isolation, Durability. For developers, the practical meaning: wrap related operations in a transaction so they either all succeed or all fail. No partial state reaches the database.

BEGIN;

-- Debit sender
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

-- Credit receiver
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- If anything fails, run ROLLBACK instead
COMMIT;

ROLLBACK Undoes Everything Since BEGIN

If the second UPDATE fails (account 2 doesn't exist), without a transaction, account 1 already lost $100. Inside a transaction, ROLLBACK reverts both changes atomically.

SAVEPOINT for Partial Rollbacks

BEGIN;
  UPDATE inventory SET quantity = quantity - 1 WHERE id = 5;
  SAVEPOINT after_inventory;
  
  INSERT INTO audit_log (action) VALUES ('stock_deducted');
  -- If only the audit log fails, roll back to savepoint
  ROLLBACK TO SAVEPOINT after_inventory;
COMMIT;  -- inventory update still committed

Most databases run each statement in an implicit transaction if no explicit BEGIN is used. Always use explicit transactions when multiple statements must be atomic.