ACID, Isolation Levels, and the Anomalies They Prevent

In 1983, Andreas Reuter and Theo Härder coined the acronym ACID^[1] to describe the properties of a reliable transaction processing system. These four letters, Atomicity, Consistency, Isolation, and Durability, form the bedrock of data integrity guarantees in most relational databases. For decades, they have been the standard by which we measure the reliability of our data stores. While the concept seems simple, the reality, especially concerning the 'I' for Isolation, is a landscape of nuanced tradeoffs between correctness and performance that every software engineer and technical leader must understand to build reliable systems.

ACID isn't just a feature; it's a contract. It promises that your data will remain logical and intact, even when faced with concurrent operations, network partitions, or sudden server crashes. This contract is what allows a banking application to transfer funds between two accounts without creating or losing money, or an e-commerce site to sell its last item to exactly one customer. Understanding the mechanisms that enforce this contract is essential for making informed architectural decisions.

What is ACID? A Foundational Guarantee

ACID defines the four essential properties of a reliable database transaction. A transaction is a sequence of operations performed as a single logical unit of work. All operations in the unit must succeed, or all of them must be undone.

Atomicity

Atomicity guarantees that a transaction is an all-or-nothing proposition. It either executes completely, or it has no effect at all. There is no intermediate state. If a transaction to transfer $100 from a savings to a checking account successfully debits the savings account but fails before crediting the checking account, atomicity ensures the debit is rolled back. The database state is as if the transaction never began.

This is typically implemented using a write-ahead log (WAL)^[2]. Before a change is made to a data file on disk, the database first writes a description of that change to a separate, append-only log file on persistent storage. If the system crashes mid-transaction, the database can consult this log upon recovery. It can complete any committed transactions that weren't fully written to the main data files and roll back any transactions that were in-progress but not yet committed.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another. This property is a shared responsibility. The database enforces consistency through constraints you define: NOT NULL, UNIQUE, foreign keys, and CHECK constraints. For example, the database can prevent a transaction from creating an order for a nonexistent customer if a foreign key constraint is in place.

However, the application layer holds the primary responsibility for business logic consistency. If your application's logic dictates that a user's account_balance must never be negative, it's the application's job to check the balance before attempting a withdrawal. The database ensures the transaction that performs this check and the subsequent update is atomic and isolated, but it doesn't understand the business rule itself. Consistency, therefore, refers to the overall correctness of the data, including all application-specific rules.

Isolation

Isolation is arguably the most complex property of ACID. It ensures that concurrently executing transactions cannot interfere with each other. From the perspective of any single transaction, it appears as though it is the only transaction running on the system. If two people try to book the last seat on a flight simultaneously, isolation mechanisms prevent them from both succeeding. One transaction will complete successfully, and the other will fail or be forced to wait.

Perfect isolation is computationally expensive. Because of this, the SQL standard^[3] defines several isolation levels, which are different degrees of isolation with varying performance characteristics. Each level makes a specific tradeoff, allowing certain types of concurrency anomalies in exchange for better performance. We will explore these levels and anomalies in detail.

Durability

Durability guarantees that once a transaction has been committed, it will remain committed, even in the event of a power loss, crash, or other system failure. Like atomicity, this is typically achieved with the write-ahead log. When a client receives a confirmation that its transaction has been committed, it means the corresponding log records have been successfully flushed to non-volatile storage like an SSD or hard drive. Even if the server crashes immediately after, the changes can be reapplied from the log during the recovery process, ensuring no committed data is lost.

The 'I' in ACID: Isolation Levels and Their Anomalies

While perfect isolation (known as serializability) is the strongest guarantee, it can create performance bottlenecks by reducing the number of transactions that can run in parallel. To address this, database systems offer weaker isolation levels. These levels are defined not by their implementation, but by which concurrency phenomena, or anomalies, they permit.

A foundational 1995 paper, "A Critique of ANSI SQL Isolation Levels,"^[4] by Berenson et al., clarified the original SQL-92 standard by precisely defining these phenomena.

The Concurrency Anomalies

• P1: Dirty Read: A transaction reads data that has been written by another transaction that has not yet committed. If the other transaction subsequently rolls back, the first transaction has read "dirty" or garbage data that never officially existed.
• P2: Non-Repeatable Read (or Fuzzy Read): A transaction reads a row of data twice and gets a different value each time, because another transaction modified and committed the data between the two reads. This can invalidate decisions made based on the first read.
• P3: Phantom Read: A transaction executes a query twice and the set of rows returned is different each time. This happens when another transaction inserts or deletes rows that match the WHERE clause of the query. The new, unexpected rows are called "phantoms."

