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:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
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.