Why Transactions? ACID, and What Course 1 Doesn't Guarantee

Building a Database Engine: Transactions & Concurrency

Chapter 1 · Why Transactions? ACID, and What Course 1 Doesn't Guarantee

Course 1 ended with a real, working single-table engine: records, pages, a heap table, B-tree indexes, and a small SQL-like query language, all genuinely wired together and verified end to end in its own capstone. What it never once claimed, anywhere, is that any of it survives going wrong partway through. This chapter doesn't add a single line of new engine code — it goes back to Course 1's own finished code and asks it two direct, uncomfortable questions, verifying the answers with real Python rather than assuming them.

Scope note — read this before anything else

This course, Course 2, is where transactions, crash recovery, concurrent access, multi-table joins, and query planning across more than one table are actually built. Nothing in this chapter is a new feature — it's a deliberately honest audit of Course 1's own real engine, using Course 1's own real classes (Page, HeapFile, HeapTable, BTreeNode, Database), unmodified.

ACID, Stated Concretely

Four properties a database is expected to guarantee around any single operation (or group of operations run together as one unit, a transaction):

Property	What it actually means
Atomicity	A multi-step operation either fully happens, or none of it does — never left half-done
Consistency	The database moves from one genuinely valid state to another — nothing that's supposed to stay true (like "every row is reachable through its own index") is allowed to silently stop being true
Isolation	Two operations running at the same time don't see each other's own half-finished work
Durability	Once an operation is confirmed done, it survives a crash — a power loss, a killed process, a kernel panic

Course 1's own capstone chapter closed with an honest "What This Course Doesn't Cover" section naming exactly this gap — it just never demonstrated what specifically goes wrong without it. That's this chapter's job.

Finding 1: A Real, Verified Consistency Failure

Course 1's own Database.insert() is a genuine two-step operation: write the row to the heap table, then update every index registered for that table.

def insert(self, table_name, values):
    schema, table = self.tables[table_name]
    location = table.insert(values)                    # STEP 1: physically write the row
    column_names = [name for name, t in schema.columns]
    for (t_name, col_name), root in list(self.indexes.items()):
        if t_name == table_name:
            idx = column_names.index(col_name)
            self.indexes[(t_name, col_name)] = btree_insert(root, values[idx], location)   # STEP 2: update the index
    return location
    

Nothing in Course 1 guarantees these two steps happen together. To verify what a real interruption between them actually looks like, this test skips Database.insert() entirely for one row and calls the heap table's own insert() directly — exactly what a process crash between step 1 and step 2 would leave behind.

db.insert('accounts', [1, 'Alice', 500])   # a real, complete insert -- both steps ran

# simulate a crash: call the heap table's own insert() directly, bypassing
# Database.insert()'s own second step (the index update) entirely
schema, table = db.tables['accounts']
table.insert([2, 'Bob', 300])
    

Verified directly — the table and its own index silently disagree

A real scan of the heap table finds both rows: [[1, 'Alice', 500], [2, 'Bob', 300]] — row 2 is genuinely, physically present on disk. But db.select('accounts', where=('id', '=', 2)), which Chapter 9's own real query planner routes through the index, returns [] — empty. The row exists. A query against it, using the index, reports that it doesn't. Two parts of the same database have silently drifted out of agreement with each other, because nothing enforces that a multi-step operation either fully completes or doesn't happen at all — precisely what atomicity is supposed to guarantee, and precisely what Course 1 never built.

This isn't a hypothetical worst case dreamed up for the sake of a scary example — it's the exact, ordinary shape every real insert already takes. Any interruption at all between its two steps — a crash, a killed process, even an unhandled exception raised partway through the index-update loop — leaves the database in exactly this state.

Finding 2: The Index Itself Never Touches Disk

A more basic question: does a B-tree index even survive an ordinary, controlled restart — closing the Python process and opening a fresh one pointed at the same files, the way any real application actually re-opens a database?

db_a = Database(directory)
db_a.create_table('products', [('id', 'INTEGER'), ('name', 'TEXT')])
db_a.insert('products', [1, 'Widget'])
db_a.insert('products', [2, 'Gadget'])
db_a.create_index('products', 'id')

# simulate a real restart: a FRESH Database object, pointing at the SAME directory
db_b = Database(directory)
db_b.create_table('products', [('id', 'INTEGER'), ('name', 'TEXT')])
    

Verified directly — the data survives, the index doesn't

