ACID Properties Explained: Atomicity, Consistency, Isolation, and Durability

When you transfer money from one bank account to another, a lot can go wrong. The network could drop mid-transfer. The server could crash. Two transfers could happen at the same time. Without the right guarantees, you could lose money, double-spend it, or end up with a corrupted balance.

ACID properties are the four guarantees that relational databases give you to prevent exactly these problems. They define what a transaction must do to keep data safe and correct, even when things go wrong.

This guide explains each ACID property in plain terms, with real examples, and shows you why they matter for every application that touches a database. If you are new to databases, you may want to start with Relational Databases Explained: Tables, Rows, and Keys first.

Table of Contents

What Is a Database Transaction?

A transaction is a group of database operations that are treated as a single unit. Either all operations in the group succeed, or none of them are applied.

The classic example: a bank transfer.

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- debit Alice
UPDATE accounts SET balance = balance + 500 WHERE id = 2; -- credit Bob

COMMIT;

Both UPDATE statements must succeed together. If the second one fails after the first has already run, Alice loses $500 and Bob gets nothing. That is a data integrity disaster.

ACID properties define the rules that prevent this.

What Does ACID Stand For?

flowchart TD
    T[Transaction] --> A[Atomicity\nAll or nothing]
    T --> C[Consistency\nValid state only]
    T --> I[Isolation\nNo interference]
    T --> D[Durability\nSurvives crashes]
    A --> R[Reliable Data]
    C --> R
    I --> R
    D --> R

Atomicity: All or Nothing

Atomicity guarantees that every operation inside a transaction is treated as one indivisible unit. If any step fails, the entire transaction is rolled back - as if none of the operations happened.

Bank Transfer Example

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Server crashes here ↑

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Without atomicity, Alice’s account is debited but Bob’s never gets the credit. With atomicity, the database rolls back the first UPDATE on recovery, and both accounts stay unchanged.

How It Works Internally

Databases use a write-ahead log (WAL). Every change is written to the log before it is applied to the actual data. On a crash, the database replays or rolls back based on what was committed in the log.

Key Point

Atomicity is binary: the transaction either fully commits or fully rolls back. There is no in-between state visible to the outside world.

Consistency: Valid State to Valid State

Consistency ensures that a transaction can only move the database from one valid state to another. It must not violate any integrity constraints, rules, or relationships defined in the schema.

What “Valid State” Means

Consider a NOT NULL constraint, a UNIQUE index, or a foreign key relationship. Consistency means these rules are always upheld - before the transaction, during it (internally), and after it commits.

-- accounts table has: CHECK (balance >= 0)
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Alice only has $200. This violates CHECK (balance >= 0).

COMMIT; -- Database rejects this and rolls back

The transaction is rejected because committing it would leave the database in an invalid state (negative balance).

Consistency Is Partly Your Responsibility

The database enforces schema-level constraints (NOT NULL, UNIQUE, CHECK, foreign keys). But application-level consistency - for example, “a user cannot have more than 3 active subscriptions” - is your responsibility to enforce in code or via triggers.

Isolation: Concurrent Transactions Do Not Interfere

Isolation controls what one transaction can see from another concurrent transaction. Without it, two transactions running at the same time can corrupt each other’s data.

The Problem Without Isolation

Imagine two users buy the last ticket to a concert simultaneously:

1. Transaction A reads: tickets_remaining = 1
2. Transaction B reads: tickets_remaining = 1
3. Transaction A sells the ticket: tickets_remaining = 0
4. Transaction B sells the ticket: tickets_remaining = -1

Both transactions saw the same value and both sold the ticket. This is called a lost update or race condition.

Isolation Levels

Isolation is not all-or-nothing. Databases offer different levels, each trading some protection for more performance:

Dirty Read: Reading uncommitted data from another transaction.
Non-Repeatable Read: Reading the same row twice gets different values because another transaction changed it.
Phantom Read: A query returns a different number of rows on two reads because another transaction inserted or deleted rows.

flowchart TD
    RU[Read Uncommitted\nLeast protection] --> RC[Read Committed]
    RC --> RR[Repeatable Read]
    RR --> SR[Serializable\nMost protection]
    SR --> NOTE[Every anomaly prevented\nbut slower concurrency]

Most production databases default to Read Committed or Repeatable Read. Use Serializable only when your business logic demands it, as it can significantly reduce concurrency.

How Isolation Is Implemented

Databases implement isolation using locks or MVCC (Multi-Version Concurrency Control). With MVCC, readers see a consistent snapshot of the data at the moment their transaction started, without blocking writers.

PostgreSQL and MySQL InnoDB both use MVCC. This is why reads are fast - they do not wait for write locks.

Durability: Committed Means Permanent

Durability guarantees that once a transaction is committed, it stays committed - even if the server crashes immediately afterward.

What Committed Means

BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 42;
COMMIT; -- At this point, the change is permanent

After COMMIT returns, the change is safe. A power failure, OS crash, or hardware failure will not undo it.

How Durability Works

Durability is achieved through the write-ahead log (WAL):

1. Before writing data to disk, the database writes the change to the WAL.
2. COMMIT flushes the WAL entry to durable storage (disk or SSD).
3. On recovery after a crash, the database replays committed WAL entries to restore state.

This is why databases are careful about fsync calls. Disabling fsync for speed (sometimes done in test environments) breaks durability - the “committed” data may not actually make it to disk.

Durability vs Backups

Durability protects against crashes. It does not protect against disk failure, accidental deletes, or corruption. That is what backups and replication are for.

How the Four Properties Work Together

ACID properties are interdependent. Remove one and the others become unreliable:

