In 2003, a software engineer at a brokerage firm discovered that a database transaction had partially completed. A customer's account had been debited. The corresponding credit to another account had not happened. The money had, in a narrow technical sense, ceased to exist. It was not in the source account. It was not in the destination account. It had vanished in the gap between two database operations that were supposed to be inseparable.
This kind of failure is so catastrophic for financial systems that an enormous amount of database engineering exists specifically to prevent it. The engineering goes by a four-letter acronym: ACID. Atomicity, consistency, isolation, durability. These are the four properties that a database management system guarantees for transactions, and they represent a contract between the database and every application that uses it.
Most developers who work with databases have heard of ACID. Most can recite the four words. Few could tell you what mechanisms inside the database engine actually produce these guarantees, and fewer still could tell you what it costs to maintain them and why some systems choose not to.
The mechanisms are worth understanding, not because you need to implement a database, but because every performance trade-off, every mysterious locking behavior, every puzzling edge case you will ever encounter in database work traces back to the machinery behind these four letters.
What transactions are actually for
Before the mechanisms, the concept.
A transaction is a unit of work that the database treats as indivisible. You group a set of operations together, declare them a transaction, and the database guarantees that either all of them happen or none of them happen. There is no state in between.
The classic example is a bank transfer: debit account A, credit account B. If you execute these as two separate database operations without wrapping them in a transaction, the system can crash between the debit and the credit, leaving you with the debit done and the credit not. If you wrap them in a transaction, a crash means the entire transaction is rolled back: the debit is undone, the credit was never applied, and both accounts are in the state they were before you started.
Transactions also provide isolation between concurrent operations. Without isolation, two transactions executing simultaneously can interfere with each other in ways that produce incorrect results. If one transaction is in the middle of a transfer and another transaction reads the accounts at that moment, it might see the account after the debit but before the credit, observing a momentary state where money has vanished. With proper isolation, each transaction executes as if it were the only thing running against the database.
The atomicity and isolation properties are the two that require the most engineering to implement. Consistency, in the ACID sense, mostly means that transactions must maintain the invariants defined on the data (foreign key constraints, unique constraints, check constraints). Durability means that once a transaction has been committed, it stays committed even if the system crashes immediately after.
The write-ahead log
Durability and atomicity both require that the database can recover to a consistent state after a crash. The mechanism that makes this possible is the write-ahead log, commonly abbreviated WAL.
The write-ahead log is exactly what it sounds like: a log that records what the database is about to do before it does it. Before any change is applied to the database's data files on disk, a record of that change is first written to the log file. When the transaction commits, the log is flushed to disk. Only after the log entry is safely on disk does the commit return success to the application.
The name "write-ahead" refers to this ordering: the log is written ahead of the actual data modifications. Data in the database's in-memory buffer pool may be modified and may not be flushed to disk immediately. That is fine. The log is on disk. If the system crashes, the database can replay the log during recovery to bring the data files up to date with what had been committed.
This is called redo recovery. The recovery process reads the log forward from the last checkpoint (more on those shortly) and replays all the operations for transactions that were committed. Any changes that made it into the data files might be there already (if the buffer pool flushed them before the crash) or might not be (if they were still in memory). Replaying the log is idempotent: redoing an operation that was already applied produces the same result as if it had not been applied yet, so it does not matter whether a given change was already in the data files.
What about transactions that were in progress when the crash happened? They were started but never committed. Their changes might be partially applied to data files if the buffer pool had flushed some of their dirty pages before the crash. These partial changes have to be undone. The log also records enough information to undo operations, not just redo them. Undo records are written before the operation they describe so that if a crash happens mid-operation, the system can undo the partial work.
The recovery process is called ARIES in the academic literature (Algorithm for Recovery and Isolation Exploiting Semantics), developed by IBM researchers in 1992. It involves three passes over the log: an analysis pass that determines which transactions were active at the time of the crash and which pages in the buffer pool were dirty, a redo pass that replays all logged operations to bring the data files to the state they would have been in if no crash had happened, and an undo pass that rolls back the uncommitted transactions.
This three-pass recovery algorithm is what PostgreSQL, MySQL (InnoDB), SQL Server, and most other production databases implement, with variations. It is what allows a database to crash and restart without data loss for committed transactions, even if the buffer pool had not flushed all dirty pages to disk.
Checkpoints
A concern with the write-ahead log is that it grows without bound. If the database runs for months without any special action, the log will be enormous, and recovery after a crash will require replaying all of it, which could take hours or days.
Checkpoints are the solution. A checkpoint is a synchronization point where the database ensures that all changes made before the checkpoint are durably on disk in the data files, not just in the log. During a checkpoint, the database flushes all dirty pages from the buffer pool to disk. After the checkpoint completes, the log entries before the checkpoint are no longer needed for recovery, because the data files are guaranteed to reflect all those changes. Those log segments can be archived or discarded.
Recovery after a crash only needs to replay log entries from the last checkpoint forward. If checkpoints happen every few minutes (a typical configuration in PostgreSQL), recovery time is bounded at a few minutes of log replay regardless of how long the database has been running.
There is a trade-off. During a checkpoint, the database has to flush a potentially large number of dirty pages to disk. This generates a burst of disk I/O that can slow down ongoing transactions. Database systems tune checkpoint behavior to spread this I/O out over time, writing dirty pages gradually rather than all at once when the checkpoint triggers. This is what PostgreSQL's checkpoint_completion_target configuration parameter controls.
Locking and the problem it solves
Atomicity and durability are about crash safety. Isolation is about concurrency: ensuring that multiple transactions running simultaneously do not interfere with each other.
The straightforward approach to isolation is locking. Before reading or writing a piece of data, acquire a lock on it. Hold the lock for the duration of the transaction. Release all locks when the transaction commits or rolls back.
Locking has two modes. A shared lock (S lock, read lock) allows the holder to read the locked data. Multiple transactions can hold shared locks on the same data simultaneously, since reading does not interfere with other reading. An exclusive lock (X lock, write lock) allows the holder to write the locked data. An exclusive lock is incompatible with both shared locks and other exclusive locks: if any transaction holds a shared or exclusive lock on a piece of data, no other transaction can acquire an exclusive lock on it.
This model, two-phase locking (2PL), provides serializability: the highest level of isolation, where the result of running concurrent transactions is equivalent to some serial order of those transactions. Serializability is the gold standard for correctness.
It is also slow. If transaction A is reading a large table and holds shared locks on all of it, transaction B cannot modify anything in that table until A commits. If transaction B is modifying a row, transaction A cannot read that row until B commits. High contention leads to long wait chains and low throughput.
Deadlock is the other problem with locking. Transaction A holds a lock on row 1 and wants a lock on row 2. Transaction B holds a lock on row 2 and wants a lock on row 1. Neither can proceed. The database detects this cycle in the lock wait graph and kills one of the transactions, forcing it to roll back and retry.
Deadlock detection is O(n) in the number of transactions waiting (you are looking for cycles in a directed graph), and it is run frequently in high-contention systems. Some databases use timeout-based deadlock detection (if you have been waiting more than N milliseconds, assume deadlock and retry) rather than cycle detection, because it is cheaper.
Multiversion concurrency control
The alternative to locking-based concurrency is multiversion concurrency control, or MVCC. This is what PostgreSQL, Oracle, MySQL (InnoDB), and most modern databases use, at least for read-write concurrency.
The core idea of MVCC is that the database keeps multiple versions of each row simultaneously. When a transaction modifies a row, it does not overwrite the old version. It creates a new version, tagged with the current transaction ID, while keeping the old version accessible to transactions that started before the modification.
A reading transaction sees the version of each row that was current when that transaction started. It does not need to acquire any locks to read, because it is reading an older version that the writing transaction is not modifying. The writing transaction does not block the reader, and the reader does not block the writer.
This is the reason why reads in PostgreSQL almost never block writes and writes almost never block reads. MVCC separates the two operations in time by giving each transaction a consistent snapshot of the database as it existed at the moment the transaction began.
The snapshot is defined by the transaction ID. When a transaction starts, the database records the current transaction ID and uses it as the transaction's snapshot point. When the transaction reads a row, it looks for the most recent version of that row whose creation transaction ID is less than or equal to the snapshot transaction ID, and whose deletion transaction ID (if any) is greater than the snapshot transaction ID. This version was created before the reading transaction started and had not been deleted yet, so it represents the row's state at the snapshot point.
Writing transactions still need locks. If two transactions try to modify the same row, one of them has to wait. MVCC does not eliminate write-write conflicts, only read-write conflicts. The database acquires a row-level exclusive lock when updating a row, and other update transactions must wait for that lock.
Vacuum and dead tuple cleanup
MVCC creates a problem: old versions of rows accumulate. Every UPDATE operation creates a new version and leaves the old one in place. Every DELETE operation marks the row as deleted without immediately removing it. The old versions are "dead tuples," no longer visible to any active transaction but still taking up space on disk.
PostgreSQL's VACUUM process is responsible for cleaning up dead tuples. It scans tables looking for rows that are no longer visible to any currently active transaction (and therefore safe to remove) and reclaims their space.
VACUUM also updates a critical structure called the visibility map, a one-bit-per-page bitmap that tracks whether all tuples on a page are known to be visible to all transactions. Pages with the visibility map bit set can be skipped during table scans for cleanup purposes, making VACUUM faster over time.
There is also VACUUM ANALYZE, which updates table statistics. The query planner uses these statistics to estimate how many rows a query will process, which affects which query plan it chooses. Stale statistics lead the planner to make bad choices, which leads to slow queries. This is why running VACUUM ANALYZE after major bulk operations is a recommended practice.
AUTOVACUUM is the daemon that runs VACUUM automatically in PostgreSQL, triggered based on configurable thresholds. Transaction ID wraparound is the other reason VACUUM matters: PostgreSQL transaction IDs are 32-bit integers, so after about two billion transactions, they wrap around. AUTOVACUUM must freeze old tuples (mark them as permanently visible regardless of transaction ID comparison) to prevent old data from appearing to be in the future after wraparound.
Isolation levels
ACID isolation is not binary. The SQL standard defines four isolation levels, each providing stronger guarantees than the last, at correspondingly higher performance cost.
Read uncommitted is the lowest level. A transaction can read changes made by other transactions that have not yet committed. The anomaly this enables is called a dirty read: you read data that might be rolled back, leaving you with information about a state of the world that never officially existed. No major production database uses this level by default, and PostgreSQL does not implement it at all (treating read uncommitted as read committed).
Read committed prevents dirty reads. A transaction only sees committed data. But it can still see different versions of the same row in the same transaction if another transaction commits between two reads. This is called a non-repeatable read: if you read row X, another transaction commits an update to row X, and you read row X again in the same transaction, you see two different values. This is the default isolation level in PostgreSQL and Oracle.
Repeatable read prevents non-repeatable reads. The same query returns the same results for the duration of the transaction. But a phenomenon called a phantom read can still occur: if another transaction inserts rows that match your query's predicate between two executions of the same query, the second execution returns rows that the first did not. You are seeing "phantom" rows that were not there before.
Serializable is the highest level. It prevents all three anomalies and guarantees that the outcome is equivalent to some serial execution of all concurrent transactions. This is what two-phase locking provides by holding locks through the transaction. It is what PostgreSQL's SSI (Serializable Snapshot Isolation) provides without most of the locking overhead.
SSI, introduced in PostgreSQL 9.1, achieves serializable isolation by tracking the read and write dependencies between concurrent snapshot-isolated transactions and detecting patterns that indicate a serializability violation would result. When it detects such a pattern, it aborts one of the transactions, which must then retry. The genius of SSI is that it allows serializable isolation with far less blocking than two-phase locking, because reads still do not acquire locks. The cost is the occasional serialization failure that requires a retry.
Predicate locks and phantom prevention
Preventing phantom reads in a locking system requires locking not just individual rows but the absence of rows: locking the gap where a new row could be inserted.
Gap locks and next-key locks are the mechanisms MySQL InnoDB uses for this. A next-key lock on a row covers both the row itself and the gap before it. If a range query locks all rows with key values between 10 and 20, next-key locks on the range prevent other transactions from inserting rows in that range, preventing phantoms.
This is why InnoDB in its default isolation level (repeatable read) can produce deadlocks from operations that look like they should not conflict. If two transactions are trying to insert into the same gap, their gap locks can conflict and cause deadlock. This surprises developers who have heard that MVCC prevents most locking conflicts and do not understand that gap locks are an exception.
PostgreSQL's SSI uses a different approach called predicate locking. A predicate lock covers any row that satisfies a given predicate, including rows that do not exist yet. Predicate locks do not block other transactions from inserting (they are tracking dependencies, not preventing access), but when SSI detects a problematic dependency pattern involving predicate locks, it triggers a serialization failure.
The two generals problem in distributed databases
Everything described so far applies to a single-node database. When the database is distributed across multiple nodes, as in a distributed SQL database like CockroachDB or YugabyteDB, or in a system like Google Spanner, all the same problems apply with an additional constraint: the nodes cannot communicate instantaneously and reliably.
This creates a version of a problem known as the two generals problem, one of the classic impossibility results in distributed computing. Two armies are trying to coordinate an attack. They can only communicate by messenger. Each army needs to confirm that the other received their message before attacking. But each confirmation message might be intercepted. No matter how many messages are exchanged, neither general can ever be certain the other received the last message.
For databases, the analogue is distributed commit. A transaction might need to modify data on multiple nodes. For atomicity, either all nodes commit the change or all nodes roll back. But how do you coordinate this when any node or any network link might fail at any point?
The standard solution is two-phase commit, 2PC (not to be confused with two-phase locking). In 2PC, a coordinator sends a "prepare" message to all participants. Each participant acknowledges whether it is ready to commit. If all participants acknowledge ready, the coordinator sends a "commit" message and the transaction commits. If any participant votes no or fails to respond, the coordinator sends an "abort" message.
The problem is that 2PC is blocking. If the coordinator crashes after the prepare phase but before the commit, the participants are stuck. They have voted to commit but cannot commit without the coordinator's instruction. They cannot safely abort (the coordinator might have already told some other participants to commit). They wait, holding locks, until the coordinator recovers.
This blocking behavior is why 2PC is considered unsuitable for high-availability distributed systems. Modern distributed databases use consensus protocols instead, primarily Raft or Paxos. Raft replicates the write-ahead log across a cluster of nodes using leader election and majority voting. As long as a majority of nodes are alive, the system can commit transactions. If the leader fails, a new leader is elected automatically, and the system continues without blocking.
Spanner, Google's distributed database, extends this with a global-time synchronization mechanism using GPS receivers and atomic clocks in each data center. The commit timestamp of each transaction is a real wall-clock time, and Spanner uses bounded clock uncertainty to implement serializable isolation across geographically distributed nodes without any centralized coordination. The key insight is that if you bound the maximum clock skew across all nodes, you can assign timestamps to transactions such that causally related transactions always get ordered timestamps. No transaction commits until its timestamp is guaranteed to be in the past according to every clock in the system, with the uncertainty bound added as a wait interval.
What ACID costs and why some systems skip it
Maintaining full ACID guarantees is expensive. The write-ahead log imposes write amplification: every piece of data you write to the database also has to be written to the log, often in a different format and to a different physical location. Locking and MVCC add overhead to every transaction. Serializable isolation can reduce throughput significantly under high contention.
This cost was the main motivation for the NoSQL movement of the 2000s. Systems like Cassandra, DynamoDB, and MongoDB were designed around different trade-offs: sacrifice some or all ACID guarantees in exchange for higher write throughput, better scalability, or simpler horizontal sharding.
Cassandra does not have transactions in the traditional sense. It offers tunable consistency, where the developer chooses how many replicas must acknowledge a write before it is considered successful. At the extreme settings, Cassandra can provide linearizable reads and writes on individual keys, but it cannot guarantee atomicity across multiple keys the way a SQL transaction can. Applications using Cassandra have to design their data models and application logic to tolerate eventual consistency, where replicas may temporarily disagree about the state of the data.
This is not inherently wrong. Many applications do not actually need full ACID guarantees, and their developers were paying the ACID tax for correctness they did not need. A social media application where slightly stale data is acceptable, where losing an occasional counter increment is not a disaster, and where write throughput is the dominant concern is a reasonable fit for a system that trades ACID for performance.
The problem was that many developers adopted NoSQL systems without fully understanding what they were giving up. They assumed that "eventually consistent" meant "probably consistent enough" and built financial or medical applications where data correctness was critical on systems that could not guarantee it. The decade of debugging that followed educated the industry on the importance of understanding exactly what guarantees a data store actually provides, rather than assuming it provides the ones you need.
Why understanding this matters for application developers
A developer who understands the write-ahead log knows why a database that has just accepted a commit is safe to crash immediately afterward. The log is on disk. Recovery will redo the committed work.
A developer who understands MVCC knows why long-running read transactions in PostgreSQL can prevent autovacuum from cleaning up dead tuples, eventually causing table bloat and slowdowns. The dead tuples cannot be removed while there is an active transaction whose snapshot predates them.
A developer who understands isolation levels knows why switching from read committed to serializable in PostgreSQL might cause transaction retries in a high-concurrency application, and knows to design for retry logic rather than assuming transactions always succeed.
A developer who understands locking knows why a query that is "just reading" can still cause lock contention if it is reading inside a transaction that holds locks from earlier operations. They know to keep transactions short, acquire locks in consistent order, and understand what operations acquire what kinds of locks.
A developer who understands 2PC knows why distributed transactions spanning multiple services are expensive and fragile, and why the patterns that avoid them, like sagas and event sourcing, have become architectural staples in distributed systems design.
The machinery behind ACID is not an implementation detail that you can safely abstract away. It surfaces constantly in the behavior of production systems. It is in the performance characteristics of your write-heavy tables. It is in the deadlock errors that appear under load. It is in the mysterious data inconsistencies that only appear in concurrent workloads. It is in the recovery time after an unexpected crash.
The database is not a black box that reliably stores and retrieves your data. It is a system that makes specific, well-defined guarantees under specific, well-defined conditions, and understanding those conditions is the difference between building systems that behave correctly under pressure and building systems that surprise you at the worst possible moments.
Top comments (0)