You send a friend $50. The app debits your account… and crashes before crediting theirs. Where did the money go? If the database is doing its job: nowhere — the debit never happened either. That all-or-nothing guarantee isn't luck; it's a transaction, and it's the reason databases run the world's money.
What you'll learn
What
A transaction groups multiple statements into one atomic unit: BEGIN starts it, COMMIT makes every change permanent together, ROLLBACK undoes all of it. ACID names the four guarantees — Atomicity (all or nothing), Consistency (constraints always hold), Isolation (concurrent transactions don't see each other's half-done work), Durability (committed means survived-the-crash). You'll also build intuition for isolation levels — the dial between correctness and concurrency.
Why
Every system you'll analyze data FROM relies on transactions to stay correct under concurrent writes — and data scientists touch this directly: safe backfills and batch UPDATEs (wrap in a transaction, check, then commit), understanding why a long-running analytics query sees a consistent snapshot, and explaining anomalies like two reports disagreeing mid-load. ACID is also a guaranteed interview topic, and the vocabulary for every 'SQL vs NoSQL' trade-off discussion.
Where it's used
Payments and inventory systems, safe data-cleaning scripts (BEGIN … verify … COMMIT), ETL loads that must appear atomically to dashboards, debugging deadlocks and lock waits, and the 'eventual consistency' debates around distributed systems.
Where this runs in production
Every charge touches multiple ledger rows — debit, credit, fees — inside one transaction; a partial write would literally create or destroy money, so atomicity is the product.
Seat holds during an on-sale are isolation in action: two transactions trying to book seat 14C must serialize, or the venue oversells — the textbook lost-update problem at stadium scale.
Warehouse loads swap fully-built tables into place transactionally, so dashboards see yesterday's data or today's — never a half-loaded mixture mid-refresh.
BEGIN; UPDATE accounts SET balance = balance - 50 WHERE account_id = 1; UPDATE accounts SET balance = balance + 50 WHERE account_id = 2; COMMIT; -- or, if anything looked wrong before committing: -- ROLLBACK;
The mechanics are three keywords. BEGIN opens the transaction; every statement after it is provisional. COMMIT makes them all permanent, together, as one instant. ROLLBACK discards them all as if nothing happened. Without explicit BEGIN, most databases run each statement in its own implicit transaction — auto-commit — which is why a lone typo'd UPDATE is instantly permanent.
A transaction either fully happens or fully doesn't — there is no state where the debit landed but the credit didn't. If the server crashes mid-transaction, recovery rolls the incomplete work back. This is what makes multi-step changes SAFE to attempt: the intermediate states are never visible facts, only the before and the after.
Consistency means every committed state satisfies the schema's rules — CHECK constraints, foreign keys, uniqueness. A transfer that would push balance below a CHECK (balance >= 0) constraint fails and rolls back entirely; the database never commits a rule-breaking state. Note the division of labor: the database enforces the rules you DECLARED — 'the two updates should sum to zero' is consistency only if you encoded it (constraints, triggers) or your application logic keeps it inside one atomic transaction.
Hundreds of transactions run at once, but each behaves as if alone: no transaction sees another's uncommitted, half-done work. The gold standard — serializability — means the outcome equals SOME one-at-a-time ordering. Full isolation costs concurrency, so databases offer LEVELS that relax it in exchange for speed; the anomalies each level permits are named and standardized, and they're the next block.
-- The standard levels, weakest → strongest: -- READ UNCOMMITTED dirty reads possible (PG treats as READ COMMITTED) -- READ COMMITTED no dirty reads; non-repeatable + phantoms possible ← PG default -- REPEATABLE READ stable re-reads; (in PG, snapshot: phantoms gone too) -- SERIALIZABLE full serializability — some transactions retry BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT SUM(balance) FROM accounts; -- consistent snapshot -- ... long analysis, other sessions commit freely ... SELECT SUM(balance) FROM accounts; -- same answer as above COMMIT;
Isolation levels are the dial. READ COMMITTED (PostgreSQL's default) sees only committed data, but each STATEMENT sees the latest commits — two reads can disagree. REPEATABLE READ freezes a snapshot at first read: your whole transaction sees one consistent instant, which is exactly what a multi-query report wants. SERIALIZABLE adds detection of subtler write interleavings, aborting one transaction with a serialization error you must retry. Stronger level = fewer anomalies = more retries/waiting.
Once COMMIT returns, the change survives power loss. The trick is the write-ahead log (WAL): before acknowledging, the database appends the change to a sequential log and forces it to disk; the table pages update later at leisure. Crash recovery replays the log. Sequential log writes are fast — this is how databases are both durable AND quick, and WAL-shipping is also how replicas stay in sync.
A transaction is drafting on a personal whiteboard: scribble the debit, scribble the credit, check your math. Nobody else can see your board (isolation). ROLLBACK is an eraser sweep. COMMIT walks the board to the notary, who copies it into the permanent ledger in one stamped entry (atomicity) — refusing entries that break the ledger's rules (consistency) — into a book that survives fire (durability). The anomalies are notary-office failures: reading someone's whiteboard before it's stamped (dirty read), the ledger changing between your two glances (non-repeatable read), new pages appearing mid-audit (phantom).
Two sessions both run: read balance (100), compute +50 in app code, write 150. Both commit; one deposit vanished — a LOST UPDATE, and READ COMMITTED happily allows it because each write was individually valid. Cures, in order of preference: make the modification atomic in SQL (UPDATE accounts SET balance = balance + 50 — read and write in one statement, row-locked); or SELECT ... FOR UPDATE to lock the row through the read-modify-write; or SERIALIZABLE and retry on abort. The general lesson: read-then-write-back in application code is a concurrency bug until proven otherwise.
One transaction's journey, with the two exits and the crash path. Click each stage to see which ACID letter is on duty.
Move $50 between accounts — once successfully, once discovering a problem mid-flight.
The SELECT between updates and COMMIT is the underrated habit: inside the transaction you see your own provisional changes, so you can VERIFY before making them permanent. Other sessions still see the old balances until COMMIT lands both changes at once.
Your task
Complete the safe backfill: inside one transaction, apply a 10% price cut to the 'Clearance' category, verify the result from inside the transaction, and make it permanent. Fill in the blanks with the three transaction-control keywords and the verification query's table.
name | unit_price -----------------+------------ Cable Organizer | 18.00 Phone Stand | 9.00 (2 rows)
Write your solution in the editor on the right, then hit Run.
A transfer transaction crashes after the debit UPDATE but before the credit UPDATE. What does the database guarantee?
What does Durability specifically promise?
Within one transaction you run the same SELECT twice and get different values because another session committed an UPDATE in between. Which anomaly is this, and which isolation level stops it?
Two sessions run read-balance-in-app-code-then-write-new-balance concurrently; one deposit vanishes with no error. Which single-statement rewrite fixes it?
What's the right fix?
Inventory oversell guard: write one transaction that (1) locks product 42's inventory row with SELECT ... FOR UPDATE, (2) decrements stock_count by 3 only if sufficient stock exists — expressed as UPDATE ... WHERE stock_count >= 3, (3) inserts the order line into order_items (order_id 9001, product_id 42, quantity 3, price_at_purchase 25.00), and (4) commits. Use inventory(product_id, stock_count).
Explain ACID — with a concrete failure each letter prevents.
Atomicity: all statements in a transaction commit or none do. Prevents: a crash between debit and credit leaving money destroyed — recovery rolls the debit back. Consistency: every committed state satisfies declared constraints (CHECKs, foreign keys, uniqueness). Prevents: a transfer committing an account below its CHECK (balance >= 0) floor — the whole transaction aborts instead. Isolation: concurrent transactions can't observe each other's uncommitted work; at full strength the outcome equals some serial ordering. Prevents: a report reading a half-finished transfer and showing money that exists in two places. Durability: once COMMIT returns, the change survives power loss, via the write-ahead log flushed before acknowledgment. Prevents: a confirmed order vanishing because the server died a second later. I'd add the practical nuance: A, C, and D are essentially absolute, while I is a dial — isolation LEVELS trade anomaly-freedom against concurrency, which is where most real-world subtlety lives.
Compare the isolation levels. What does each allow, and what would you actually use when?
READ UNCOMMITTED permits dirty reads — seeing uncommitted data that may roll back; PostgreSQL doesn't truly offer it (treats it as READ COMMITTED). READ COMMITTED — the common default — sees only committed data, but takes a fresh snapshot per statement, so non-repeatable reads and phantoms occur between queries; right for typical OLTP where each statement is self-contained, especially with atomic updates (SET x = x + 1). REPEATABLE READ pins one snapshot for the whole transaction: re-reads are stable and (in PostgreSQL's snapshot implementation) phantoms are gone too; it's my default for multi-query reports, backfills with verification steps, and anything where numbers from different queries must reconcile. SERIALIZABLE guarantees equivalence to some serial order, catching subtler write-interleaving bugs (like write skew) — at the price of serialization failures that the application must catch and retry; right for genuinely intertwined financial logic. Practical summary: default + atomic single-statement writes for OLTP, REPEATABLE READ for consistent reads, SERIALIZABLE + retry loop where correctness of concurrent WRITES is the product.
As a data scientist, when do transactions actually matter to you day to day?
Four recurring places. Defensive data surgery: any hand-run UPDATE or DELETE goes inside BEGIN → check the affected row count → SELECT the new state → COMMIT or ROLLBACK; the transaction converts a potentially catastrophic typo into a free do-over. Consistent multi-query analysis: a report or feature-extraction job whose queries must agree runs under REPEATABLE READ, buying a frozen snapshot of a live database without blocking a single write — MVCC's gift to analysts. Atomic publishing: pipeline outputs swap into place transactionally (build new table, rename inside one transaction), so downstream dashboards never see half-loaded data — and understanding this pattern helps me debug when someone else's pipeline DIDN'T do it. Interpreting anomalies: when two systems disagree, knowing about snapshots, commit timing, and replication lag turns 'the data is wrong' into 'query A's snapshot predates load B' — a diagnosis instead of a mystery. The meta-point: analysts live downstream of concurrency; transactions are the physics of why the numbers are what they are.
Common Mistakes to Avoid
1) Running destructive UPDATEs/DELETEs in auto-commit — no BEGIN means no ROLLBACK; the safe ritual is BEGIN → change → verify counts and state → COMMIT. 2) Read-then-write-back in application code — the lost-update gap; prefer atomic SET x = x + delta, or FOR UPDATE when app logic must sit in the middle. 3) Confusing the anomalies — dirty = uncommitted data seen; non-repeatable = a row CHANGED between re-reads; phantom = new rows APPEARED in a re-run query. 4) Fixing read-consistency with table locks — REPEATABLE READ gives a consistent snapshot while writers proceed; locks halt the business. 5) Leaving transactions open (idle-in-transaction) — held locks block writers and pinned snapshots bloat tables; keep transactions short and never hold one across think-time.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5 ACID with the whiteboard-and-notary analogy, then quiz me letter by letter.' • 'Play two concurrent sessions and make me predict what each SELECT returns at READ COMMITTED vs REPEATABLE READ.' • 'Walk me into a lost update step by step, then make me fix it three different ways.' • 'Give me a messy backfill task and check whether my transaction ritual is safe.' • 'Interview mode: ask me to design an inventory checkout that can't oversell, and attack my answer with interleavings.'
Glossary
Transaction — a group of statements executing as one atomic unit (BEGIN … COMMIT/ROLLBACK). Auto-commit — each statement runs as its own instant transaction; the default outside BEGIN. Atomicity — all or nothing. Consistency — committed states satisfy all declared constraints. Isolation — concurrent transactions don't observe each other's in-flight work. Durability — committed changes survive crashes (via the WAL). Write-ahead log (WAL) — changes logged to disk before commit acknowledgment; replayed in recovery. Dirty read — reading uncommitted data. Non-repeatable read — a re-read row changed within one transaction. Phantom read — a re-run query matches new rows. Lost update — concurrent read-modify-write where one write silently overwrites another. Isolation levels — READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE. Snapshot / MVCC — each transaction reads a consistent version of the data; readers and writers don't block each other. SELECT … FOR UPDATE — locks read rows until commit. Deadlock — transactions waiting on each other's locks; one is aborted and retried. Serialization failure — SERIALIZABLE aborting a transaction that must be retried.
Recommended Resources
• Deep dive: 'Designing Data-Intensive Applications' (Kleppmann), Ch. 7 'Transactions' — the best treatment of isolation anomalies in print. • Docs: PostgreSQL manual 'Transaction Isolation' — short, precise, and the source of truth for MVCC behavior. • Interactive: open two psql/DB-client windows and REPRODUCE the lesson's demos — the non-repeatable read and the lost update; seeing the interleaving live cements it permanently. • Practice: adopt the BEGIN → verify → COMMIT ritual on your very next data fix. • Next in DSM: the Design module is complete — the Analysis module begins with SQL for EDA, turning everything you've built toward profiling real datasets.
Recap
✓ BEGIN makes changes provisional; COMMIT lands them all at one instant; ROLLBACK erases them for free — the safe-backfill ritual is BEGIN → change → verify → COMMIT. ✓ ACID: Atomicity (all or nothing), Consistency (constraints always hold), Isolation (no seeing half-done work), Durability (committed survives crashes via the WAL). ✓ Anomaly ladder: dirty read (uncommitted seen) → non-repeatable read (row changed between re-reads) → phantom (new rows appear); isolation levels trade these off against concurrency. ✓ REPEATABLE READ = one consistent snapshot for multi-query reports, without blocking writers (MVCC). ✓ The lost update hides in read-then-write-back code — close the gap with atomic SET x = x + delta, FOR UPDATE, or SERIALIZABLE + retry. ✓ Keep transactions short; atomic table swaps publish pipeline output without half-loaded states. Next up: SQL for EDA. The Design module is done — you know how data is structured, sped up, and kept correct. Now the Analysis module puts it all to work: profiling unfamiliar datasets, hunting data quality issues, and building cohorts and funnels in pure SQL.
Run your code to see the output here.