Transactions & ACID in a File-Based Engine

sqlite1-1 flagged a caveat about copying the database file mid-write. This chapter delivers the full explanation — and corrects a genuine, common misconception along the way.

Countering a Real Misconception

"It's just a file, how can it be reliably transactional?" is a real, common skepticism worth answering directly rather than dismissing. SQLite genuinely provides full ACID guarantees — Atomicity, Consistency, Isolation, Durability — despite having no server process at all. This is a real, extensively tested, well-documented fact, not a marketing claim.

Atomicity — All or Nothing, Even on a Crash

A transaction (BEGIN ... COMMIT) either fully applies or has no effect at all — even if the application crashes, the operating system crashes, or power is lost mid-write. This is the specific guarantee that answers the "just a file" skepticism directly: SQLite achieves it through careful, deliberate low-level file operations, not by simply hoping a write completes.

The Rollback Journal

SQLite's original mechanism (still the default outside WAL mode): before modifying the actual database file, SQLite first writes the original, pre-modification content of the pages about to change into a separate journal file. If the transaction completes successfully, the journal is deleted. If the process crashes mid-write, the next time the database is opened, SQLite detects the leftover journal file and automatically uses it to roll the (possibly partially-written) main file back to its last known-good state.

This is exactly the mechanism sqlite1-1's own caveat pointed toward: copying the main database file mid-write, without its accompanying journal, can capture a half-written, inconsistent state. A copy taken while the database is idle — or via a proper backup mechanism — avoids this entirely.

WAL Mode's Own Different Durability Mechanism

sqlite1-4 covered WAL mode for its concurrency benefits; this chapter revisits it for its own durability angle. Rather than a before-image journal, new changes are appended to the WAL file, and the original main database file is left untouched until a checkpoint. A crash mid-write in WAL mode still leaves a valid, intact main database file — the old, pre-transaction state — with the WAL file itself either fully containing a completed transaction (recoverable) or an incomplete one (safely discarded on the next open). Same atomicity guarantee, a genuinely different physical mechanism from the rollback journal.

Durability & fsync

The actual mechanism making any of this real, rather than theoretical: SQLite calls fsync() (or the platform equivalent) at the right moments to force the operating system to actually flush data to physical storage, rather than leaving it sitting in an in-memory OS buffer that could be lost on a power failure.

PRAGMA synchronous controls how aggressively this happens: FULL is safest and slowest; NORMAL is a real, documented, still-safe-in-WAL-mode compromise; OFF is fast but genuinely risks corruption on power loss. This is an honest, concrete acknowledgment that durability has a real, tunable performance cost — the same kind of trade-off postgres1-11's own synchronous-vs-asynchronous replication material illustrated for a completely different system.

Isolation & Consistency

SQLite transactions provide real isolation — a reader never sees a partially-completed transaction's own intermediate state — and real consistency, with constraints (including foreign keys, when enabled) enforced within a transaction. This isn't covered in as much depth here, since it isn't SQLite's own most distinguishing feature the way atomicity-and-durability-despite-being-a-file is.

Hands-On Exercises