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 retu 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 • 388 views
What is Database Indexing, how does a B+ Tree index work, and when should you avoid creating indexes?
Intermediate level • 2 views
Explain Database Normalization from 1NF through BCNF with real-world anomaly examples.
Intermediate level • 2 views
Explain Redis In-Memory Data Structures and Common Caching Patterns (Cache-Aside, Write-Through, Write-Behind).
Intermediate level • 2 views
Write a query for the second-highest distinct non-NULL salary. What happens if it does not exist?
Medium level • 2 views