ACID Properties

ACID is a set of four guarantees — Atomicity, Consistency, Isolation, Durability — that database transactions must satisfy to ensure data integrity even in the face of errors, crashes, and concurrent access.

What It Really Means

Imagine transferring $500 from your checking account to your savings account. This operation involves two writes: debiting one account and crediting another. What happens if the system crashes between those two writes? Without ACID guarantees, you could lose $500 — the money is debited but never credited. ACID properties exist to prevent exactly this class of problem.

The four properties work together as a contract between the database and the application developer. Atomicity guarantees that both writes happen or neither does. Consistency ensures the total balance across accounts remains unchanged. Isolation means another user querying your accounts mid-transfer will not see an inconsistent intermediate state. Durability guarantees that once the transfer is confirmed, it survives even a power failure.

ACID was first formalized by Andreas Reuter and Theo Härder in 1983, though the concepts existed in database systems before that. Today, ACID compliance is the defining characteristic that separates traditional relational databases (PostgreSQL, MySQL InnoDB, Oracle) from many NoSQL systems that deliberately relax these guarantees for performance and scalability. Understanding exactly what each property guarantees — and what it costs — is essential for making informed architectural decisions.

How It Works in Practice

Atomicity

Atomicity means a transaction is an indivisible unit of work. Either all operations within the transaction complete successfully, or none of them take effect. There is no partial completion.

How databases implement it: Most databases use a write-ahead log (WAL). Before modifying any data pages, the database writes the intended changes to a sequential log file. If the system crashes mid-transaction, the recovery process reads the WAL and rolls back any incomplete transactions. PostgreSQL calls this the WAL; MySQL InnoDB calls it the redo log.

Real example: In a banking system, a wire transfer transaction debits the sender, credits the receiver, and inserts an audit log entry. If the audit log insert fails due to a constraint violation, the entire transaction rolls back — including the debit and credit.

Consistency

Consistency in ACID means the database moves from one valid state to another valid state. All constraints — primary keys, foreign keys, CHECK constraints, triggers — must be satisfied before and after every transaction.

Important distinction: ACID consistency is different from CAP theorem consistency. CAP consistency means every read sees the latest write (linearizability). ACID consistency means database invariants are preserved.

Real example: If you have a constraint that account balances must be non-negative, a transaction that would result in a negative balance is rejected. The database remains in a consistent state.

Isolation

Isolation determines how concurrent transactions interact. In a perfect world, every transaction would execute as if it were the only one running. In practice, strict serialization is expensive, so databases offer multiple isolation levels that trade correctness for performance.

The SQL standard defines four isolation levels:

PostgreSQL defaults to Read Committed. MySQL InnoDB defaults to Repeatable Read. In practice, PostgreSQL's Repeatable Read uses snapshot isolation (MVCC), which prevents phantom reads as well, making it stronger than the SQL standard requires.

Durability

Durability means that once a transaction is committed, its changes survive permanently — even if the system crashes, loses power, or the disk fails. Databases achieve this by flushing the write-ahead log to disk before confirming the commit.

Real example: PostgreSQL's synchronous_commit setting controls durability. With synchronous_commit = on (default), the server waits for WAL records to be flushed to disk before confirming. With synchronous_commit = off, the server confirms before flushing, which improves performance but risks losing the last few hundred milliseconds of commits after a crash.

Implementation

Trade-offs

When ACID Is Essential

• Financial systems — incorrect balances are unacceptable
• Inventory management — overselling due to race conditions
• Healthcare records — partial updates could endanger patients
• Any system where data correctness is more important than performance

When You Might Relax ACID

• High-throughput analytics pipelines where eventual consistency is acceptable
• Social media feeds where a slightly stale view is fine
• Caching layers where temporary inconsistency is tolerable
• Systems requiring horizontal scalability across regions (see BASE properties)

Performance Cost of ACID

• Atomicity: WAL writes add disk I/O overhead
• Consistency: Constraint checking adds CPU overhead per transaction
• Isolation: Locks or MVCC snapshots consume memory; stricter isolation means more contention and aborted transactions
• Durability: Synchronous disk flushes are the single biggest performance bottleneck; fsync calls can limit throughput to a few hundred transactions per second on spinning disks

Common Misconceptions

• "All databases are ACID-compliant" — Many NoSQL databases intentionally sacrifice one or more ACID properties. MongoDB only gained multi-document ACID transactions in version 4.0 (2018). Cassandra does not support ACID transactions at all. Redis transactions are atomic but not isolated in the traditional sense.

• "ACID means slow" — Modern databases achieve impressive throughput with ACID guarantees. PostgreSQL can handle tens of thousands of ACID transactions per second on commodity hardware. The key is choosing the right isolation level — serializable is expensive, but read committed is cheap.

• "ACID consistency and CAP consistency are the same thing" — They are completely different concepts. ACID consistency means database invariants (constraints) hold. CAP consistency means every read returns the most recent write (linearizability). A system can be ACID-consistent but not CAP-consistent, and vice versa.

• "Transactions automatically prevent all concurrency bugs" — Isolation levels below serializable still allow anomalies. Read Committed allows non-repeatable reads. Repeatable Read (in some databases) allows phantom reads. You must choose the isolation level that matches your correctness requirements.

• "NoSQL means no transactions" — Some NoSQL databases support transactions. Google Cloud Spanner provides globally distributed ACID transactions. CockroachDB is a distributed SQL database with serializable isolation. FaunaDB supports ACID transactions with a Calvin-style protocol.

How This Appears in Interviews

ACID properties are fundamental in database and system design interviews:

• "Explain ACID properties with an example" — use the bank transfer scenario. Walk through what happens during a crash at each step. Show you understand the WAL mechanism. See our interview questions on databases.
• "What isolation level would you choose for an e-commerce checkout?" — discuss the trade-off between serializable (prevents overselling but limits throughput) and read committed (higher throughput but needs application-level locking for inventory). See our system design interview guide.
• "How does MVCC work?" — explain that each transaction sees a snapshot of the database at the time it started. Writers do not block readers. Conflicts are detected at commit time.
• "Compare ACID and BASE" — show you understand the spectrum between strong consistency and eventual consistency, and when each is appropriate. See BASE properties.

Related Concepts

• BASE Properties — The alternative to ACID for distributed systems
• CAP Theorem — How consistency and availability trade off during network partitions
• Database Indexing — How indexes interact with transaction performance
• Database Partitioning — Partitioning complicates ACID guarantees across shards
• Normalization vs Denormalization — Schema design impacts transaction complexity
• System Design Interview Guide — Apply ACID reasoning in design interviews
• Algoroq Pricing — Practice database interview questions