The Engineering of Database Transactions: MVCC and Distributed Consensus
A database transaction gives developers an extremely powerful optical illusion: the ability to pretend that they are the only user executing code on the database, and that their code will either succeed perfectly or never happen at all. Behind this simple abstraction is decades of Computer Science research into Multi-Version Concurrency, Distributed Consensus, and Write-Ahead Logging.
Part 1: The MVCC Revolution
In the 1980s, if Transaction A wanted to read a row, and Transaction B wanted to write to that same row, Transaction A had to wait for B to finish. This "Pessimistic Locking" destroyed throughput on busy tables.
Modern databases (PostgreSQL, MySQL InnoDB) solved this with Multi-Version Concurrency Control (MVCC).
When Transaction B updates a row, it does NOT overwrite the old data. Instead, it creates a brand new, invisible version of the row marked with its Transaction ID. When Transaction A tries to read the row, the database checks its snapshot timestamp, ignores B's uncommitted new version, and instantly hands A the old version. Readers never block Writers, and Writers never block Readers.
Part 2: The Write-Ahead Log (WAL)
How does a database guarantee Durability if a power outage happens 1 millisecond after a COMMIT?
Updating the actual Data Files on a spinning hard drive or SSD (a "random I/O" write) is extremely slow. If the database blocked the transaction waiting for the Data Files to update, performance would crawl entirely.
Instead, the database performs an incredibly fast, append-only sequential write to the Write-Ahead Log (WAL). The database says "I intend to change Row 5 to X". Once the WAL acknowledges the disk write, the database tells the client "Commit Successful". It updates the actual Data Files in RAM, and lazily flushes them to SSD in the background minutes later. If the power fails, the database reboots, reads the WAL, and mathematically reconstructs the lost RAM state.
Part 3: The Slow Death of Two-Phase Commit (2PC)
When you outgrow a single database server and split your users across two microservices (e.g., Orders DB and Payments DB), you lose ACID transactions. The industry initially tried to solve this with Two-Phase Commit (2PC).
In 2PC, a Coordinator tells Orders DB and Payments DB to "Prepare" (Phase 1). If both say yes, it tells both to "Commit" (Phase 2).
It is mathematically provable that 2PC is a blocking protocol. If the Coordinator crashes in between Phase 1 and Phase 2, the Orders DB and Payments DB are legally obligated to hold their row locks forever until the Coordinator comes back online. This single point of failure and severe lock-contention made 2PC completely unviable for large-scale cloud infrastructure.
Part 4: The Outbox Pattern & Sagas
Modern microservices discarded 2PC in favor of Eventual Consistency using the Saga Pattern and the Transactional Outbox.
Instead of locking two databases simultaneously, the Orders Service writes the new Order and a "Payment Requested" Event into its own database in a perfectly safe, single-node local transaction. A background worker reads the Event from the database and publishes it to a message broker (like Kafka). The Payments Service receives it, charges the card, and emits its own Event.
If the payment fails, the system triggers a Compensating Transaction—the Orders Service receives the failure event and changes the Order status to "Cancelled". We sacrifice strict real-time isolation for massive availability and infinite horizontal scalability.