Explain ACID properties and how relational databases implement them.
sql-sen-001
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means a transaction is all-or-nothing — the database uses a write-ahead log (WAL) or undo log to roll back partial changes on failure. Consistency means a transaction brings the database from one valid state to another, enforced via constraints, triggers, and application logic. Isolation controls how concurrent transactions see each other's intermediate state; databases implement this via lock-based concurrency control or MVCC (Multi-Version Concurrency Control), with isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) offering different tradeoffs between correctness and throughput. Durability means committed data survives crashes, achieved by flushing the WAL to disk before acknowledging a commit. PostgreSQL uses MVCC with an autovacuum to reclaim dead row versions; SQL Server uses row versioning (with RCSI) or traditional locking depending on configuration.
Code example
-- Demonstrating isolation level in PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- Both reads see the same snapshot; no non-repeatable reads
SELECT balance FROM accounts WHERE id = 1; -- 1000
-- ... some application logic ...
SELECT balance FROM accounts WHERE id = 1; -- still 1000 even if another txn committed
COMMIT;
-- Serializable prevents phantom reads
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM bookings WHERE date = '2025-06-01'; -- 5
-- Another txn inserts a 6th booking and commits
-- Under SERIALIZABLE this txn would abort with serialization failure
INSERT INTO bookings(date, seat) VALUES ('2025-06-01', 'A7');
COMMIT;Follow-up
What anomalies can occur at READ COMMITTED isolation level? Give a concrete example of a phantom read and how SERIALIZABLE prevents it.