list(db_b.tables['products'][1].scan()) after "restarting" returns both real rows, completely intact — Chapter 4's own load_page correctly reads back real bytes from a real file, exactly as it was verified to do in Course 1. But db_b.indexes is {} — empty. The entire B-tree built across Chapters 5–6 is gone. A BTreeNode was always a plain, in-memory Python object — nothing in this engine ever gave it a byte format, a file, or a way to be written to disk at all, unlike Page, which genuinely has all three.

A query for WHERE id = 1 against db_b still returns the right answer here — but only because Chapter 9's own choose_plan() correctly sees no index registered for that column and safely falls back to a full scan. This particular case is a silent performance loss, not a correctness bug: every index would need to be rebuilt from scratch, by hand, after every single restart, or a real application simply never gets the benefit Course 1's own Chapter 9 built it for in the first place.

Separately, real database durability has a second, deeper layer worth naming honestly even without simulating it directly: Course 1's own HeapFile.write_page() calls self.file.flush() after every write, but never os.fsync(). This is a well-established, uncontroversial fact about how operating systems work, not something that needs a live crash to demonstrate — flush() only pushes data out of Python's own internal buffer and into the operating system's page cache; the OS itself is still free to hold that data in memory for a while before actually committing it to the physical disk. A real power loss between those two points loses data Python already reported as "written." Chapter 2 builds the real mechanism — write-ahead logging — that closes this exact gap.

What Actually Happened, Underneath Both Findings

Both findings trace back to the same root cause: Course 1 built real mechanisms — pages, a heap file, a B-tree, a query planner — but never built any protocol governing what happens when something interrupts a sequence of steps partway through. That protocol is precisely what this course exists to build:

This chapter's finding	Resolved in
Data written to Python's own file buffer isn't guaranteed to survive a real power loss	Chapter 2 — Write-Ahead Logging
A crash mid-write can leave a page (or the whole table) in an unknown state	Chapter 3 — Crash Recovery: Replaying the Log
A two-step insert with only step 1 completed leaves the index silently wrong	Chapter 4 — Atomicity: Undo Logging & Rollback
Two operations running at the same time could interfere with each other (not yet tested directly — Course 1's engine has never run two operations concurrently at all)	Chapters 5–6 — Locking and MVCC

Where This Connects

This chapter's finding	What it connects to
The index/data consistency bug	Building a Database Engine: Storage & Query Fundamentals Chapters 4 and 9 (Course 1) — the exact two real methods, `HeapTable.insert()` and `Database.insert()`, whose gap between them is demonstrated here
The in-memory-only B-tree	Building a Database Engine: Storage & Query Fundamentals Chapters 5–6 (Course 1) — the real `BTreeNode` class, confirmed here to have no on-disk representation at all
flush() vs. a genuine OS-level fsync	Technical Support: Backup & Disaster Recovery Basics — the same "silence isn't proof of success" caution, applied one layer lower, to a single write instead of a whole backup job

Hands-On Exercises

Exercise 1

Rebuild the index used in Finding 1 (via db.create_index('accounts', 'id'), run after the "crashed" row 2 was inserted) and re-run the query for id = 2. Confirm it now returns the row correctly, and explain in your own words why this is a working manual fix rather than a real guarantee.

📄 View solution

Exercise 2

Construct a scenario with two indexes on the same table (e.g. on id and on name) and simulate a crash between step 1 and the FIRST of the two index updates in the loop — so both indexes are stale, not just one. Verify both queries return wrong (empty) results for the crashed row.

📄 View solution

Exercise 3

Read Course 1's own HeapFile.write_page() method directly and confirm, by inspection, that it never calls os.fsync() anywhere. Then look up what os.fsync() actually does in Python's own documentation, and explain in one paragraph, in your own words, why flush() alone doesn't guarantee durability against a real power loss.

📄 View solution

Chapter 1 Quick Reference

ACID: Atomicity, Consistency, Isolation, Durability — four guarantees Course 1's engine never made
Verified Finding 1: an interrupted two-step insert leaves the index silently wrong — the row is really on disk, but a query using the index returns nothing
Verified Finding 2: the entire B-tree index lives only in memory — it vanishes completely on every restart, while the underlying table data survives intact
Named, not tested directly: write_page() calls flush() but never os.fsync() — a real, well-documented OS-level durability gap
Next chapter: Write-Ahead Logging — the real mechanism that makes a write durable before the data itself is even touched