Transactions & MVCC Internals
What a transaction is inside the engine: lock tables, waits-for graphs, version chains, snapshots, dead tuples and the same workload under three isolation levels.
BEGIN hands out a transaction id and a snapshot; reads consult it, writes stamp it onto row versions and into the WAL; COMMIT is one log record, one fsync and one bit flip in the transaction status table — and ROLLBACK, in PostgreSQL, writes almost nothing at all.
Interleave four operations from two transactions and €20 disappears; the engine's job is to allow only interleavings whose result equals some serial order, and the two ways to do that — refuse conflicting steps (locking) or keep every version and check afterwards (multi-version / optimistic) — are the roots of every isolation mechanism.
A lock is a row in a hash table keyed by the resource, with a list of who holds it in which mode and a queue of who is waiting; the compatibility matrix decides grant or wait, intention locks let row and table granularity coexist, and PostgreSQL avoids the table entirely for row locks by writing the holder's id into the row itself.
Two transactions each holding what the other needs will wait forever; the lock manager's wait queues already encode "who waits for whom" as a directed graph, a depth-first search finds the cycle, and the engine breaks it by aborting one participant — PostgreSQL after a one-second grace period, InnoDB immediately.
If a row is never overwritten but versioned, a reader can be handed the version that was current when it started and never wait for a writer; the version chain is a linked list with a creating and a superseding transaction id on each node, the snapshot is three numbers and a list, and the visibility rule is a dozen lines that every read in the engine runs.
Under MVCC an UPDATE is an insert plus a stamp and a DELETE is only a stamp; neither frees a byte, so every write leaves a dead version behind that some later process — VACUUM, autovacuum, InnoDB purge — has to find, remove from the page and every index, and hand back to the free space map before the table stops growing.
The same snapshot machinery produces three isolation levels by changing one thing — when the snapshot is taken — plus one rule for writers; Serializable then adds either dependency tracking that aborts (PostgreSQL) or locking reads that block (InnoDB), which is why the same level name costs retries on one engine and waits on the other.