Transactional Database Architecture in Depth

Why architecture matters here

A transactional engine turns a promise — these statements all happen or none of them do, and nobody observes a half-finished version — into a physical arrangement of a log file, a page cache and a lock table. This article walks that arrangement: what each ACID letter costs in mechanism, why the write-ahead log's ordering rule is durability, how ARIES-style recovery reconstructs a crashed instance, what the three families of concurrency control charge you, and which anomaly each isolation level does and does not permit — the part most often stated wrongly.

ACID as the engine implements it

The four letters are delivered by three mechanisms, and knowing which is which tells you what to tune.

Atomicity is undo. The engine must be able to erase a partial transaction, so it must remember the prior state of everything it changed — in a rollback segment (Oracle, InnoDB) or interleaved with redo information in one log stream (ARIES-style systems).

Consistency is mostly not the database's job. Declared constraints, foreign keys and triggers are enforced; an application invariant such as "these two balances always sum to 5000" is invisible to the engine and holds only if isolation is strong enough or the code takes explicit locks. Most write-skew bugs are consistency violations nothing promised to catch.

Isolation is concurrency control — locking, versioning, validation.

Durability is the log and one flush. Nothing is durable because it was written to a data page; it is durable because a commit record reached stable storage.

The write-ahead log: ordering is the guarantee

Every change generates a log record stamped with a monotonically increasing LSN and appended to a sequential file. The record is physiological — physical about which page it touches, logical about what it did inside that page ("insert this tuple into slot 4 of page 118" rather than a byte image), which keeps records small while keeping redo cheap. Each data page carries in its header the LSN of the last record applied to it, the pageLSN. Two ordering rules then do all the work.

The write-ahead rule. The log record describing a change to page P must be on stable storage before P itself is written out. Without it a crash can leave a modified page on disk with no record of how to undo it.

The commit rule. All of a transaction's records, up to and including its commit record, must be flushed before the client is told the commit succeeded. Without it you acknowledge work you cannot reconstruct.

Everything else about the WAL is a performance argument: the log is one sequential append shared by every session, while the pages it protects are scattered random writes that can be deferred, coalesced, and written once for many updates.

The architecture: every layer explained

Read the diagram as three layers. On top, the transaction manager tracks each transaction's state, its snapshot and its position in the log, while the requested isolation level decides which conflicts it must police. In the middle, concurrency control — version visibility and the lock manager together — decides who sees what and who waits. Underneath, the write-ahead log, the undo information and the recovery passes turn all of it into something that survives power loss, with vacuum reclaiming versions nobody can see any more and two-phase commit stretching atomicity across nodes. Each piece is unpacked below.

Steal, no-force, and the debt they create

Two buffer-pool policies decide what recovery has to do. Steal lets the pool evict a dirty page belonging to an uncommitted transaction, putting uncommitted data on disk — which is what allows a transaction larger than memory, and exactly why undo information must exist. No-force means commit flushes only the log, not the transaction's data pages — which turns many random writes per commit into one sequential one, and exactly why redo information must exist.

Every serious engine picks steal/no-force and pays with a real recovery algorithm. Pool mechanics themselves — pins, latches, replacement — are covered in buffer pool architecture.

ARIES recovery: analysis, redo, undo

Checkpoints keep restart bounded. A fuzzy checkpoint does not flush the pool; it records the dirty page table (each dirty page with the recLSN of the oldest record that dirtied it) and the transaction table (each live transaction with its latest LSN). Restart then makes three passes.

Analysis scans forward from the checkpoint, rebuilding both tables as of the crash: which transactions were still live (the losers) and which pages may be dirty.

Redo starts at the smallest recLSN in the dirty page table and repeats history, reapplying every logged change — including changes made by losers. For each record it compares the record's LSN with the page's pageLSN and skips the page when the change is already there, which makes redo idempotent. Repeating history reconstructs the exact state at the crash, giving undo a well-defined starting point.

Undo rolls the losers back in reverse LSN order. Each undone action writes a compensation log record carrying an undoNextLSN pointer to the next record still owing a rollback. CLRs are redo-only and never themselves undone, so a crash during recovery resumes where the pointers left off rather than undoing the undo.

Three families of concurrency control

Two-phase locking. A growing phase acquires locks and a shrinking phase releases them, and no lock is taken after the first release; that discipline alone yields conflict-serializable schedules. Real engines use strict 2PL, holding exclusive locks until commit, because releasing early lets someone read data a rollback then erases — cascading aborts. The lock manager is a hash table keyed by resource with a mode compatibility matrix, intention locks (IS, IX, SIX) so a table-level request can tell what sits beneath it, and escalation when row locks grow too numerous to track. The cost is blocking in both directions.

MVCC. Writers create versions instead of overwriting, so a reader never waits and never blocks a writer. The cost relocates: version storage (in-heap tuples in PostgreSQL, undo segments in InnoDB and Oracle), a visibility test on every tuple examined, and a collector that must keep up. See MVCC. Note the limit — MVCC removes read-write conflicts only; write-write conflicts still resolve by blocking or aborting.

Optimistic control. Run against a private workspace, validate at commit that the read set was not modified, write only then. Lock acquisition leaves the hot path, replaced by wasted work: under contention a transaction executes fully and is then discarded. It wins when conflicts are genuinely rare and degrades badly when they are not.

End-to-end transaction flow

Trace one transaction on PostgreSQL at REPEATABLE READ, which is snapshot isolation.

BEGIN allocates a transaction entry; the snapshot is taken at the first statement, not at BEGIN, and records which transaction IDs were still in flight then.