• Without atomicity, partial failures corrupt data. Consistency breaks immediately.
• Without consistency, valid schema constraints mean nothing. Data integrity is gone.
• Without isolation, concurrent transactions overwrite each other. Atomic commits can still collide.
• Without durability, committed data can vanish. Every guarantee becomes temporary.

flowchart TD
    A[Atomicity] -->|prevents partial failures| C[Consistency]
    C -->|maintains valid state| I[Isolation]
    I -->|prevents concurrent corruption| D[Durability]
    D -->|makes commits permanent| A

Together, the four properties mean: you can trust the database to do exactly what you asked, nothing more and nothing less, even when things go wrong.

ACID vs BASE: What Is the Difference?

Not all databases prioritize ACID. Many NoSQL databases (Cassandra, DynamoDB, CouchDB) use BASE instead:

BASE databases accept that data may be eventually consistent - all nodes will agree eventually, but a read right after a write may not see that write yet.

Neither is better. ACID is the right choice when correctness is non-negotiable (payments, inventory). BASE is the right choice when scale and availability matter more than immediate consistency (social media likes, view counts).

For more on the tradeoffs between relational and NoSQL databases, see SQL vs NoSQL: Differences, Use Cases, and Examples.

Which Databases Are ACID-Compliant?

Most relational databases are fully ACID-compliant by default:

One important note for MySQL users: the default storage engine InnoDB is ACID-compliant. The older MyISAM engine is not - it does not support transactions at all. Always use InnoDB for new MySQL tables.

Common Misconceptions

“ACID means my data is always consistent.”
The database enforces schema constraints, but application logic is your responsibility. ACID does not prevent you from inserting semantically wrong data - only structurally invalid data.

“Isolation means transactions are completely invisible to each other.”
Only Serializable isolation gives full invisibility. Lower levels (Read Committed, Repeatable Read) allow some cross-transaction visibility to improve performance.

“Durability means backups.”
Durability only protects against crashes. A committed DELETE is durable - meaning it is permanently gone. Backups protect against user error and hardware failure.

“ACID is only for financial applications.”
Any application where partial writes would cause bugs benefits from ACID: e-commerce inventory, user registration, order processing, session management, and more.

“NoSQL databases are not ACID.”
This was true for most early NoSQL databases. Many modern ones (MongoDB 4.0+, FaunaDB, CockroachDB) now support ACID transactions.

Interview Questions

1. What does ACID stand for and why does it matter?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties guarantee that database transactions are processed reliably - preventing partial writes, data corruption from concurrent access, and data loss from crashes. Any system that handles money, orders, or user accounts depends on ACID guarantees.

2. What is the difference between atomicity and durability?

Atomicity governs during a transaction: either all operations apply or none do. Durability governs after a commit: the committed state is permanently stored and survives crashes. Atomicity prevents partial writes; durability makes full writes stick.

3. Explain isolation levels. Which would you use for a payment system?

The four levels from weakest to strongest are: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. For a payment system, Serializable is the safest - it prevents all anomalies including phantom reads, which are a real concern when checking account balances before deducting funds. In practice, many payment systems use Repeatable Read plus application-level locking (SELECT FOR UPDATE) for performance.

4. What is a “dirty read” and which isolation level prevents it?

A dirty read is when a transaction reads data written by another transaction that has not yet committed. If that other transaction later rolls back, the first transaction has read data that never officially existed. Read Committed and higher prevent dirty reads.

5. How does MVCC implement isolation without blocking reads?

Multi-Version Concurrency Control (MVCC) gives each transaction a consistent snapshot of the database at the moment the transaction started. Readers see the version of each row that existed when they began - not the in-progress writes of other transactions. This means reads never wait for write locks, which dramatically improves concurrency.

6. How would you explain ACID to a non-technical stakeholder?

Think of a bank transfer. You move $500 from your account to a friend’s. ACID means: either both your debit and their credit happen (Atomicity), or neither does. The balances always follow the rules (Consistency). If someone else is also moving money at the same time, the transactions do not corrupt each other (Isolation). And once the transfer shows as complete, a power failure will not undo it (Durability).

Conclusion

ACID properties are the foundation of data reliability in relational databases:

1. Atomicity - Transactions are all-or-nothing. Partial failures roll back completely.
2. Consistency - Every transaction leaves the database in a valid state.
3. Isolation - Concurrent transactions do not corrupt each other’s work.
4. Durability - Committed transactions survive crashes and stay permanent.

Understanding ACID is essential for building backend systems that handle real money, real orders, and real user data. The next topic in this series covers database transactions in more depth - how BEGIN, COMMIT, and ROLLBACK actually work, and when to use savepoints.

For a deeper dive into what indexes do for your queries, see What Are Database Indexes? How They Improve Performance.

References

1. ACID Properties in DBMS - GeeksforGeeks
   https://www.geeksforgeeks.org/dbms/acid-properties-in-dbms/
2. ACID Databases - freeCodeCamp
   https://www.freecodecamp.org/news/acid-databases-explained/
3. What Are ACID Transactions? - DataCamp
   https://www.datacamp.com/blog/acid-transactions
4. ACID - Wikipedia
   https://en.wikipedia.org/wiki/ACID

YouTube Videos

1. “ACID Properties in DBMS | Database Transactions Explained” - Gate Smashers
   https://www.youtube.com/watch?v=pomxJOFVcQs
2. “ACID Transactions Explained Simply” - ByteByteGo
   https://www.youtube.com/watch?v=yaQ5YMWkxq4
3. “Database Transactions & ACID Properties” - Hussein Nasser
   https://www.youtube.com/watch?v=AcqtAEzuoj0