The Standard Isolation Levels

Databases prevent these anomalies using concurrency control mechanisms. The two most common are locking and multi-version concurrency control (MVCC)^[5]. In locking, transactions take locks on data to prevent others from modifying it. In MVCC, the database maintains multiple versions of a row, allowing readers to see a consistent snapshot of data without blocking writers.

1. Read Uncommitted

• Prevents: Nothing.
• Allows: Dirty Reads, Non-Repeatable Reads, Phantom Reads.
• Mechanism: Readers do not acquire any locks. This provides maximum concurrency but at the cost of reading uncommitted, potentially invalid data. It's rarely used in practice due to the significant risks to data integrity.

2. Read Committed

• Prevents: Dirty Reads.
• Allows: Non-Repeatable Reads, Phantom Reads.
• Mechanism: This is the default isolation level for many databases, including PostgreSQL^[6], SQL Server^[7], and Oracle^[8]. In a locking-based system, it works by having write transactions acquire exclusive locks on data they modify. Read transactions acquire a brief shared lock while reading, preventing them from reading a row that is actively being changed. In an MVCC system, a query sees a snapshot of the database as it existed at the moment the query began. It will not see uncommitted data.

3. Repeatable Read

• Prevents: Dirty Reads, Non-Repeatable Reads.
• Allows: Phantom Reads.
• Mechanism: In locking systems, read transactions acquire shared locks on all data they read and hold them until the transaction ends. This ensures that once a row is read, it cannot be changed by another transaction until the first one completes. In MVCC systems like PostgreSQL and MySQL's InnoDB engine^[9], the snapshot is taken at the first non-transaction-control statement in PostgreSQL, and in MySQL InnoDB, it is established by the first read, not when the transaction begins. All subsequent reads within that transaction see the same consistent snapshot.

4. Serializable

• Prevents: Dirty Reads, Non-Repeatable Reads, Phantom Reads.
• Allows: Nothing.
• Mechanism: This is the strongest isolation level. It guarantees that the result of running transactions concurrently is the same as if they had been run one after another in some sequential order. Historically, this was implemented with Strict Two-Phase Locking (S2PL), where transactions acquire locks as needed and release them all only at the end. To prevent phantoms, databases often use predicate locks or index-range locks, which can be slow.

A more modern and often more performant implementation is Serializable Snapshot Isolation (SSI), pioneered by PostgreSQL since version 9.1^[10]. SSI allows transactions to run optimistically on a snapshot. The database tracks the read and write dependencies between concurrent transactions. If it detects a situation where serializability could be violated (a dangerous read-write dependency), it will force one of the transactions to abort and retry.

A Worked Example: The Double-Booking Problem

Let's illustrate why READ COMMITTED can be insufficient. Imagine a system for booking a single, exclusive meeting room. We have a table rooms with a booked_by_user_id column, which is NULL if the room is available.

CREATE TABLE rooms (
 id INT PRIMARY KEY,
 name TEXT NOT NULL,
 booked_by_user_id INT NULL
);

INSERT INTO rooms (id, name, booked_by_user_id) VALUES (101, 'The Boardroom', NULL);

Two users, Alice (user ID 50) and Bob (user ID 51), try to book the room at the same time. Both of their applications will run a transaction that looks like this:

1. Check if the room is available.
2. If it is, book it by updating the booked_by_user_id.

Here's how things can go wrong under the READ COMMITTED isolation level:

-- Terminal 1: Alice's Transaction (user_id = 50)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 1. Alice checks if the room is free.
-- The query returns NULL, so the room is available.
SELECT booked_by_user_id FROM rooms WHERE id = 101;
-- (returns NULL)

-- 3. Before Alice can update, Bob's transaction runs to completion.

-- 4. Alice's application logic proceeds, unaware of Bob's action.
-- She updates the row, overwriting Bob's booking.
UPDATE rooms SET booked_by_user_id = 50 WHERE id = 101;

COMMIT;

-- Terminal 2: Bob's Transaction (user_id = 51)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 2. Concurrently, Bob also checks if the room is free.
-- Because Alice hasn't committed anything, Bob also sees the room is available.
SELECT booked_by_user_id FROM rooms WHERE id = 101;
-- (returns NULL)

-- Bob's application logic proceeds and he books the room.
UPDATE rooms SET booked_by_user_id = 51 WHERE id = 101;

COMMIT; -- Bob commits first.

