What are ACID Properties in DBMS?
Understand ACID properties — Atomicity, Consistency, Isolation, Durability — with real examples, SQL transactions and common DBMS interview questions.
Expected Interview Answer
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four guarantees a database transaction must satisfy so that data stays correct even under failures and concurrent access.
Atomicity means a transaction either completes fully or not at all. Consistency means every transaction moves the database from one valid state to another, preserving all constraints. Isolation means concurrent transactions do not interfere with each other’s intermediate states. Durability means once a transaction commits, its changes survive crashes and power loss.
- Prevents partial updates from corrupting data
- Keeps constraints and business rules valid
- Makes concurrent access safe
- Guarantees committed data survives failures
AI Mentor Explanation
A completed run in cricket needs both batters to reach the opposite crease — if one is run out mid-run, the run doesn’t half-count; it simply isn’t scored. That is atomicity: all or nothing. The scoreboard must always obey the rules of the game (consistency), two scorers updating it can’t garble each other’s entries (isolation), and once a run is entered in the official scorebook it stands even if the electronic board loses power (durability).
Step-by-Step Explanation
Step 1
Atomicity
All operations in a transaction succeed together or are rolled back together.
Step 2
Consistency
Constraints, triggers and rules hold before and after every transaction.
Step 3
Isolation
Concurrent transactions behave as if they ran one after another.
Step 4
Durability
Committed changes are persisted (write-ahead logs) and survive crashes.
What Interviewer Expects
- Expansion of the acronym with a one-line definition of each property
- A concrete example — bank transfer is the classic
- Awareness of isolation levels and their trade-offs
- How databases implement durability (write-ahead logging)
Common Mistakes
- Mixing up consistency (ACID) with consistency in CAP theorem
- Describing isolation as "one user at a time" rather than controlled concurrency
- Unable to give a real-world example
- Claiming NoSQL databases can never provide ACID guarantees
Best Answer (HR Friendly)
“ACID properties — Atomicity, Consistency, Isolation and Durability — are the guarantees that keep database transactions reliable. They ensure operations complete fully or not at all, data always follows the rules, simultaneous users don’t corrupt each other’s work, and saved data survives failures.”
Code Example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- both updates apply, or ROLLBACK undoes bothFollow-up Questions
- What are the SQL isolation levels and what anomalies does each allow?
- What is a dirty read, non-repeatable read and phantom read?
- How does write-ahead logging provide durability?
- How does ACID relate to the CAP theorem?
MCQ Practice
1. Which ACID property guarantees a transaction is all-or-nothing?
Atomicity means every operation in the transaction commits together or none do.
2. Committed data surviving a power failure demonstrates which property?
Durability guarantees committed changes persist through crashes, typically via write-ahead logs.
3. Two concurrent transactions not seeing each other’s intermediate state is?
Isolation controls how concurrent transactions interact, hiding uncommitted intermediate states.
Flash Cards
A in ACID? — Atomicity — a transaction fully completes or fully rolls back.
C in ACID? — Consistency — every transaction preserves all database rules and constraints.
I in ACID? — Isolation — concurrent transactions don’t expose intermediate states to each other.
D in ACID? — Durability — committed changes survive crashes and restarts.