ACID Transactions

The four guarantees a “real” database gives you so you don’t lose your money. ACID is the reason you can trust your bank’s software to move $100fromoneaccounttoanotherwithoutlosingthe$100 or duplicating it.

The canonical example

Transfer $100 from Alice to Bob:

1. Subtract $100 from Alice's balance
2. Add $100 to Bob's balance

Between step 1 and step 2, the system could:

• Crash → Alice loses $100, Bob gets nothing
• Be interrupted by another transaction reading balances → someone sees a moment where the $100 doesn’t exist anywhere

Neither is acceptable. A transaction wraps both operations so that either both happen or neither does, and no one can see the in-between state.

The four properties that make this work are ACID.

A — Atomicity

Either all of the operations in a transaction succeed, or none of them do. There’s no “partially applied” state.

If the system crashes between step 1 and step 2, the database rolls back step 1 on recovery. Alice’s balance is unchanged.

Implementation: write-ahead logs (WAL) or undo logs. Every change is first recorded in the log; only after the full transaction’s log entries are durable does the change commit.

C — Consistency

A transaction moves the database from one valid state to another valid state. Constraints (foreign keys, unique indexes, check constraints) are never violated.

If there’s a rule that balances can’t go negative, a transaction that would break this rule is rejected.

Note: this is the most abused word in the ACID vocabulary. “Consistency” here means integrity constraints are preserved — not the same as “every read sees the same value” (that’s a separate property, typically called linearizability or strong consistency).

I — Isolation

Concurrent transactions behave as if they ran one at a time. One transaction’s mid-flight changes are invisible to others.

If 10,000 transfers happen simultaneously, none of them sees another’s partial state. The classic anomalies isolation must prevent:

Anomaly	What happens
Dirty read	T1 reads data that T2 wrote but hasn’t committed; T2 rolls back; T1 acted on data that never existed
Non-repeatable read	T1 reads a row twice; between reads, T2 updates it; T1 sees different values
Phantom read	T1 queries a range; between queries, T2 inserts a new row matching the range; T1’s second query gets a different set
Lost update	T1 and T2 both read a counter, both increment, both write; one update is lost

Isolation levels (SQL standard)

Databases offer a ladder — stronger isolation costs more locking / more aborts:

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’s default is Read Committed. Many Postgres deployments use Serializable or Repeatable Read for critical operations via BEGIN ISOLATION LEVEL ....

D — Durability

Once a transaction commits, it survives crashes, power loss, OS failure. “Committed” means “on disk, or at least in a location guaranteed to reach disk.”

Implementation:

• Write-ahead log flushed to disk (fsync) before the commit is acknowledged
• On restart, the database replays the log to reconstruct committed-but-not-yet-written changes

This is why disks with lying caches (consumer SSDs that acknowledge writes before they’re actually persisted) can corrupt databases. Enterprise storage uses battery-backed cache or power-loss-protected SSDs specifically to honor the durability guarantee.

The cost of ACID

ACID has a price, paid in two currencies:

1. Performance — logs, locks, coordination all cost CPU and I/O
2. Distributed-system difficulty — enforcing ACID across multiple machines is hard (distributed transactions, two-phase commit, consensus protocols like Raft/Paxos)

This is why many NoSQL systems chose to weaken ACID in exchange for horizontal scalability. The trade-off is usually called BASE.

BASE — the NoSQL counterpoint

BASE is a loose counter-acronym:

• Basically Available
• Soft state
• Eventually consistent

The promise: “accept all writes, figure out consistency later.” A write to one replica might not be visible on another for milliseconds or seconds. Conflicts are resolved later (last-write-wins, version vectors, CRDTs).

Good for: social feeds, product catalogs, caches, shopping carts (mostly), analytics. Bad for: money, inventory counts, anything where consistency matters more than availability.

Modern reality: it’s a spectrum

Few modern systems are purely ACID or purely BASE:

• PostgreSQL is strongly ACID but adds async replication for read scaling (replicas can lag).
• MongoDB added multi-document ACID transactions in 4.0.
• DynamoDB added transactions with strong consistency in 2018.
• Google Spanner / CockroachDB / YugabyteDB offer full ACID across geo-distributed nodes using TrueTime / HLC + Paxos/Raft.

“ACID vs eventual consistency” is no longer a binary choice — you configure it per operation.

See also

• Relational vs NoSQL Databases
• Database Indexing
• Backup Fundamentals — RPO and RTO — durability meets disaster recovery