📼 धडा 15 — Engine च्या आत: आधी log

📍 तुम्ही इथे आहात: 18 पैकी धडा 15 · मागील: lesson-14-advanced-sql · पुढील: lesson-16-replication-failover

भाग 3 — अधिक खोलात. धडे 01–12 नी रेकॉर्ड रूम बांधली आणि चालवली. धडे 13–18 हे interviews आणि incidents मध्ये विचारले जाणारे विषय आहेत — प्रत्येक त्याच school.db वर प्रत्यक्ष चालतो.

📦 या ब्रँचमध्ये काय आहे

धडे 01–15, आणि db/demo.py मधले wal(): ते school.db ला WAL mode मध्ये नेते, reader transaction च्या मध्यात असताना writer ला commit करू देते, reader आपला snapshot टिकवून ठेवतो हे दाखवते, school.db-wal मोजते, त्याचा checkpoint करते, आणि परत पूर्वीच्या mode मध्ये नेते.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

मोठ्या नोंदवहीतले पान पुन्हा लिहिणे हळू आणि धोक्याचे असते — अर्ध्यावर वीज गेली तर पान अर्धे जुने, अर्धे नवे राहते. म्हणून काळजीपूर्वक काम करणारी अभिलेखपाल आधी डायरीत 📼 एक ओळ लिहिते: "page 7: Aishwarya → Aishwarya S". डायरीतली ओळ कागदावर सुरक्षित झाल्यावरच ती "झाले ✅" म्हणते. नंतर, शांत वेळी, ती डायरीतल्या ओळी मोठ्या नोंदवहीत उतरवते. वीज गेली, तर ती डायरी वाचते आणि काम पूर्ण करते. ती डायरी म्हणजे write-ahead log (WAL).

आणि ती काम करत असताना, एक मिनिटापूर्वी नोंदवही उघडलेल्या वाचणाऱ्याला पान त्याने सुरुवात केली तेव्हा जसे होते तसेच दिसत राहते — खोलीचा त्याचा स्वतःचा फोटो. हेच MVCC: readers writers ना कधी अडवत नाहीत, writers readers ना कधी अडवत नाहीत.

🗺️ आकृती

sequenceDiagram
    participant R as 👀 reader
    participant DB as 🗄️ school.db (WAL mode)
    participant W as ✍️ writer
    R->>DB: BEGIN · SELECT name → 'Aishwarya'
    W->>DB: UPDATE → 'Aishwarya S' · COMMIT (appended to the WAL)
    DB-->>W: OK — no waiting for the reader
    R->>DB: SELECT again → still 'Aishwarya' (snapshot)
    R->>DB: COMMIT · new SELECT → 'Aishwarya S'

🗺️ काढलेली आवृत्ती + एक lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l15

❓ काय

Write-ahead log — प्रत्येक बदल log मध्ये जोडला जातो आणि COMMIT परत येण्याआधी disk वर flush होतो (fsync). Data pages नंतर checkpoint वेळी लिहिली जातात. Crash नंतर engine log पुन्हा चालवते (replay). हेच ACID मधले D (धडा 04).
Buffer cache — pages memory मध्ये राहतात; log मुळे ती उशिरा disk वर पोहोचली तरी सुरक्षित असते.
MVCC (multi-version concurrency control) — एका row च्या अनेक versions असू शकतात; प्रत्येक transaction ला ती सुरू झाली तेव्हा commit झालेल्या versions दिसतात. Postgres जुन्या row versions table मध्येच ठेवते आणि VACUUM ने साफ करते; WAL mode मधले SQLite नवी pages checkpoint होईपर्यंत -wal file मध्ये ठेवते.
SQLite चा default (rollback journal) वेगळा आहे: writer ला संपूर्ण file लागते, म्हणून तो readers ची वाट पाहतो — हाच धडा 07 मधला database is locked.

🤔 का

हे तीन गोष्टी समजावते ज्या लोक अन्यथा डोळे झाकून मानतात: OK मिळालेला commit वीज गेल्यावरही का टिकतो, Postgres मध्ये readers writers ची वाट का पाहत नाहीत, आणि तुम्ही फक्त UPDATE करत असताना table का वाढू शकतो (VACUUM ची वाट पाहणाऱ्या जुन्या versions). हाच log replication (L16) आणि change data capture (L18) ला पुरवतो.

🔧 कसे (या repo मध्ये)

db/demo.py मधले wal() — एकाच file ला दोन खरी connections, आणि PRAGMA wal_checkpoint(TRUNCATE) च्या आधी आणि नंतर मोजलेली -wal file.

🧪 करून पाहा

python3 db/demo.py wal
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
print(c.execute("PRAGMA journal_mode").fetchone())          # delete — the demo switched it back
print(c.execute("PRAGMA journal_mode=WAL").fetchone())      # try it: now open a second python and write while you read
c.execute("PRAGMA journal_mode=DELETE")
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

journal mode before: delete → after: wal; writer कोणत्याही lock error शिवाय 'Aishwarya S' commit करतो; त्याच transaction मधल्या reader ला अजूनही 'Aishwarya' दिसते, आणि त्याच्या COMMIT नंतर 'Aishwarya S'; -wal file काही kilobytes ची असते, मग checkpoint नंतर 0 bytes.

🏁 तुम्ही आत्ताच काय सिद्ध केले

तुम्ही durability आणि snapshot isolation घडताना पाहिले: आधी log, नंतर pages, आणि प्रत्येक reader खोलीचा स्वतःचा फोटो ठेवतो.

