🗄️ Database Management Systems (DBMS) — Master Interview & Revision Guide
Targeted for: Juspay Elimination Round, Systems & Backend Database Architecture Interviews
Core Focus: ACID guarantees, transaction isolation anomalies, B+ Tree indexing internals, SQL injection prevention, WAL / ARIES crash recovery, database concurrency, and distributed data systems.
📑 Complete DBMS Module Index
| # | Document | Core Topics Covered | Juspay High-Yield Relevance |
|---|---|---|---|
| 01 | 01-acid-properties.md | Atomicity (Undo logs), Consistency (Invariants), Isolation (Locking/MVCC), Durability (Redo logs/WAL), Banking transfer failure scenarios | ⭐⭐⭐⭐ Core transactional integrity in payment gateways |
| 02 | 02-indexing.md | B-Tree vs B+ Tree, Hash Indexes, Clustered vs Secondary Indexes, Index Selectivity, Space/Write amplification trade-offs | ⭐⭐⭐⭐ High-frequency system design & latency optimization |
| 03 | 03-transaction-isolation-levels.md | Read Uncommitted, Read Committed, Repeatable Read, Serializable; 3 Anomalies: Dirty Read, Non-Repeatable Read, Phantom Read; MVCC Snapshot Isolation | ⭐⭐⭐⭐⭐ CORE JUSPAY GAUNTLET (Concurrency & double-spend prevention) |
| 04 | 04-normalization.md | Functional dependencies, 1NF, 2NF, 3NF, BCNF; Normalization vs Denormalization trade-offs in high-throughput payment architectures | ⭐⭐⭐ Schema design & relational modeling |
| 05 | 05-sql-vs-nosql.md | Relational vs Document vs Key-Value vs Wide-Column vs Graph; ACID vs BASE; CAP Theorem & PACELC; When to use Postgres vs Redis vs Cassandra | ⭐⭐⭐⭐ Core Systems Architecture trade-offs |
| 06 | 06-query-optimization.md | EXPLAIN ANALYZE inspection, Cost-based optimizers, Slow query diagnosis, Table scans vs Index scans, N+1 query problem & mitigation | ⭐⭐⭐ Production debugging & backend performance |
| 07 | 07-caching-strategies.md | Cache-Aside, Write-Through, Write-Back, Write-Around; Cache Invalidation strategies; Stampede (Dog-piling) prevention; Redis cache topologies | ⭐⭐⭐⭐ High-scale payment caching architecture |
| 08 | 08-database-concurrency-and-locking.md | Shared (S) vs Exclusive (X) locks, Intent locks (IS/IX), 2-Phase Locking (Strict 2PL), Deadlock detection (Wait-For Graph), Optimistic (OCC) vs Pessimistic locking | ⭐⭐⭐⭐⭐ CRITICAL FOR PAYMENT CONCURRENCY |
| 09 | 09-sql-injection-prevention-and-database-security.md | SQL Injection (In-band, Error, Blind, OOB), Prepared statements 2-phase binary wire protocol, ORM pitfalls, B+ Tree page fanout calculations, WAL / ARIES recovery, Composite index leftmost prefix rule | ⭐⭐⭐⭐⭐ Direct Juspay Systems & Security Interview questions |
⚡ 10-Minute Rapid Pre-Interview Cheat Sheet
1. Transaction Isolation Levels & Anomaly Matrix
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read | Standard Mechanism |
|---|---|---|---|---|
| Read Uncommitted | ❌ Allowed | ❌ Allowed | ❌ Allowed | No read locks; reads uncommitted memory dirty pages |
| Read Committed (Postgres Default) | Prevented | ❌ Allowed | ❌ Allowed | Short-term read lock (released immediately after statement) or MVCC snapshot per query |
| Repeatable Read (MySQL InnoDB Default) | Prevented | Prevented | ❌ (Partially mitigated via Next-Key locks in InnoDB) | Long-term read lock (held until commit) or MVCC snapshot per transaction |
| Serializable | Prevented | Prevented | Prevented | Strict 2PL (Two-Phase Locking) or SSI (Serializable Snapshot Isolation) |
- Dirty Read: Transaction B reads uncommitted modifications made by Transaction A. If A rolls back, B read phantom garbage.
- Non-Repeatable Read: Transaction A reads row $R$. Transaction B updates $R$ and commits. Transaction A re-reads $R$ and sees different column values.
- Phantom Read: Transaction A reads all rows matching
WHERE age > 30(gets 5 rows). Transaction B inserts a new user withage = 35and commits. Transaction A re-runs query and sees 6 rows.
2. How Prepared Statements Guarantee SQL Injection Immunity
- The Core Invariant: Query parsing and syntax compilation (
COM_STMT_PREPARE) happens BEFORE parameter binding. - Parameter values are transmitted in separate binary wire frames (
COM_STMT_EXECUTE) and plugged directly into the execution engine as literal values. - The database never runs the parser on bound parameters, making syntax alteration mathematically impossible.
3. B+ Tree vs B-Tree Quick Comparison
- B-Tree: Keys and records/pointers stored in all nodes. Range query requires tree traversal.
- B+ Tree: Internal nodes store only keys and child pointers (higher fanout, lower tree height $\implies$ fewer disk seeks). All data pointers reside in leaf nodes connected via a doubly linked list, enabling $O(\text{range})$ sequential scans.
4. Write-Ahead Logging (WAL)
- Invariant: Changes must be sequentially appended and flushed to the disk log file before dirty database pages in RAM can be written to disk table files.
- Ensures ACID Durability at high throughput (sequential disk I/O vs random writes).
- Crash recovery via ARIES: 1. Analysis $\to$ 2. Redo (repeat history) $\to$ 3. Undo (rollback uncommitted active transactions).