What are the ACID properties of a transaction?
ACID stands for Atomicity, Consistency, Isolation and Durability. A transaction either fully happens or not at all, leaves the data valid according to its rules, behaves as if it ran without interference from other transactions, and stays saved once committed, even if the server crashes.
Explain it simply
Think of sending ₹500 to a friend over UPI. Two things must happen: ₹500 leaves your account, and ₹500 arrives in theirs.
- Atomicity: both happen, or neither does. You never lose ₹500 into thin air because the app crashed halfway.
- Consistency: the rules always hold. Your balance can never go below zero if the bank forbids it, and the total money in the system stays correct.
- Isolation: if you and your sibling pay from the same joint account at the same moment, each payment behaves as if it ran alone. They cannot both spend the same last ₹500.
- Durability: once the app shows "Payment successful", the record survives a power cut or server restart.
Example
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A101';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B202';
-- If anything fails above (no such account, a CHECK constraint, a crash),
-- run ROLLBACK and neither change is kept.
COMMIT;With a rule in the table definition:
ALTER TABLE accounts ADD CONSTRAINT balance_not_negative CHECK (balance >= 0);If A101 only has ₹300, the first UPDATE breaks the CHECK constraint, the transaction fails, and no money moves. That is atomicity and consistency working together.
How it works
| Property | What it guarantees | How databases usually achieve it |
|---|---|---|
| Atomicity | All or nothing | Undo information or row versioning, so changes can be rolled back |
| Consistency | Constraints and rules hold before and after | Primary keys, foreign keys, CHECK, NOT NULL, triggers, plus correct application logic |
| Isolation | Concurrent transactions do not see each other's half-done work | Locks and/or MVCC snapshots, controlled by isolation levels |
| Durability | Committed data survives crashes | Write-ahead log (WAL / redo log) flushed to disk before COMMIT returns |
The write-ahead log is the key idea behind durability. Before the database tells you "committed", it writes a record of the change to a log file on disk. If the server crashes before the actual table pages are written, it replays the log on restart.
Isolation is the property most often relaxed for speed. Databases offer levels such as Read Committed, Repeatable Read and Serializable, which trade strictness for concurrency.
Real-world use
- Banking and UPI transfers: debit and credit must succeed together.
- IRCTC seat booking: marking a seat as booked and creating the ticket must happen as one unit, and two people must not get the same berth.
- E-commerce checkout: reducing stock, creating the order and recording the payment in one transaction.
Common mistakes
- Mixing up ACID consistency with "consistency" in distributed systems (the C in CAP). In ACID it means the data obeys its rules; in CAP it means every node sees the same latest data.
- Thinking the database alone guarantees consistency. It enforces the constraints you declare. If the application logic is wrong, the data can still be wrong.
- Assuming every statement is isolated perfectly. The default isolation level in most databases is Read Committed, which allows some anomalies.
- Forgetting to wrap related statements in a transaction. With autocommit on, each statement commits separately.
What to say in the interview
"ACID describes what a reliable transaction guarantees. Atomicity means all of its changes happen or none do. Consistency means it moves the database from one valid state to another, respecting constraints like keys and checks. Isolation means concurrent transactions do not see each other's uncommitted work, controlled by isolation levels. Durability means once it commits, the change survives a crash, usually because it was written to a write-ahead log first. A bank transfer is the standard example: debit and credit must succeed together."
Follow-up questions
- What are the isolation levels, and which anomalies does each allow?
- What is a write-ahead log?
- How do NoSQL databases relate to ACID, and what is BASE?