Guest Lecture Series – Lecture Notes Repository
Welcome!
This repository contains all lecture notes, diagrams, and examples used in the Database Internals guest lectures. The goal is to help you understand how a database actually works beneath SQL, specifically how it handles many users at once and how it keeps your data safe.
By going through these modules, you’ll understand:
- How databases ensure ACID guarantees
- How transactions run without interfering with each other
- Why anomalies like dirty reads or deadlocks happen
- How modern databases (PostgreSQL, MySQL, etc.) implement locks, timestamps, MVCC, and WAL
- How databases recover safely after a crash
We use a simple, intuitive analogy throughout the course:
A Movie Seat Booking System — to explain concurrency, conflicts, and isolation.
| # | Module | Description |
|---|---|---|
| 01 | Transaction Fundamentals | What is a transaction? Understanding ACID, transaction lifecycle, and the basics of locking. |
| 02 | Isolation Levels | Dirty reads, non-repeatable reads, phantoms, write skew, and how databases prevent them. |
| 03 | Two-Phase Locking (2PL) | Shared vs. exclusive locks, growing & shrinking phases, strict 2PL, and deadlocks. |
| 04 | Timestamp Ordering &OCC | How databases handle concurrencywithoutlocks using timestamps and optimistic validation. |
| 05 | MVCC | How snapshot isolation works. Version chains, visibility rules, and why vacuuming is needed. |
| # | Module | Description |
|---|---|---|
| 06 | Crash Recovery Techniques | Why recovery is hard. Introducing Write-Ahead Logging (WAL) and Shadow Paging. |
| 07 | ARIES Recovery | The standard recovery algorithm:Analysis → Redo → Undo. |
- Lecture Notes for each module
- SQL examples to replicate anomalies and test locking behavior
- Diagrams explaining MVCC snapshots, WAL logs, lock schedules, and recovery phases
- A consistent example throughout the course: Movie Seat Booking
- Start with Module 1 – each module builds on the previous one.
- Try the SQL snippets – run them on a local PostgreSQL instance to observe locks, waits, and anomalies.
- Refer to the diagrams inside the image/ folder whenever you need a visual explanation.
- Move through the modules in order for the best learning experience.
- Atomicity: A transaction is all-or-nothing
- Isolation: Transactions shouldn’t interfere with each other
- Serializability: The final result should look like transactions ran one-by-one
- Deadlock: Two transactions waiting for each other forever
- WAL: Log is written first, data page later
- LSN: ID used to order all log records
- Dirty Page: Modified in memory but not yet saved to disk
These are the primary sources and recommended readings used while preparing the lecture notes:
Books
- Database System Concepts – Silberschatz, Korth, Sudarshan
- Designing Data-Intensive Applications – Martin Kleppmann
University Course
- CMU – Introduction to Database Systems (15-445 / 645) - Lectures by Andy Pavlo (available online) — excellent for understanding storage, WAL, concurrency, and recovery.
If you have questions about the concepts or need clarification, feel free to reach out on LinkedIn: Rathish Kumar B
Found a typo? Want to fix a diagram or improve an explanation?
Feel free to open a PR - contributions are welcome.
Just tell me!