Pathwise

Manuals / SQL / Chapter 9

C · Write · advanced · Week 3 · Chapter 9 of 12

Transactions & isolation

BEGIN / COMMIT / ROLLBACK. ACID guarantees. Transfer money mental model: debit and credit must both succeed or both fail.

Path progress
69%

Step 1 of 3

Transaction basics

BEGIN; multiple statements; COMMIT if all good, ROLLBACK on error. SQLite: BEGIN IMMEDIATE for writes.

Transaction basicsDrag stickies · tap for tips
Study mapDrag stickies · tap for tipsKeep it shortdrag · tap →Name the waitdrag · tap →Scope locatorsdrag · tap →Trace when stuckdrag · tap →One browser firstdrag · tap →Isolate statedrag · tap →Assert the UIdrag · tap →Retry wiselydrag · tap →Seed datadrag · tap →Close the loopdrag · tap →Keep it shortdrag · tap →Name the waitdrag · tap →Scope locatorsdrag · tap →Trace when stuckdrag · tap →One browser firstdrag · tap →Isolate statedrag · tap →Pathwise hackdrag · tap →Transaction basicsdrag · tap →Try thisdrag · tap →Follow the dashed drag · tap →

Example

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- inspect; then COMMIT or ROLLBACK
COMMIT;

Do this now

Transfer script: decrement account A, increment B — wrap in transaction.

Was this step clear?
Chapter learning outcomes
  • BEGIN/COMMIT/ROLLBACK
  • ACID intuition
  • Isolation levels lite
  • Deadlocks awareness

Clear these before you leave

Side quest

Failed insert rollback

Insert valid row + invalid row in txn; ROLLBACK; verify neither persisted.