The result is a classic lost update anomaly. Bob's successful booking is silently overwritten by Alice's. The system now believes Alice booked the room, Bob thinks he booked the room, and the business has failed. READ COMMITTED did not prevent this because it makes no promises about data staying the same between two separate queries within a transaction.

If both transactions had used SERIALIZABLE, the database would have detected the hazardous dependency. In PostgreSQL's SSI, the second transaction to commit (Alice's) would have failed with a serialization error, forcing her application to retry. The retry would correctly see that the room is already booked.

The Decision Lens: Choosing an Isolation Level

Choosing an isolation level is a direct tradeoff between data correctness and system performance. Higher levels provide stronger guarantees but can reduce throughput by increasing lock contention or causing more transaction aborts.

• Start with the Default: READ COMMITTED is the default in most systems for good reason. It prevents reading uncommitted data and offers excellent performance. Many application workflows do not require stronger guarantees. Simple, atomic writes or reads that don't depend on a prior read within the same transaction are perfectly safe.

• When to Increase Isolation: You should consider REPEATABLE READ or SERIALIZABLE when a transaction's logic involves a read-modify-write cycle. If your code reads some data, makes a decision based on it, and then writes back to the database, you are vulnerable to non-repeatable reads or phantom reads. The double-booking example is a perfect case for this. Complex reports that require a stable, consistent view of the data across many tables also benefit from higher isolation.

• Consider Surgical Alternatives: Instead of setting a higher isolation level for an entire transaction, you can often solve concurrency issues more surgically and with better performance:

• Pessimistic Locking (SELECT ... FOR UPDATE): This SQL statement allows you to explicitly lock the rows returned by a SELECT query. In our booking example, if Alice had run SELECT ... FROM rooms WHERE id = 101 FOR UPDATE, the database would have placed a write lock on that row. Bob's subsequent SELECT ... FOR UPDATE would have been blocked until Alice's transaction committed or rolled back. This effectively serializes access to that specific row while allowing high concurrency elsewhere in the system.

• Optimistic Locking: This is an application-level technique. You add a version or last_updated_at column to your table. When you read a row, you also fetch its version. When you write it back, your UPDATE statement includes a condition: UPDATE rooms SET ..., version = version + 1 WHERE id = ? AND version = ?. If another transaction has modified the row in the meantime, its version number will have changed, and your UPDATE statement will affect zero rows. Your application can detect this and handle the conflict, usually by retrying the entire transaction.

Common Misconceptions and Modern Realities

**Misconception: "My ORM handles transactions, so I don't need to worry about this."
**Object-Relational Mappers (ORMs) provide helpful abstractions for transaction boundaries (@Transactional annotations, for example), but they do not eliminate the need to understand concurrency. The developer is still responsible for choosing the correct isolation level and designing workflows that are safe for that level. An ORM can make it easy to cause a lost update if you're not careful.

**Misconception: "ACID is slow and legacy; NoSQL is fast and modern."
**This is a false dichotomy. The performance of a database is determined by its architecture, data model, and consistency guarantees, not a simple label. Many modern NoSQL databases now offer ACID transactions^[11], recognizing their importance for application developers. At the same time, distributed SQL databases like Google's Spanner^[12] and CockroachDB^[13] have been built from the ground up to provide serializable isolation across continents, challenging the notion that strong consistency cannot scale.

**Misconception: "Serializable is the only 'true' isolation."
**While SERIALIZABLE provides the strongest formal guarantee, another popular and very strong level is SNAPSHOT isolation. Implemented by Oracle (as its SERIALIZABLE level) and Microsoft SQL Server (as a distinct SNAPSHOT level since version 2005), it's not one of the standard ANSI levels. In Snapshot Isolation, every transaction reads from a consistent snapshot of the database. It prevents the common anomalies (P1, P2, P3) but can still allow a subtle anomaly called write skew, which SERIALIZABLE prevents. Write skew can occur when two transactions read the same set of objects, and then each updates a different object within that set. A classic example is ensuring at least one doctor is on call. Two doctors could concurrently check the schedule, see that two doctors are on call, and then each decide to take leave. Both transactions succeed, leaving no doctors on call. PostgreSQL's SERIALIZABLE level (SSI) detects this pattern and would abort one of the transactions.

Today, the landscape of data consistency is richer than ever. The principles of ACID, born from the mainframe era, remain profoundly relevant. Modern database engineering has provided new, highly performant implementations like SSI that make the strongest guarantees more accessible than before. Understanding the precise contract offered by each isolation level, and what it costs, is not an academic exercise. It's a fundamental skill for building software that is correct, predictable, and reliable.