🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

✏️ धडा 04 — Writes आणि transactions: pencil ने लिहिलेली खातेवही

📍 तुम्ही इथे आहात: 18 पैकी धडा 04 · मागे: lesson-03-sql-reads · पुढे: lesson-05-data-modelling

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

धडे 01–03, आणि नोंदवह्या सुरक्षितपणे बदलणे: INSERT, UPDATE, DELETE, आणि transaction — BEGIN … COMMIT, किंवा ROLLBACK — जे ACID ची चार वचने खरी करते.

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

ऐश्वर्याला 3A मधून 3B मध्ये हलवणे म्हणजे दोन नोंदवह्यांमधील दोन ओळी: तिचा class pointer बदला, आणि वर्गांची संख्या update करा. दोन्हींच्या मध्येच वीज गेली तर? अर्धे स्थलांतर — ऐश्वर्या 3B मध्ये, पण संख्या अजूनही 3A सांगते.

म्हणून दप्तरदार आधी pencil ने लिहितो ✏️. BEGIN pencil उघडते. दोन्ही बदल लिहिले जातात. काहीही बिघडले — error, नाकारलेला pointer, crash — तर दप्तरदार सगळे खोडून टाकतो (ROLLBACK) आणि नोंदवह्या अगदी पूर्वीसारख्या दिसतात. दोन्ही ओळी बरोबर असतील तेव्हाच दप्तरदार त्यांच्यावर शाईने (COMMIT) गिरवतो, एकदम सगळे, म्हणजे त्यानंतर वीज गेली तरी दोन्ही बदल कपाटात राहतात.

हेच transaction, आणि त्याच्या चार वचनांतून ACID बनते:

Atomic — सगळ्या ओळी किंवा एकही नाही.
Consistent — शाई वाळल्यानंतरही प्रत्येक constraint पाळला जातो.
Isolated — इतर clerks ना तुमच्या pencil खुणा दिसत नाहीत (धडा 07).
Durable — शाई वीज गेल्यावरही टिकते.

🗺️ आकृती

flowchart LR
    b["✏️ BEGIN<br/>UPDATE students SET class_id = 2 WHERE id = 1"]
    u2["UPDATE students SET class_id = 99 WHERE id = 4<br/>💥 FOREIGN KEY constraint failed"]
    rb["↩️ ROLLBACK<br/>both pencil marks erased — nothing changed"]
    ok["✅ COMMIT<br/>ink: all of it, at once, durable"]
    b --> u2 -.->|"1 any error"| rb
    b -.->|"2 no errors"| ok

❓ काय

INSERT INTO students (name, roll_no, class_id) VALUES (…), UPDATE students SET class_id = 2 WHERE id = 1, DELETE FROM students WHERE id = 5. UPDATE/DELETE वर नेहमी आधी WHERE लिहा — त्याशिवाय प्रत्येक row बदलते.
BEGIN / COMMIT / ROLLBACK. Python मध्ये: with conn: block च्या शेवटी commit करतो आणि exception बाहेर पडला तर rollback करतो — txn() मधील हीच पद्धत.
Autocommit = प्रत्येक statement स्वतःचे transaction. एका ओळीसाठी ठीक; एकमेकांशी जुळायला हव्यात अशा दोन ओळींसाठी चुकीचे.
Durability ची किंमत म्हणजे प्रत्येक commit ला एक disk sync; म्हणूनच 1,000 inserts असलेले एक transaction 1,000 transactions पेक्षा कितीतरी जलद असते.
Constraint failures write च्या वेळीच होतात (IntegrityError) — धडा 02 मधील schema transaction च्या आत लागू होतो, म्हणून एक नाकारलेली ओळ संपूर्ण pencil मागे घेते.

🤔 का

कारण पैसे, प्रवेश, साठा आणि bookings हे सगळे "एकमेकांशी जुळायला हव्यात अशा दोन ओळी" आहेत, आणि जगातले सर्वात महागडे bugs म्हणजे अर्धवट transactions. खोडरबर हाच "काहीतरी चुकले" आणि "काहीतरी चुकले आणि आता data खोटे बोलतो" यांतील फरक आहे.

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

