A relational database’s ACID transaction properties are less about guaranteeing data correctness and more about managing concurrent access to data without breaking things.
Let’s see this in action with a simple bank transfer. Imagine two transactions happening at the exact same millisecond:
Transaction A: Transfer $100 from Account 1 to Account 2. Transaction B: Check the balance of Account 1.
If these weren’t ACID compliant, Transaction B might see Account 1 after $100 is debited but before it’s credited to Account 2. This "dirty read" would show Account 1 with a balance that doesn’t reflect the completed state of the transfer, even though the transfer might eventually succeed. ACID prevents this by ensuring the entire transfer operation (debiting Account 1 AND crediting Account 2) is treated as a single, indivisible unit.
Here’s how the properties work together:
Atomicity: This is the "all or nothing" principle. A transaction is a single unit of work. If any part of it fails, the entire transaction is rolled back, and the database returns to its state before the transaction began. In our bank transfer, if the system crashes after debiting Account 1 but before crediting Account 2, Atomicity ensures the debit is undone, and Account 1’s balance remains unchanged.
Consistency: This property ensures that a transaction brings the database from one valid state to another. It doesn’t mean the data itself is "correct" in a business sense, but rather that the database’s internal rules (like referential integrity, unique constraints, or even custom triggers) are maintained. If a transaction violates these rules, it’s rolled back. For example, if Account 2 doesn’t exist, a transaction trying to credit it would fail the Consistency check, and the debit from Account 1 would be rolled back.
Isolation: This is where things get complex, as it dictates how concurrent transactions interact. Isolation ensures that each transaction executes as if it were the only transaction running on the system. Different isolation levels (like Read Uncommitted, Read Committed, Repeatable Read, and Serializable) offer varying degrees of protection against concurrency phenomena like dirty reads, non-repeatable reads, and phantom reads.
Let’s take our transfer again. If Transaction B (checking Account 1 balance) runs with a strict isolation level like Serializable, it will effectively "lock" Account 1 and Account 2 for the duration of Transaction A. Transaction B will either see Account 1’s balance before the transfer or after the transfer is fully committed, but never an intermediate state.
Durability: Once a transaction is committed, its changes are permanent. Even in the event of a system failure (power outage, crash), the committed data will survive. This is typically achieved through write-ahead logging (WAL). Before any data modification is written to disk, the changes are logged to a durable transaction log. If the system crashes, the database can replay these logs upon restart to restore all committed transactions.
Consider the levers you control:
- Transaction Boundaries: Explicitly defining
BEGIN TRANSACTION,COMMIT, andROLLBACKin your SQL is the most direct way to manage transactions. - Isolation Levels: Choosing the right isolation level is a performance vs. correctness trade-off.
READ COMMITTEDis often the default and a good balance for many applications.SERIALIZABLEoffers the strongest guarantees but can significantly reduce concurrency. - Database Configuration: Parameters like
wal_level,fsync, andsynchronous_commitin PostgreSQL, or similar settings in other databases, directly influence durability guarantees.
The mechanism most people don’t fully grasp is how isolation levels prevent specific concurrency anomalies. For instance, READ COMMITTED prevents dirty reads by ensuring a transaction only sees data that has been committed. However, it doesn’t prevent non-repeatable reads: if you read a row twice within the same transaction, and another committed transaction modifies that row in between your reads, you’ll see different values. REPEATABLE READ addresses this by locking rows read within a transaction, but it still might not prevent phantom reads.
Understanding the nuances of how your database implements these isolation levels is key to writing correct and performant concurrent applications.
The next logical step is exploring how different database systems implement these ACID properties and the specific performance implications of each.