Before diving into the how, we must understand the what. What does a relational database guarantee? It guarantees ACID. This is the formal contract between the database engine and every application that uses it. Every internal mechanism we discuss in this lesson exists to uphold one or more of these four properties.
Atomicity (All or Nothing)
A transaction is a group of operations treated as a single unit. Atomicity guarantees that either all operations in the group succeed, or none of them are applied. There is no halfway state.
Example: Transferring $10 from Account A to Account B requires two steps: deduct from A, add to B. If the power fails after the deduction but before the addition, the system must roll back the deduction so money doesn't vanish. The transaction either completes fully or leaves no trace.
Consistency (Follow the Rules)
The database must always move from one valid state to another valid state, obeying all defined rules — constraints, foreign keys, data types, and triggers.
Example: If a NOT NULL constraint exists on an email column, no transaction can leave a row with a null email. If a transaction tries to do so, the entire transaction is rejected.
Isolation (Don't Interfere)
Multiple transactions running concurrently should not corrupt each other's view of the data. The result of running transactions concurrently should be equivalent to running them one at a time in some serial order.
Example: If two users simultaneously try to buy the last concert ticket, isolation ensures only one succeeds. Without it, both could read "1 ticket remaining," both decrement to 0, and both get a confirmation — overselling by one.
Durability (Saved Forever)
Once a transaction is committed (the database has confirmed success to the client), the changes are permanent — even if the server loses power one millisecond later. The data is safely on persistent storage.
The key tension: Atomicity and Durability pull in opposite directions. Atomicity requires the ability to undo changes. Durability requires changes to survive crashes. The Write-Ahead Log (covered next) is the mechanism that satisfies both simultaneously.
Interviewers often ask: "Which ACID property is hardest to implement, and why?" The nuanced answer is Isolation, because it exists on a spectrum (isolation levels) and requires careful trade-offs between correctness and performance. Durability is also non-trivial — it requires understanding the difference between write() and fsync() at the OS level, since data can be in the OS page cache (not yet on physical disk) even after a write call returns.