db/demo.py मधील txn(): दोन UPDATE असलेला एक with c: block, ज्यातला दुसरा अस्तित्वात नसलेल्या वर्गाकडे बोट दाखवतो. दोन्ही बदल गायब होताना पाहा.

🧪 करून पाहा

python3 db/demo.py txn
python3 - <<'EOF'
import sqlite3; c = sqlite3.connect("db/school.db"); c.execute("PRAGMA foreign_keys = ON")
with c:                                                       # one transaction: enrol + first grade
    cur = c.execute("INSERT INTO students (name, roll_no, class_id) VALUES ('Zoya', '3B-03', 2)")
    c.execute("INSERT INTO grades (student_id, subject, term, grade) VALUES (?, 'maths', 1, 'A')", (cur.lastrowid,))
print(c.execute("SELECT s.name, g.grade FROM students s JOIN grades g ON g.student_id = s.id WHERE s.name='Zoya'").fetchall())
try:
    with c: c.execute("DELETE FROM students")                 # 😱 no WHERE — but inside a transaction…
    # (it committed! there was no error.) Restore:
except Exception as e: print(e)
EOF
python3 db/demo.py >/dev/null && echo "room rebuilt"

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

txn अयशस्वी transfer च्या आधी आणि नंतर त्याच दोन rows छापतो — दोन्ही updates खोडले गेले. तुमचा प्रवेश [('Zoya', 'A')] छापतो. WHERE शिवायचा DELETE यशस्वी होतो (error नाही, म्हणून rollback नाही) — तुम्ही पुन्हा बनवेपर्यंत खोली रिकामी राहते: transaction errors पासून वाचवते, चुकांपासून नाही; त्यांच्यासाठी धडा 09 आहे.

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

अयशस्वी transaction कोणतेही अर्धे स्थलांतर मागे ठेवत नाही, यशस्वी transaction एकदम सगळे उतरवते — आणि बरोबर-पण-चुकीचे statement तरीही commit होते, म्हणूनच backups असतात.

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

WHERE शिवाय UPDATE/DELETE (आधी WHERE लिहा, मग क्रियापद)
loop मध्ये प्रत्येक transaction ला एकच statement — हजार disk syncs
user विचार करत असताना locks धरून ठेवणारी लांब transactions (धडा 07)
block च्या आत exception पकडून पुढे चालू ठेवणे — exception बाहेर पडला तरच rollback होतो

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: payment systems म्हणजे वरपासून खालपर्यंत transactions; "आम्ही दोनदा पैसे कापले" आणि "order आहे पण stock हलला नाही" ही दोन्ही हरवलेल्या BEGIN ची लक्षणे. Frameworks pencil ला @transactional मागे लपवतात — ते काय काढते ते जाणून घ्या.

⏭️ पुढे

प्रत्येक माहिती कुठे राहावी? Data modelling — एक तथ्य, एक जागा, आणि हा नियम मुद्दाम कधी मोडायचा.

git checkout lesson-05-data-modelling

✏️ Lesson 04 — Writes & transactions: the ledger in pencil

📍 You are here: Lesson 04 of 18 · Previous: lesson-03-sql-reads · Next: lesson-05-data-modelling

📦 What's in this branch

Lessons 01–03, plus changing the registers safely: INSERT, UPDATE, DELETE, and the transaction — BEGIN … COMMIT, or ROLLBACK — that makes the four promises of ACID real.

🧒 Explain like I'm 5

Moving Aishwarya from 3A to 3B is two lines in two registers: change her class pointer, and update the class counts. What if the power goes out between the two? Half a move — Aishwarya in 3B, the counts still say 3A.

So the archivist writes in pencil first ✏️. BEGIN opens the pencil. Both changes go in. If anything goes wrong — an error, a refused pointer, a crash — the archivist erases everything (ROLLBACK) and the registers look exactly as before. Only when both lines are fine does the archivist go over them in ink (COMMIT), all at once, so that even a power cut after that leaves both changes on the shelf.

