Explain SQL ACID properties and the differences between READ COMMITTED and REPEATABLE READ isolation levels.
Quick Revision Summary
ACID guarantees transactional reliability. READ COMMITTED prevents dirty reads; REPEATABLE READ prevents both dirty and non-repeatable reads using MVCC.
Interactive Practice Mode
Formulate your thoughts or take notes before revealing the model solution.
Saved automatically
Model Answer & Deep-Dive Explanation
ACID Properties:
- Atomicity: Transactions execute completely or abort entirely (All-or-Nothing).
- Consistency: Ensures database transitions only from one valid state to another, enforcing constraints.
- Isolation: Concurrent transactions execute without interfering with one another.
- Durability: Committed data persists permanently, surviving power outages or system crashes.
Isolation Levels Comparison:
- READ COMMITTED: A transaction only reads data committed before the query executed. Prevents Dirty Reads. However, Non-Repeatable Reads can occur if another transaction updates and commits row changes between two reads.
- REPEATABLE READ: Ensures that if a row is read once within a transaction, subsequent reads return the exact same snapshot. Prevents Dirty Reads and Non-Repeatable Reads. In PostgreSQL/MySQL InnoDB, snapshot isolation or MVCC (Multi-Version Concurrency Control) is used.
Interviewer Evaluation Points & Scoring Rubric
- 1 1. Defines all 4 ACID pillars. 2. Explains Dirty Read vs Non-Repeatable Read. 3. Mentions MVCC / snapshot isolation.
Related Questions in SQL & Relational Databases
What are Clustered and Non-Clustered Indexes in SQL, and when should you use each?
Intermediate level • 443 views
Explain Redis In-Memory Data Structures and Common Caching Patterns (Cache-Aside, Write-Through, Write-Behind).
Intermediate level • 63 views
How do INNER JOIN and LEFT JOIN differ when a customer has no orders?
Easy level • 58 views
A LEFT JOIN report counts one order for customers who have none. What caused it?
Medium level • 57 views
How would you choose a composite index for a filtered, ordered query?
Medium level • 54 views