A Transaction, Inside the Engine

The practical lesson Transactions and ACID draws a transaction as BEGIN … COMMIT. Inside the engine it is a sequence of six things, each owned by a different subsystem: the transaction manager hands out an id and a state; the snapshot machinery decides what this transaction may see; the executor reads through that snapshot and writes new row versions stamped with the id; the lock manager serialises conflicting writers; the WAL makes the writes durable; COMMIT ties them together with one record.

Nothing in this sequence touches the table pages a second time at commit. That single design fact explains most of what follows: a commit is cheap because the verdict is stored in one place, and every row version carries a reference to that place instead of carrying the verdict itself.

The transaction id is the engine's name for "this unit of work". It is assigned from a global counter under a spinlock, so ids are monotonically increasing and the order of two ids is the order in which the transactions first wrote. The id is stamped onto every row version the transaction creates or invalidates and into every WAL record it emits. It is never stored *with* a verdict; the verdict lives elsewhere and is looked up.

Alongside the counter, shared memory holds the running list: one slot per session, containing the xid that session is currently running (or nothing). Taking a snapshot means walking this array under a shared lock and copying the ids that are in progress — typically a few dozen entries, a few hundred bytes. That copy is what "snapshot" means physically; the next lesson on MVCC Internals: Version Chains and Snapshots spells out how it is used.

PostgreSQL keeps the verdict per transaction in pg_xact (called pg_clog before version 10): a flat file of two-bit entries, indexed by xid. Four states fit in two bits — in progress, committed, aborted, and sub-committed for subtransactions — so an 8 KB page records 32,768 transactions and a busy system producing a thousand write transactions per second fills a page every half minute. The pages are cached in a small shared buffer (SLRU) and written like any other page; the WAL commit record is what makes the bit durable, so a crash before the page is written is repaired by redo.

Consulting pg_xact costs a lookup on every visibility check, and it is a random access into a file that can be gigabytes long. The fix is the hint bit: the first reader that finds a row whose creator has a final verdict writes HEAP_XMIN_COMMITTED or HEAP_XMIN_INVALID into the row header's infomask. The page is dirtied, but every subsequent reader skips the lookup. This is why the first sequential scan after a bulk load is slow and writes a lot: it is setting hint bits on every row.

Four steps, in a fixed order. Write the commit record to the WAL buffer, after every data record the transaction produced. Flush the WAL to disk up to that record — the fsync; this is the only synchronous disk write in the whole transaction, and it is why commit latency is measured in log-flush time (a few hundred microseconds on a fast SSD, milliseconds on a spinning disk, and the reason synchronous_commit = off exists). Mark the transaction committed in the status table. Remove the id from the running list — from this instant a new snapshot treats the transaction as finished — and release its locks, waking anyone queued on them.

Nothing is written to the table pages. Every row version the transaction created still says xmin = 1038; readers now find 1038 marked committed and believe the row. Ten rows or ten million, commit does the same four steps. The table pages will be written later, by the checkpointer or when the buffer pool evicts them — see Write-Ahead Logging for why that is safe.

In PostgreSQL, ROLLBACK undoes almost nothing on disk. An abort record goes to the WAL (it need not be flushed — a crash produces the same outcome), the status bits are set to aborted, the id leaves the running list, locks are released. The row versions the transaction wrote stay exactly where they are, stamped with an xid that is now aborted; every visibility check will treat them as if they never existed, and VACUUM will remove them later like any other dead tuple. Rows the transaction deleted have their xmax pointing at an aborted transaction, which the visibility rule reads as "not deleted".

This is why a rollback of a million-row UPDATE returns in milliseconds and why it leaves a million dead tuples behind. It is also why a crash is just an implicit rollback: no undo phase is needed, because the aborted rows are indistinguishable from any other invisible ones.

InnoDB updates rows in place, so ROLLBACK has real work to do. Each row carries a 6-byte DB_TRX_ID and a 7-byte DB_ROLL_PTR pointing at an undo log record that holds the previous image of the row. Before every change the old image is written to an undo segment, itself protected by the redo log. ROLLBACK walks the transaction's undo records from newest to oldest and re-applies each old image, restoring the row and its index entries; only then is the transaction marked aborted and its locks released.

The cost is proportional to the work being undone: rolling back a million-row UPDATE takes about as long as running it did, sometimes longer. The benefit is the mirror image of PostgreSQL's: the table contains no aborted rows to clean up, and old versions live in undo space rather than in the table. After a crash, InnoDB recovery has an explicit undo phase for transactions that were in flight — see Crash Recovery.