ACID Properties and Transactions in DBMS: Atomicity, Consistency, Isolation, Durability
What a transaction guarantees, how databases keep those promises, and the isolation levels interviewers ask about.
Coding problems with instant test-case feedback
Write Python, C++, C or JavaScript in your browser and see which hidden tests pass.
A transaction is a group of database operations that must succeed or fail as one unit. Moving ₹500 from account A to account B is two updates; if the system crashes between them, money disappears. ACID is the set of guarantees that prevents this.
1. A transaction in SQL#
Every relational database lets you mark the start and end of a transaction. Until COMMIT, the changes are provisional; ROLLBACK undoes them all.
| 1 | BEGIN; |
| 2 | |
| 3 | UPDATE accounts SET balance = balance - 500 WHERE id = 'A'; |
| 4 | UPDATE accounts SET balance = balance + 500 WHERE id = 'B'; |
| 5 | |
| 6 | -- Both rows change together, or neither does |
| 7 | COMMIT; -- or ROLLBACK; to undo both updates |
2. The four properties#
Each letter answers a different "what if" question.
| Property | Guarantee | Bank transfer example |
|---|---|---|
| Atomicity | All operations happen, or none do | A is never debited without B being credited |
| Consistency | A transaction moves the database from one valid state to another | Total money and constraints like balance ≥ 0 still hold afterwards |
| Isolation | Concurrent transactions do not see each other’s half-done work | A report running mid-transfer never sees money "in flight" |
| Durability | Once committed, changes survive crashes | After the app shows "Transfer successful", a power cut cannot undo it |
Practise placement aptitude on the real test pattern
TCS NQT, Infosys, Wipro, Accenture and Cognizant style mocks with section-wise analysis.
3. How databases implement ACID#
Atomicity and durability come from the write-ahead log (WAL). Before a change is applied to the data files, a record of it is written to the log and flushed to disk. After a crash, the database replays committed transactions from the log and undoes uncommitted ones.
Isolation comes from concurrency control: locking (shared locks for reads, exclusive locks for writes, often with two-phase locking) or multi-version concurrency control (MVCC), where readers see a snapshot and never block writers. PostgreSQL and MySQL InnoDB both use MVCC.
Consistency is shared between the database (constraints, foreign keys, triggers) and the application (business rules such as "a transfer cannot exceed the daily limit").
4. Isolation levels and read phenomena#
Full isolation is expensive, so SQL defines weaker levels. Each level allows or prevents three anomalies: a dirty read (reading uncommitted data), a non-repeatable read (the same row returns different values within one transaction) and a phantom read (a repeated query returns new rows).
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible (per the SQL standard) |
| Serializable | Prevented | Prevented | Prevented |
PostgreSQL defaults to Read Committed; MySQL InnoDB defaults to Repeatable Read. Mentioning that real systems rarely run at Serializable by default shows practical understanding.
Test Your Concept Mastery
Verify your retention on: ACID Properties and Transactions in DBMS: Atomicity, Consistency, Isolation, Durability
Which ACID property ensures a committed transfer survives a power failure?
Reading a row another transaction has changed but not yet committed is called…
Ready for a full proctored assessment?
Take a free aptitude mock on a real company pattern, with a worked solution for every question.
