What Is ACID: Atomicity, Consistency, Isolation, Durability

ACID is the set of four properties a DBMS guarantees when executing transactions: Atomicity, Consistency, Isolation and Durability. Together they mean one thing: you can trust your data even if the server crashed mid-operation and thousands of clients are hitting the database at once.

A transaction itself is a group of SQL operations executed as a single unit: either all of them complete successfully, or none of them do. The classic example is a money transfer:

MySQL 8.1
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;

COMMIT;

Anything can happen between the two UPDATE statements: a power outage, a dropped network connection, a crashed DBMS process. If the first command runs and the second doesn't, a thousand dollars simply vanishes. The ACID properties exist precisely so that such stories are impossible. Let's go through each one.

A — Atomicity

A transaction is indivisible: either all of its operations execute, or none of them do.

In the transfer example, atomicity guarantees there is no database state where the money has left one account but never arrived at the other. If anything goes wrong at any step, the DBMS performs a rollback — it returns the data to the state at the start of the transaction, as if it never happened.

You can also trigger a rollback manually with the ROLLBACK command:

MySQL 8.1
START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
-- changed our mind: undo all changes of this transaction
ROLLBACK;

Under the hood the DBMS keeps a change log: before modifying data it records what it is about to do and how to undo it. After a crash the database recovers from the log: committed transactions are completed, unfinished ones are rolled back.

C — Consistency

A transaction moves the database from one valid state to another valid state.

"Valid" is defined by the rules described in the schema: NOT NULL, UNIQUE and CHECK constraints, foreign keys, triggers. If a rule is violated inside a transaction — say, a balance goes negative against a CHECK (balance >= 0) constraint — the whole transaction rolls back.

An important nuance: the DBMS only checks the rules you have described to it. If the rule "total debits equal total credits" exists only in a developer's head, the database won't protect it. Consistency is a joint effort between the DBMS and the schema designer.

I — Isolation

Concurrent transactions do not see each other's intermediate states.

While one client transfers money, another simultaneously sums up all account balances. Without isolation the second client could see the database "halfway through": the money already debited but not yet credited — the total would be off by exactly a thousand.

Ideally transactions should behave as if they ran strictly one after another. But full isolation is expensive: transactions end up waiting for each other. So DBMS offer compromises — isolation levels. The lower the level, the faster the database runs and the more oddities concurrent transactions may observe. These oddities are called read anomalies:

• Dirty read. A transaction sees changes another transaction has not yet committed. If that one rolls back, the data it read never "officially" existed.
• Non-repeatable read. A transaction reads the same row twice and gets different values, because another transaction modified the row and committed in between.
• Phantom read. A transaction runs the same conditional query twice and the second time sees new rows that another transaction managed to insert.

The easiest way to see an anomaly in action is a timeline of two concurrent sessions. Here is a non-repeatable read at the READ COMMITTED level:

Session A did nothing between steps 2 and 4, yet the world changed underneath it. At the REPEATABLE READ level step 4 would return the same 100: the transaction works with a snapshot of the data taken at its start.

The SQL standard defines four isolation levels — each next one rules out more anomalies:

By default PostgreSQL runs at READ COMMITTED and MySQL (InnoDB) at REPEATABLE READ. In practice that is almost always enough, but knowing which anomalies are possible at your level pays off: "flaky" bugs in reports and counters often grow exactly from here.

The level can be changed for a specific transaction:

MySQL 8.1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- accuracy-critical logic
COMMIT;

A transaction ran the query SELECT COUNT(*) FROM orders WHERE status = 'new' twice and got different numbers: another transaction inserted orders in between. What is this anomaly called?

D — Durability

Once a transaction is committed, its result will not be lost.

As soon as the DBMS replies "COMMIT done", the changes survive anything: an emergency server shutdown, a process crash, a reboot. This is achieved by force-flushing a record of the transaction to an on-disk log before confirming (in PostgreSQL it is called the WAL — write-ahead log, in MySQL InnoDB the redo log). Even if the table data itself didn't get updated in time, after a restart the database brings it to the correct state using the log.

The database server crashed and restarted at the moment a transaction had executed the first of its two UPDATE statements. What happens to that change after the restart?

ACID vs BASE: What About NoSQL

The world of distributed NoSQL stores often uses a different set of trade-offs — BASE: Basically Available, Soft state, Eventually consistent. The idea: the system always responds, but data on different nodes may diverge for a while and converge "eventually".

This is neither "worse" nor "better" than ACID — it is a different choice. A payment system needs ACID strictness: a lost transfer is unacceptable. A like counter is fine with BASE: nobody is hurt if the number lags by a second. Many modern systems combine both approaches for different parts of their data.

What Interviewers Ask

ACID is one of the most frequent topics in SQL and database interviews. Typical questions:

• Spell out ACID and explain each property. The money transfer example is enough — it covers all four letters.
• How is atomicity different from consistency? Atomicity is about "all or nothing"; consistency is about the final state obeying the schema rules.
• Which isolation levels do you know, and which is the default? They expect the table above plus your DBMS's defaults.
• What is a dirty read and at which level is it possible? Only at READ UNCOMMITTED.
• How does the database guarantee durability if data is cached in memory? The keyword is the write-ahead log (WAL/redo log): what reaches disk before the confirmation is the log record, not the data pages themselves.

What's Next

ACID is theory best cemented with practice right away:

• how to create and manage transactions — in the lessons Transactions and Creating transactions;
• how the DBMS resolves conflicts between concurrent transactions — in the lesson Locking;
• test yourself against real interview questions — in the interview questions section.