ACID: Four letters that keep your data safe

ACID describes four properties of database transactions. It comes up in interviews and architecture decisions because these properties determine what happens when a transaction fails or runs alongside another.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties protect transactions against failures and concurrent access, including crashes, power failures, and two users changing the same row.

Table of Contents

• 1. Atomicity — all or nothing
• 2. Consistency — only valid states
• 3. Isolation — transactions don't interfere
• 4. Durability — committed means committed
• 5. Putting it all together: a bank transfer
• 6. When ACID breaks down
• 7. Bottom line

1. Atomicity — all or nothing

A transaction is atomic if it either completes entirely or doesn't happen at all. No partial states. No "halfway done."

If you're transferring money from account A to account B and the database crashes after debiting A but before crediting B, atomicity guarantees the debit gets rolled back. You never end up with money that just disappeared.

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- 💥 Database crashes here
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
 
COMMIT;

Without atomicity, account A is $100 short and B never got paid. With atomicity, the BEGIN...COMMIT block either fully succeeds or fully rolls back. The crash means the whole transaction is aborted, and A's balance is restored.

The rule: a transaction is a single unit of work. It either all happens or none of it happens.

2. Consistency — only valid states

Consistency means a transaction takes the database from one valid state to another. All constraints, including primary keys, foreign keys, check constraints, and NOT NULL, must hold after the transaction commits.

-- Check constraint: balance can never go below zero
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
 
-- This transaction will be rejected
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A' AND balance = 200;
-- Fails: resulting balance would be -300, violates the constraint
COMMIT;

The database enforces consistency through constraints. Your constraints define what "valid" means, and the database won't let a transaction commit if it would leave things in an invalid state.

The rule: after a transaction, every rule and constraint in the database must still hold.

3. Isolation — transactions don't interfere

Isolation means concurrent transactions behave as if they ran one after the other, even if they actually ran at the same time. Each transaction sees a consistent snapshot and can't see the intermediate state of other transactions.

-- Transaction 1: Reading account balance
BEGIN;
SELECT balance FROM accounts WHERE id = 'A';  -- Returns 500
-- Transaction 2 updates A's balance to 400 here
SELECT balance FROM accounts WHERE id = 'A';  -- Still returns 500 (not 400)
COMMIT;

Without isolation, Transaction 1 would see a different balance on the second read, a "phantom read" caused by Transaction 2's uncommitted change. Isolation prevents that.

Databases offer isolation levels that let you trade strictness for performance:

Most production databases default to Read Committed. If you need stronger guarantees (bank transfers, inventory management), you bump it up.

The rule: concurrent transactions must not interfere with each other. Each sees a snapshot as if it ran alone.

4. Durability — committed means committed

Durability guarantees that committed transactions survive crashes and power failures, though destruction of the data center is a different matter. The data is written to non-volatile storage before the commit returns success.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- Once you see "COMMIT", it's permanent
-- 💥 Even if the server explodes right now,
-- the data is safe on disk (or replicated)

The database uses a write-ahead log (WAL). Before any change hits the data files, it's recorded in the WAL. If the database crashes, it replays the WAL on recovery to restore all committed transactions and roll back any that didn't finish.

The rule: once a commit succeeds, the data remains permanent through system failures.

5. Putting it all together: a bank transfer

A bank transfer shows how the four properties work together.

You're transferring $500 from Alice (account A, balance $1,200) to Bob (account B, balance $300).

BEGIN;
 
-- Step 1: Debit Alice's account
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
 
-- Step 2: Credit Bob's account
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
 
-- Step 3: Log the transfer
INSERT INTO transfer_log (from_account, to_account, amount, created_at)
VALUES ('A', 'B', 500, NOW());
 
COMMIT;

Here's how each ACID property keeps this safe:

Atomicity: If the database crashes between Step 1 and Step 2, the entire transaction rolls back. Alice keeps her $1,200. Without atomicity, Alice loses $500 and Bob gets nothing.

Consistency: The CHECK (balance >= 0) constraint ensures Alice can't go into negative territory. If Alice only had $300 and tried to transfer $500, the database rejects the entire transaction. The invariant "total money in the system stays the same" is preserved: $1,200 + $300 = $1,500 before, $700 + $800 = $1,500 after.

Isolation: While this transfer is happening, another transaction trying to read Alice's balance won't see the intermediate state where $500 has been debited but not yet credited to Bob. They see either the before-state ($1,200) or the after-state ($700), without an intermediate state.

Durability: Once the COMMIT returns, the transfer is permanent. Even if the database server loses power one millisecond later, Alice's new balance ($700) and Bob's new balance ($800) survive. The WAL ensures the transfer record is replayed on recovery.

All four properties work together. Remove any one, and the transfer breaks:

• No atomicity → partial debit with no credit
• No consistency → negative balances possible
• No isolation → other transactions see half-complete transfers
• No durability → committed transfers lost on crash

That's why ACID is a package deal. Any database that claims to be "ACID-compliant" guarantees all four. PostgreSQL, MySQL with InnoDB, Oracle, and SQL Server all support these properties.

6. When ACID breaks down

ACID isn't free. Serializable isolation kills throughput under high concurrency. Two-phase commit across distributed databases is slow and fragile. That's why NoSQL databases (Cassandra, DynamoDB, MongoDB) often sacrifice some ACID properties for performance and availability.

The CAP theorem makes this explicit: in a distributed system, you can have at most two of Consistency, Availability, and Partition tolerance. Most NoSQL databases chose Availability + Partition tolerance, sacrificing strong consistency.

For financial data, inventory, and other workloads where correctness matters more than throughput, ACID is non-negotiable. You don't want "eventually consistent" bank balances.

If you need ACID guarantees across distributed services, look into sagas (compensating transactions) or use a database that supports distributed transactions (PostgreSQL with PREPARE TRANSACTION, or something like CockroachDB). These tools come with trade-offs.

7. Bottom line

ACID protects database operations against partial updates, invalid states, concurrent interference, and the loss of committed data.

Atomicity means all or nothing. Consistency means only valid states. Isolation prevents interference between transactions. Durability makes committed changes permanent.

If you're working with relational databases, you're already using ACID every day. Understanding why it works means you can make better decisions about when to enforce it, when to relax it, and when something else entirely (like eventual consistency) is the right call.