ACID describes what a database guarantees for a transaction, a group of statements that must succeed or fail together. Use one example for all four: moving ₹1,000 from account A to account B.
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;
Atomicity: all or nothing
If the server crashes after the first update, the database rolls it back. A is never debited without B being credited.
Consistency: rules always hold
The transaction takes the database from one valid state to another. Constraints such as CHECK (balance >= 0), foreign keys and unique keys are enforced; if one would break, the whole transaction fails.
Isolation: transactions do not see each other’s half-done work
Two transfers running at once behave as if they ran one after another (at the strictest level). Databases offer levels that trade safety for speed:
Level Dirty read Non-repeatable read Phantom read
READ UNCOMMITTED possible possible possible
READ COMMITTED no possible possible
REPEATABLE READ no no possible*
SERIALIZABLE no no no
* MySQL InnoDB also blocks most phantoms at this level.
- Dirty read: reading another transaction’s uncommitted change.
- Non-repeatable read: the same row gives a different value when read twice.
- Phantom read: re-running a query returns new rows.
Defaults differ: PostgreSQL, Oracle and SQL Server use READ COMMITTED; MySQL InnoDB uses REPEATABLE READ.
Durability: committed means saved
Once COMMIT returns, the change survives a crash or power cut, because it is written to a write-ahead or redo log on disk before the commit is acknowledged.
Follow-ups
- What is a deadlock and how does the database resolve it?
- Optimistic versus pessimistic locking.
- How do microservices keep consistency without one ACID transaction (sagas, the outbox pattern)?
- What does BASE mean in NoSQL databases?
This answer was written by Debugger’s Space as practice material. It is asked in most Indian IT services interviews (TCS, Infosys, Wipro, HCLTech, Tech Mahindra, LTIMindtree, Accenture, Cognizant, Capgemini, IBM). See each company’s hiring steps and practice questions on Debugger’s Space.