SELECT balance FROM accounts WHERE id = 42 walks the version chain and returns the newest version whose creating transaction committed before the snapshot and whose deleting transaction did not: 500, no lock taken. A concurrent session now updates that row and commits; our snapshot is unchanged, so an identical second SELECT still returns 500 — no non-repeatable read.

UPDATE accounts SET balance = balance + 100 WHERE id = 42 is different. A write must apply to the current row, not the snapshot's, and the current row was replaced by a transaction that committed after our snapshot began. PostgreSQL raises could not serialize access due to concurrent update; at READ COMMITTED it would instead re-read the newest committed version, re-check the WHERE clause and proceed.

COMMIT appends a commit record, flushes the log to that record's LSN — possibly in one flush shared with other committing sessions — and only then acknowledges; the dirty pages are still in memory. Crash before that flush and the transaction never happened, because recovery finds no commit record. Crash after it and redo rebuilds the change from the log.

Isolation levels and the anomalies they permit

A level is defined by what it forbids, not by how it is built. Dirty read: seeing a version written by a transaction that has not committed and may still roll back. Non-repeatable read: reading one row twice and getting different values because another transaction committed an update in between. Phantom: re-running a predicate query and getting a different set of rows because someone inserted or deleted a row matching it — the same problem at range granularity, which is why preventing it needs range or predicate locks rather than row locks.

Two footnotes cause most of the confusion. First, an implementation may be stronger than the level asks for: PostgreSQL never produces dirty reads at all, so READ UNCOMMITTED behaves as READ COMMITTED, and InnoDB's REPEATABLE READ takes next-key (gap) locks on locking reads, suppressing phantoms the standard would permit. Second, the standard's phenomena were written in terms of a lock-based implementation; the 1995 critique by Berenson and colleagues showed the loose reading admits schedules nobody wants, and that snapshot isolation does not fit anywhere in the table.

READ COMMITTED earns its own warning: it takes a fresh snapshot per statement, so a read-modify-write split across two statements can lose an update outright. UPDATE t SET n = n + 1 is safe because the engine re-reads the current row under lock; SELECT n then UPDATE t SET n = 4 is not, unless the read used FOR UPDATE.

Snapshot isolation is not serializable

Under snapshot isolation a transaction reads from a snapshot fixed at its start and commits only if no concurrent transaction wrote any row it wrote (first-committer-wins). That kills dirty reads, non-repeatable reads, phantoms and lost updates, which is why it gets mislabelled serializable. PostgreSQL's REPEATABLE READ is snapshot isolation; so is Oracle's SERIALIZABLE.

What it does not prevent is write skew. Two transactions read an overlapping set, each checks an invariant that still holds in its own snapshot, and each writes a different row. There is no write-write conflict, so both commit and the invariant breaks. The canonical case is an on-call roster requiring at least one doctor on duty: two doctors each read "2 on call", each sets their own row to off duty, and the shift ends up empty. No serial order of those two transactions produces that outcome, which is precisely what serializable rules out.

PostgreSQL's SERIALIZABLE adds serializable snapshot isolation: it tracks reads with predicate (SIREAD) locks and watches for the dangerous structure — a transaction with both an incoming and an outgoing read-write anti-dependency — aborting a participant when one appears. It is conservative, so some aborts are false positives, and tracking read sets costs memory that may be coarsened under pressure. The application-side requirement is absolute: catch serialization failure (SQLSTATE 40001) and retry the whole transaction from BEGIN. Retrying the failed statement alone is not a retry. See transaction isolation architecture.

Operational limits: deadlocks, fsync, old snapshots

Deadlock: detect or prevent

Anything that lets a transaction hold one lock while waiting for another can cycle. Detection builds a wait-for graph and searches for cycles — InnoDB checks eagerly at wait time, PostgreSQL waits deadlock_timeout (one second by default) since most waits clear themselves — then aborts a victim. Prevention orders instead: wound-wait and wait-die use transaction age to decide whether a waiter may wait or must abort, which distributed systems prefer because a global wait-for graph is expensive. The application-level version is free: lock rows in a deterministic order, ascending primary key, and the classic two-account transfer deadlock disappears. MVCC does not exempt you — two transactions updating the same two rows in opposite order still deadlock. See deadlock detection.

The fsync floor and group commit

The commit rule costs one durable write per commit, and durable means the device acknowledged stable media, not a volatile cache. Commit latency therefore has a hard floor at the device's sync latency — hundreds of microseconds on NVMe, milliseconds once a platter or a network hop is involved. Group commit breaks the tie between commit rate and sync rate: sessions arriving during a flush queue behind it and one flush covers the group, so throughput becomes group size divided by sync latency and rises with concurrency while individual latency does not. See group commit. Settings such as synchronous_commit = off or innodb_flush_log_at_trx_commit = 2 acknowledge before the log is durable, trading a bounded window of recent commits for throughput — defensible chosen deliberately, a data-loss bug chosen by accident.

Long-running transactions

In an MVCC engine a snapshot promises that everything visible to it stays readable, so the oldest live snapshot pins the collector's horizon for the entire instance. One idle-in-transaction session — BEGIN, one SELECT, then lunch — blocks cleanup of dead versions produced by every other transaction, and the symptoms surface later and elsewhere: tables and indexes grow, scans read mostly dead tuples, index-only scans stop working, query times drift up with no plan change. Undo-based engines fail differently for the same reason, either growing the rollback segment or overwriting versions an old reader needed and killing it with a snapshot-too-old error. Keep transactions short, never do network I/O inside one, and enforce it with idle_in_transaction_session_timeout. See vacuum and bloat.