DBMS · HARD CODEX

DBMS Interview Questions — Hard

Q: Detail the Transaction Table, Dirty Page Table, and the role of LSNs in ARIES.

Log Sequence Number (LSN): A unique, monotonically increasing integer assigned to every log record. Transaction Table: Maps active transactions to their LastLSN (the LSN of the most recent log record

Q: Explain Write-Ahead Logging (WAL) rules mathematically.

WAL Theorem: A page modification cannot be written to disk before the log record describing the modification is flushed to non-volatile disk storage. Mathematical rule: Let PageLSN be the LSN of the m

Q: Detail the differences between strict 2PL, rigorous 2PL, and conservative 2PL.

Strict 2PL: Follows 2PL, but forces transactions to hold all Exclusive (write) locks until commit/abort. Prevents cascading rollbacks. Rigorous 2PL: Forces transactions to hold all locks (both Shared

Q: Explain Multi-Version Two-Phase Locking (MV2PL) and how it achieves lock-free reads.

Mechanism: Combines MVCC with 2PL. Behavior: Writes create a new version of the document/row, acquiring exclusive locks only on the write version. Readers access a stable, committed snapshot version c

Deep runtime internals, memory models, distributed design, concurrency failure modes, and architectural decisions.

50 Theory Questions7 Implementation Folios5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 50 questions

Q1Explain the ARIES recovery algorithm in detail.

ARIES (Algorithms for Recovery and Isolation Exploiting Semantics): A state-of-the-art database recovery framework.
Phase 1: Analysis Phase:
- Scans the transaction log forward from the last checkpoint.
- Identifies all active transactions (dirty transactions) and dirty pages in memory at the time of the crash.
Phase 2: Redo Phase:
- Replays all logged operations forward from the oldest unwritten page (using RecLSN in the Dirty Page Table).
- Restores the database state to the exact moment of the crash (repeating history, including aborted transactions).
Phase 3: Undo Phase:
- Scans the log backward, reversing the modifications of all transactions that were active or aborted but never committed prior to the crash. Writes Compensation Log Records (CLRs) for each reversed write to prevent duplicate undos during subsequent crashes.

Q2Detail the Transaction Table, Dirty Page Table, and the role of LSNs in ARIES.

Log Sequence Number (LSN): A unique, monotonically increasing integer assigned to every log record.
Transaction Table: Maps active transactions to their LastLSN (the LSN of the most recent log record written by that transaction).
Dirty Page Table (DPT): Maps dirty in-memory pages to their RecLSN (the oldest LSN that made the page dirty since it was last flushed to disk).
PageLSN: A metadata header field on every physical page on disk and in memory indicating the LSN of the most recent write applied to that page. During recovery, if PageLSN >= LogRecordLSN, the database engine skips the write (already applied).

Q3Explain Write-Ahead Logging (WAL) rules mathematically.

WAL Theorem: A page modification cannot be written to disk before the log record describing the modification is flushed to non-volatile disk storage.
Mathematical rule: Let PageLSN be the LSN of the most recent modification of page $P$, and let FlushedLSN be the highest LSN flushed to disk. The storage engine must guarantee: PageLSN <= FlushedLSN before writing page $P$ to disk.

Q4Detail the differences between strict 2PL, rigorous 2PL, and conservative 2PL.

Strict 2PL: Follows 2PL, but forces transactions to hold all Exclusive (write) locks until commit/abort. Prevents cascading rollbacks.
Rigorous 2PL: Forces transactions to hold all locks (both Shared and Exclusive) until commit/abort. Guarantees that the serialization order matches the commit order.
Conservative 2PL: Forces the transaction to acquire all required locks up front before it begins execution. If any lock is unavailable, the transaction waits without holding any locks. Completely eliminates deadlocks, but drastically restricts concurrent throughput.

Q5Explain Multi-Version Two-Phase Locking (MV2PL) and how it achieves lock-free reads.

Mechanism: Combines MVCC with 2PL.
Behavior:
- Writes create a new version of the document/row, acquiring exclusive locks only on the write version.
- Readers access a stable, committed snapshot version corresponding to their transaction start timestamp.
Impact: Readers never block writers, and writers never block readers, yielding massive concurrency gains in high-throughput transactional databases.

Unlock the remaining 52 DBMS (Hard) questions

You've completed the 5 free sample questions. Get unrestricted lifetime access to every question, model answer, implementation challenge, and all 27+ technologies for a single payment.

Unlock Everything — Already Enrolled? Sign In

One-time settlement () · Lifetime access · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing DBMS

EASY LEVEL

Foundations & Core Concepts

MEDIUM LEVEL

Mechanisms & Practical Patterns