Explain database transactions and ACID
Overview
This question evaluates a candidate's understanding of database transactions including ACID properties, isolation levels (read uncommitted, read committed, repeatable read, serializable), common anomalies (dirty reads, non-repeatable reads, phantom reads), MVCC, and commit/rollback semantics, testing competency in transactional correctness and concurrency control. Commonly asked in System Design and database contexts because it probes reasoning about data integrity, visibility, and durability under concurrent workloads, and it tests both conceptual understanding of transactional guarantees and practical application of isolation behavior in relational database systems.
Database Transactions and ACID under Concurrency
Prompt
You are working with a relational database that supports transactions and multiple isolation levels. Explain:
-
The ACID properties of transactions:
- Define atomicity, consistency, isolation, and durability.
-
How common isolation levels differ and which anomalies they prevent:
- Isolation levels: read uncommitted, read committed, repeatable read, serializable.
- Anomalies: dirty reads, non-repeatable reads, phantom reads.
- How commits and rollbacks work in practice (including the effect on visibility and durability).
- How MVCC (multi-version concurrency control) helps achieve isolation.
-
Provide a concrete example:
- A simple schema.
- A sequence of statements from two concurrent sessions that demonstrates correct transactional usage under concurrency (i.e., no anomalies), and briefly explain why it works.
Assume a mainstream RDBMS (e.g., PostgreSQL or MySQL/InnoDB). If behavior differs by engine, note the typical difference briefly.
Solution
Login required