Building Transactions: A Preview
Concept. Building a transaction means wrapping a sequence of database operations in BEGIN … COMMIT so the database treats them as one atomic unit, never letting another transaction see the work half-done.
Intuition. When Mickey adds 5 songs to his playlist, the app issues BEGIN, runs 5 INSERTs into Listens, then COMMIT. Making the playlist appear instantly with all 5 songs to Mickey, Minnie, and everyone else, never with 2 or 3 mid-write.
The Kitchen Analogy
A bustling restaurant has to fill ten orders at once. The kitchen has two ways to do it:
Serial. One order at a time (DMV-style)
Take order #1, prepare it completely, serve. Then order #2. Then order #3.
+ No mix-ups, simple to manage.
− Painfully slow, staff sit idle.
Concurrent. Smart interleaving (busy bar)
Start drinks for everyone, fry several plates in parallel, assemble each order as its components finish.
+ Fast, resource-efficient.
− Mix-up risk; needs careful coordination.
The Key Insight: Isolation Guarantee
Each customer's expectation
However chaotic the kitchen, each customer gets their complete, correct order. As if they were the only one being served. Alice orders a burger and fries; she gets a burger and fries. Her fries don't end up on Bob's plate, even when the kitchen is juggling five other orders at the same time.
The Database Version: Transaction Manager
The database faces the same trade-off, but across thousands of concurrent transactions instead of ten orders. The next pages introduce the rules and protocols (schedules, locks, ACID guarantees) that let it interleave safely.
The Three Types of Schedules
Serial Schedule 1
Finish Transaction 1, then Transaction 2, then Transaction 3...
T1: ████
T2: ████
T3: ████
Safe but slow
Serial Schedule 2
Different order: Transaction 2, then Transaction 1, then Transaction 3...
T2: ████
T1: ████
T3: ████
Also safe but slow
Interleaved Schedule
Mix operations from different transactions smartly
T1: █ T2: ██ T1: █ T3: ██ T1: ██ T2: ██
Fast if done correctly