That is a transaction, and its four promises spell ACID:

Atomic — all of the lines or none of them.
Consistent — every constraint still holds after the ink dries.
Isolated — other clerks do not see your pencil marks (lesson 07).
Durable — ink survives the power cut.

🗺️ Diagram

flowchart LR
    b["✏️ BEGIN<br/>UPDATE students SET class_id = 2 WHERE id = 1"]
    u2["UPDATE students SET class_id = 99 WHERE id = 4<br/>💥 FOREIGN KEY constraint failed"]
    rb["↩️ ROLLBACK<br/>both pencil marks erased — nothing changed"]
    ok["✅ COMMIT<br/>ink: all of it, at once, durable"]
    b --> u2 -.->|"1 any error"| rb
    b -.->|"2 no errors"| ok

❓ What

INSERT INTO students (name, roll_no, class_id) VALUES (…), UPDATE students SET class_id = 2 WHERE id = 1, DELETE FROM students WHERE id = 5. Always write the WHERE on UPDATE/DELETE first — without it, every row changes.
BEGIN / COMMIT / ROLLBACK. In Python: with conn: commits at the end of the block and rolls back if an exception escapes it — the pattern in txn().
Autocommit = every statement its own transaction. Fine for one line; wrong for two lines that must agree.
Durability costs a disk sync per commit; that is why one transaction with 1,000 inserts is far faster than 1,000 transactions.
Constraint failures happen at write time (IntegrityError) — the schema from lesson 02 is enforced inside the transaction, so a refused line rolls the whole pencil back.

🤔 Why

Because money, enrolment, inventory and bookings are all "two lines that must agree", and the world's most expensive bugs are half-transactions. The eraser is the difference between "something went wrong" and "something went wrong and now the data lies".

🔧 How (in this repo)

txn() in db/demo.py: a with c: block with two UPDATEs, the second of which points at a class that does not exist. Watch both changes vanish.

🧪 Try it

python3 db/demo.py txn
python3 - <<'EOF'
import sqlite3; c = sqlite3.connect("db/school.db"); c.execute("PRAGMA foreign_keys = ON")
with c:                                                       # one transaction: enrol + first grade
    cur = c.execute("INSERT INTO students (name, roll_no, class_id) VALUES ('Zoya', '3B-03', 2)")
    c.execute("INSERT INTO grades (student_id, subject, term, grade) VALUES (?, 'maths', 1, 'A')", (cur.lastrowid,))
print(c.execute("SELECT s.name, g.grade FROM students s JOIN grades g ON g.student_id = s.id WHERE s.name='Zoya'").fetchall())
try:
    with c: c.execute("DELETE FROM students")                 # 😱 no WHERE — but inside a transaction…
    # (it committed! there was no error.) Restore:
except Exception as e: print(e)
EOF
python3 db/demo.py >/dev/null && echo "room rebuilt"

✅ Verify — what you should see

txn prints the same two rows before and after the failed transfer — both updates were erased. Your enrolment prints [('Zoya', 'A')]. The DELETE without WHERE succeeds (no error, so no rollback) — the room is empty until you rebuild it: a transaction protects against errors, not against mistakes; lesson 09 is for those.

🏁 What you just proved

A failed transaction leaves no half-move behind, a successful one lands all at once — and a correct-but-wrong statement still commits, which is why backups exist.

⚠️ Common mistakes

UPDATE/DELETE without WHERE (write the WHERE first, then the verb)
one statement per transaction in a loop — a thousand disk syncs
long transactions holding locks while the user thinks (lesson 07)
catching the exception and continuing inside the block — the rollback only happens if the exception escapes

🏭 Why this matters in production: payment systems are transactions all the way down; "we double-charged" and "the order exists but the stock didn't move" are both a missing BEGIN. Frameworks hide the pencil behind @transactional — know what it draws.

⏭️ Next

Where should each fact live? Data modelling — one fact, one place, and when to break that rule on purpose.

git checkout lesson-05-data-modelling

← Previoussql reads Next →data modelling

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