DBMS Topics
ACID Properties
Last Updated : 21 May, 2026
ACID is an acronym for the four key properties that every database transaction must satisfy to ensure data validity even in cases of errors, power failures, and concurren
Overview
ACID is an acronym for the four key properties that every database transaction must satisfy to ensure data validity even in cases of errors, power failures, and concurrent access.
A — Atomicity
Definition
A transaction is treated as a single indivisible unit. Either ALL operations in the transaction are executed successfully, or NONE of them are (all-or-nothing).
Example
| Bank Transfer | ₹10,000 from Account A to Account B |
| 1. Debit A: A = 50,000 − 10,000 = 40,000 | write(A) |
| 2. Credit B: B = 30,000 + 10,000 = 40,000 | write(B) |
| Without Atomicity | A = 40,000, B = 30,000 ← ₹10,000 disappeared! |
| With Atomicity: ROLLBACK | A = 50,000, B = 30,000 ✓ Consistent |
Enforced By
- Transaction Manager using the undo log
- On failure, DBMS undoes all writes of the aborted transaction
C — Consistency
Definition
A transaction takes the database from one valid, consistent state to another valid, consistent state. All defined rules, constraints, and integrity conditions must hold before and after the transaction.
Example
Types of Consistency
Integrity Constraints
- Primary key uniqueness
- Foreign key referential integrity
- CHECK constraints (e.g., salary > 0)
- NOT NULL constraints
Business Rules
- Sum of debits = sum of credits
- Stock quantity cannot be negative
- Order total = sum of line items
Enforced By
- Application logic (developer responsibility)
- Database integrity constraints (CHECK, FK, UNIQUE, NOT NULL)
I — Isolation
Definition
Concurrent transactions execute independently of each other. The intermediate (incomplete) state of a transaction is not visible to other transactions. Each transaction appears to execute as if it were the only transaction running.
Example
| T1: Transfer A | B ₹10,000 T2: Read balance of A |
| T1 | read(A) = 50,000 |
| T1 | write(A = 40,000) ← partially done, not committed |
| T2 | read(A) = ??? |
| WITHOUT Isolation | T2 reads A = 40,000 (dirty read — T1 might rollback!) |
| WITH Isolation | T2 reads A = 50,000 (sees committed value only) |
Isolation Problems (without proper control)
| Problem | Description |
|---|---|
| Dirty Read | Reading uncommitted data from another transaction |
| Non-Repeatable Read | Same query returns different values within a transaction |
| Phantom Read | New rows appear/disappear between two reads of same query |
| Lost Update | One transaction's write overwritten by another |
Isolation Levels (SQL Standard)
| READ UNCOMMITTED | No isolation; all problems possible |
| READ COMMITTED | Prevents dirty reads |
| REPEATABLE READ | Prevents dirty + non-repeatable reads |
| SERIALIZABLE | Prevents all problems; highest isolation |
Enforced By
- Concurrency Control Manager using locks or MVCC
D — Durability
Definition
Once a transaction is committed, its changes are permanent — they survive any subsequent system failures (crashes, power loss, hardware failures).
Example
| T1 commits at 2 | 00 PM (successfully transfers ₹10,000) |
| System crashes at 2 | 01 PM |
| WITHOUT Durability | Data reverts to state before T1's commit |
| WITH Durability | On recovery, T1's changes are restored from log |
Enforced By
Write-Ahead Logging (WAL)
- All changes written to log BEFORE writing to data pages
- Log is on stable storage (survives crashes)
- On recovery: redo all committed transactions from log
Checkpoints
- Periodic flushing of dirty pages to disk
- Reduces log scanning needed during recovery
ACID in Practice
| Property | Responsibility | Mechanism |
|---|---|---|
| Atomicity | Transaction Manager | Undo log, Rollback |
| Consistency | App + DB Constraints | CHECK, FK, Business logic |
| Isolation | Concurrency Manager | Locks, MVCC, Timestamps |
| Durability | Recovery Manager | Write-ahead log, Checkpoints |
BASE vs ACID (NoSQL Comparison)
NoSQL databases often trade ACID for scalability using BASE:
Traditional relational databases follow ACID. Modern NoSQL systems (Cassandra, DynamoDB) follow BASE for better performance at scale.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for ACID Properties.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, acid, properties, acid properties
Related DBMS Topics