⚠️ नेहमीच्या चुका

COMMIT data file लिहितो असे मानणे — तो log लिहितो
वेगासाठी fsync / synchronous बंद करणे आणि crash मध्ये commits गमावणे
खूप लांब transactions: त्यांना अजून दिसू शकतील अशा row versions Postgres VACUUM करू शकत नाही, आणि tables फुगतात
disk वरच्या WAL/log वाढीकडे दुर्लक्ष (धडा 12 ची चेकलिस्ट)
प्रत्येक engine SQLite च्या default journal सारखे वागते असे गृहीत धरणे — Postgres मध्ये readers writers ना कधीच अडवत नाहीत

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: WAL settings, checkpoint tuning, autovacuum आणि long-running-transaction alerts हे database on-call चे नेहमीचे विषय आहेत.

⏭️ पुढे

git checkout lesson-16-replication-failover — तो log खोलीच्या एका प्रतीकडे पाठवा.

📼 Lesson 15 — Inside the engine: the log comes first

📍 You are here: Lesson 15 of 18 · Previous: lesson-14-advanced-sql · Next: lesson-16-replication-failover

Part 3 — going deeper. Lessons 01–12 built and ran the record room. Lessons 13–18 are the topics interviews and incidents ask about — each one runs for real on the same school.db.

📦 What's in this branch

Lessons 01–15, plus wal() in db/demo.py: it switches school.db to WAL mode, lets a writer commit while a reader is in the middle of a transaction, shows the reader keeping its snapshot, measures school.db-wal, checkpoints it, and switches back.

🧒 Explain like I'm 5

Rewriting a page in the big register is slow and risky — if the lights go out halfway, the page is half old, half new. So the careful archivist first writes one line in a diary 📼: "page 7: Aishwarya → Aishwarya S". Only when the diary line is safely on paper does she say "done ✅". She copies diary lines into the big register later, when it is quiet. If the lights go out, she reads the diary and finishes the job. That diary is the write-ahead log (WAL).

And while she works, a reader who opened the register a minute ago keeps seeing the page as it was when they started — their own photo of the room. That is MVCC: readers never block writers, writers never block readers.

🗺️ Diagram

sequenceDiagram
    participant R as 👀 reader
    participant DB as 🗄️ school.db (WAL mode)
    participant W as ✍️ writer
    R->>DB: BEGIN · SELECT name → 'Aishwarya'
    W->>DB: UPDATE → 'Aishwarya S' · COMMIT (appended to the WAL)
    DB-->>W: OK — no waiting for the reader
    R->>DB: SELECT again → still 'Aishwarya' (snapshot)
    R->>DB: COMMIT · new SELECT → 'Aishwarya S'

🗺️ Drawn version + a lab: https://school-edh.pages.dev/database/lesson-diagrams.html#l15

❓ What

Write-ahead log — every change is appended to a log and flushed to disk (fsync) before COMMIT returns. Data pages are written later at a checkpoint. After a crash, the engine replays the log. This is the D in ACID (lesson 04).
Buffer cache — pages live in memory; the log makes it safe for them to reach disk late.
MVCC (multi-version concurrency control) — a row can have several versions; each transaction sees the versions that were committed when it started. Postgres keeps old row versions in the table and cleans them with VACUUM; SQLite in WAL mode keeps the new pages in the -wal file until a checkpoint.
SQLite's default (rollback journal) is different: a writer needs the whole file, so it waits for readers — that is the database is locked from lesson 07.

🤔 Why

It explains three things people otherwise take on faith: why an OK'd commit survives a power cut, why readers don't wait for writers in Postgres, and why a table can grow when you only UPDATE (old versions waiting for VACUUM). The same log feeds replication (L16) and change data capture (L18).

🔧 How (in this repo)

wal() in db/demo.py — two real connections to the same file, and the -wal file measured before and after PRAGMA wal_checkpoint(TRUNCATE).

🧪 Try it

python3 db/demo.py wal
python3 - <<'EOF'
import sqlite3
c = sqlite3.connect("db/school.db")
print(c.execute("PRAGMA journal_mode").fetchone())          # delete — the demo switched it back
print(c.execute("PRAGMA journal_mode=WAL").fetchone())      # try it: now open a second python and write while you read
c.execute("PRAGMA journal_mode=DELETE")
EOF

✅ Verify — what you should see

journal mode before: delete → after: wal; the writer commits 'Aishwarya S' with no lock error; the reader in the same transaction still sees 'Aishwarya', and 'Aishwarya S' after its COMMIT; the -wal file has a few kilobytes, then 0 bytes after the checkpoint.

🏁 What you just proved

You watched durability and snapshot isolation happen: the log first, pages later, and each reader keeping its own photo of the room.

⚠️ Common mistakes

believing COMMIT writes the data file — it writes the log
turning off fsync / synchronous for speed and losing commits on a crash
very long transactions: Postgres cannot VACUUM row versions they might still see, and tables bloat
ignoring WAL/log growth on disk (lesson 12's checklist)
assuming every engine behaves like SQLite's default journal — Postgres readers never block writers

🏭 Why this matters in production: WAL settings, checkpoint tuning, autovacuum and long-running-transaction alerts are standard database on-call topics.

⏭️ Next

git checkout lesson-16-replication-failover — ship that log to a copy of the room.

← Previousadvanced sql Next →replication failover

This page is the lesson's README from the lesson-15-inside-the-engine branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.