DBMS: ACID Properties, B+ Tree Indexing & Transaction Isolation Levels
Database architecture questions in government tech interviews test both theoretical transaction mechanics and real-world performance optimization under high concurrency.
In-Depth Interview Questions & Model Solutions
Q1Why do relational databases prefer B+ Trees over B-Trees for disk-based indexing?
In a B+ Tree, internal nodes store only search keys (routers) without data pointers, allowing significantly more keys per disk block (higher fan-out / branching factor). This results in shallower tree height and fewer disk I/O operations. Crucially, all data records are stored in leaf nodes, which are linked together in a doubly linked list, enabling highly efficient range queries (e.g. `BETWEEN 10 AND 100`) with O(log N) initial lookup followed by linear pointer traversal.
- Higher fan-out reduces tree height and disk seek operations.
- Leaf nodes form a doubly linked list for optimal range scans.
- Predictable lookup latency since all leaf nodes are at uniform depth.
Q2Explain the 4 SQL Isolation Levels and the anomalies they prevent (Dirty Read, Non-Repeatable Read, Phantom Read).
The ANSI SQL standard defines: (1) Read Uncommitted (allows dirty reads), (2) Read Committed (prevents dirty reads, allows non-repeatable reads), (3) Repeatable Read (prevents dirty & non-repeatable reads; in Postgres/MySQL MVCC also prevents phantom reads via Next-Key locking), and (4) Serializable (strict two-phase locking or SSI, eliminates all anomalies at the cost of concurrency).
- Dirty Read: Reading uncommitted transaction data.
- Non-Repeatable Read: Same query returns different row values within same transaction.
- Phantom Read: New rows inserted by concurrent transaction match range query condition.
Technical Panel Interview Strategy Tips
- Be clear about 2PL (Two-Phase Locking) vs MVCC (Multi-Version Concurrency Control).
- Mention how index selectivity determines whether the query planner picks an Index Scan or Sequential Scan.