ACID Transactions Visualization
Step through the four ACID guarantees — Atomicity, Consistency, Isolation, Durability — with undo/redo logging, 2PL and MVCC isolation, isolation levels, and ARIES crash recovery.
Continue with the guided workspace
Use the paired workspace to connect this guide's mental model with live component state, visible signals, and the outcome each step or scenario is meant to prove.
A database transaction is a unit of work that must behave as a single, reliable step even when concurrency and crashes fight against it. The four ACID guarantees — Atomicity, Consistency, Isolation, and Durability — are the promises that make that reliability possible, and each is enforced at a specific stage of a transaction’s journey from client to durable disk.
Mental Model
A transaction is a unit of work that must behave as a single, reliable step despite two hazards: concurrent transactions touching the same data, and crashes that interrupt work mid-flight. ACID is the set of four promises that make this true. The guarantees emerge from an ordered pipeline: a Client submits work to a Transaction Manager (lifecycle), which consults Concurrency Control (isolation), modifies pages in the Buffer Pool (in-memory staging), appends undo/redo records to the WAL (log before disk), and fsyncs that log to Disk Storage before acknowledging the commit. No single component provides ACID — it is the cooperation of the lifecycle, the concurrency controller, the log, and durable storage.
Components and Why They Exist
Client (A and B)
The client is the application that submits transaction operations — BEGIN, WRITE, and COMMIT — to the database and depends on the ACID guarantees to get a reliable result. It sends each command to the Transaction Manager and waits for a commit or abort acknowledgement before it can safely proceed. A second client appears in the isolation steps to drive the concurrency lessons: it reads the same rows that the first client is writing, which is exactly the situation that exposes dirty reads and other anomalies. In the demo, watch the operation chip on each client change from begin to write to commit (or abort), and follow the colored message particles leaving the client toward the Transaction Manager.
Transaction Manager
The Transaction Manager owns the lifecycle of every transaction and is the backbone of Atomicity. It moves each transaction through a state machine — active, then partially committed while the log is flushed, then committed — or, on a failure, failed and then aborted after rollback. Along the way it coordinates logging, locking, and the durability flush, and it refuses to tell the client “committed” until the WAL is safely on disk. In the demo, the state badge on the Transaction Manager is the clearest progress signal: it transitions ACTIVE → FLUSHING → COMMITTED on a clean commit, or FAILED → ABORTED when a constraint violation forces a rollback.
Concurrency Control
Concurrency Control enforces Isolation. It is the gatekeeper that decides whether a transaction may read or write a given row, so that concurrent transactions cannot observe each other’s uncommitted or intermediate state. It works in one of two modes: Two-Phase Locking (2PL), where it grants shared and exclusive locks and blocks conflicting requests, or Multi-Version Concurrency Control (MVCC), where it keeps multiple versions of each row and gives each reader a snapshot instead of blocking. It also tracks the active ANSI isolation level. In the demo, the Concurrency Control panel switches between a lock table — showing X and S locks as granted or waiting — and a version chain showing committed and uncommitted versions, with the snapshot a reader selected highlighted.
Buffer Pool
The Buffer Pool holds data pages in memory so that reads and writes are fast, and it tracks which pages are dirty — meaning modified in memory but not yet flushed to disk. When the Transaction Manager issues a read or write, the Buffer Pool serves or updates the in-memory copy; modified pages stay dirty until a later flush writes them to Disk Storage. In the demo, page slots turn yellow when they become dirty and return to green after a rollback or a flush, and the changed slot flashes for a moment the instant a mutation lands so you can see exactly which row the current operation touched.
Write-Ahead Log (WAL)
The Write-Ahead Log is the single source of truth for both Atomicity and Durability. Before any data page is modified, the Transaction Manager appends a log record here that carries both undo information (how to reverse the change) and redo information (how to replay it). On commit, a final commit record is appended and the whole log is fsynced to disk; on abort, the undo portion of each record is read back to roll the changes away. In the demo, watch log records appear with a BUF badge while they are still only in memory and flip to FLUSH once they are durable, and notice the flushed-LSN counter advance each time the log is fsynced — everything up to that number is safe.
Disk Storage
Disk Storage is the stable storage that survives crashes, and it is where Durability is actually achieved — but only once the WAL has been fsynced here before the commit is acknowledged. It receives log fsyncs from the WAL and page flushes from the Buffer Pool. If a crash interrupts work, the on-disk data pages may be stale, so on restart the database replays the committed WAL records (redo) to rebuild them and reverses any uncommitted ones (undo). In the demo, watch the disk pages turn red and show CRASHED when a power loss is simulated, then see them restored to green as REDO particles replay the committed log records during the recovery step.
Edge Cases and Misconceptions
- Atomicity ≠ durability. Atomicity is about all-or-nothing within a transaction (undo on abort); durability is about surviving crashes after commit (redo + fsync). They share the WAL but answer different questions.
- Consistency depends on A and I. Consistency cannot hold without Atomicity (failed work is rolled back) and Isolation (concurrent transactions don’t see half-applied invariants). The four properties are interdependent, not independent.
- Repeatable Read is not always Serializable. The SQL standard only requires Repeatable Read to prevent non-repeatable reads; phantoms may still occur. PostgreSQL’s MVCC Repeatable Read happens to prevent phantoms too, but pure Snapshot Isolation permits write skew, which only Serializable Snapshot Isolation (SSI) closes.
- MVCC is not lock-free. MVCC removes read-write blocking, but writers still conflict on the same row (the first committer wins; the second aborts with a serialization failure). It trades lock contention for version storage and vacuuming.
- The log is the truth, not the data pages. After a crash, on-disk data pages may be stale or torn; the WAL on stable storage is authoritative. Recovery replays redo for committed transactions and undo for uncommitted ones, so the data pages are rebuilt to match the log.
- 2PC does not make distributed ACID cheap. Two-Phase Commit extends atomicity across nodes, but it adds a blocking prepare phase and is fragile: a coordinator or participant failure during commit can leave the whole transaction blocked until recovery.
Common Questions
What does each letter of ACID guarantee for a database transaction?
Atomicity means all operations in a transaction succeed or none take effect (rollback on failure). Consistency means the transaction moves the database from one valid state to another, preserving declared invariants. Isolation means concurrent transactions do not observe each other's intermediate, uncommitted results. Durability means once a transaction is committed, its changes survive any subsequent crash.
How does a write-ahead log (WAL) provide both atomicity and durability?
The WAL records undo and redo information for every change before the data page is modified (the write-ahead rule). For atomicity, the undo portion lets the database reverse partial changes on abort. For durability, the redo portion lets it replay committed changes after a crash. Because the log is flushed (fsynced) to stable storage before a commit is acknowledged, the log on disk is sufficient to reconstruct any committed transaction even if the in-memory dirty pages were lost.
What is the difference between Two-Phase Locking (2PL) and MVCC for isolation?
2PL enforces isolation with shared (S) and exclusive (X) locks: a writer holds an X lock that blocks other readers and writers until commit, preventing dirty reads through blocking. MVCC instead keeps multiple versions of each row and gives each transaction a snapshot, so readers see a consistent committed version without blocking writers. MVCC trades lock contention for version storage and vacuuming overhead, and is the default in PostgreSQL, MySQL/InnoDB, and Oracle.
What are the four ANSI SQL isolation levels and which anomalies does each prevent?
Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable reads. Repeatable Read also prevents non-repeatable reads (and PostgreSQL's MVCC implementation prevents phantoms too). Serializable prevents all anomalies — dirty reads, non-repeatable reads, phantom reads, and serialization anomalies — so execution is equivalent to running transactions one at a time. Higher levels are safer but reduce concurrency.
What is a dirty read and why is it dangerous?
A dirty read occurs when a transaction reads data written by another transaction that has not yet committed. If that other transaction later aborts and rolls back, the reader has acted on a value that never officially existed, corrupting the reader's logic. It is the weakest isolation anomaly and is only permitted at Read Uncommitted.
How does ARIES crash recovery work?
ARIES performs three phases after a crash: Analysis reads the WAL to determine which transactions committed and which were in progress. Redo replays all logged changes to reconstruct the exact state at the crash. Undo then reverses the changes of transactions that never committed, using the undo portion of their log records. The result is that all committed work survives and all incomplete work is erased.
Why is ACID not always used in distributed and NoSQL systems?
Strong ACID guarantees have a cost: higher isolation reduces concurrency, and durability adds an fsync latency to every commit. Many NoSQL systems choose BASE (Basically Available, Soft state, Eventually consistent) to gain availability and throughput under the CAP tradeoff. When distributed atomicity is required, Two-Phase Commit (2PC) coordinates a prepare/commit across nodes, but it is slow and fragile because a single coordinator or participant failure can block the whole transaction.