What do the ACID properties mean?

Atomicity, consistency, isolation and durability explained with a bank transfer, plus isolation levels and the read anomalies they prevent.

0 0
Read Time:1 Minute, 43 Second

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.

Previous post What is normalisation? Explain 1NF, 2NF and 3NF.
Next post What happens when you type a URL in the browser and press Enter?
Subscribe
Notify of
guest

0 Comments
Oldest
Newest Most Voted
Close
0
Would love your thoughts, please comment.x
()
x