Theory
The power cut between two UPDATEs
ResultDesk gets its first scary job: the exam cell rechecked paper 7, and Riya's DBMS score must move from 78 to 88, while the class-total table must go up by 10 to match.
Two UPDATE statements. You run the first. The lab's power dies.
The database now says Riya has 88, but the total still counts her old 78. The two numbers disagree, and nobody remembers which half ran. Multiply this by a thousand daily updates in a bank and you see why SQL refuses to leave this to luck.
Theory
The pencil-first rule
A careful clerk corrects a paper register in pencil first: change both entries, check they agree, and only then go over them in pen.
If anything looks wrong midway, erase the pencil: the register never officially changed.
A transaction is the pencil phase. COMMIT is the pen. ROLLBACK is the eraser. The register (your database) only ever shows finished, consistent states.
Theory
The three keywords, formally
A transaction is a sequence of SQL operations executed as one logical unit of work: either all of it happens, or none of it does.
BEGIN TRANSACTION;opens the unit (pencil out).COMMIT;makes every change since BEGIN permanent, together.ROLLBACK;undoes every change since BEGIN, as if it never ran.
The all-or-nothing property has a name worth marks: atomicity, the A of ACID (Atomicity, Consistency, Isolation, Durability), and SQLite guarantees all four.
Practical
The recheck, done safely
BEGIN TRANSACTION;
UPDATE marks
SET score = 88
WHERE roll = 101 AND subject = 'DBMS';
UPDATE class_totals
SET total = total + 10
WHERE subject = 'DBMS';
-- both worked? make them permanent TOGETHER:
COMMIT;
-- had anything gone wrong instead:
-- ROLLBACK; and the database is exactly as before BEGIN
This example runs in Gri-Learn on the web, where you can edit it and see the output.
Think first
Play the power cut
Same two UPDATEs, wrapped in BEGIN ... COMMIT. The power dies AFTER the first UPDATE but BEFORE COMMIT. When the machine restarts and SQLite reopens college.db, what does Riya's score show, and why?
Show the answer
78, the old value. The transaction never reached COMMIT, so on restart SQLite rolls the incomplete work back: the pencil marks are erased automatically. The database shows the last consistent state, both numbers agreeing. That is atomicity doing its job precisely when things go wrong, which is the whole point: transactions are insurance you buy BEFORE the accident.
Quiz
A clerk runs BEGIN; UPDATE ...; COMMIT; and then, realizing a mistake, runs ROLLBACK;. What state is the database in?
- The UPDATE stays: ROLLBACK cannot undo a committed transaction
- The UPDATE is undone: ROLLBACK always reverses the last transaction
- Error: ROLLBACK cannot be typed after COMMIT
- Half the UPDATE is undone
Show the answer
The UPDATE stays: ROLLBACK cannot undo a committed transaction
COMMIT is the pen: once written, the transaction is permanent (the D of ACID, durability). A later ROLLBACK has no open transaction to cancel, so it does nothing to the committed change (at most a harmless warning). Option B is the misconception this question exists to catch: ROLLBACK reaches back only to the BEGIN of the CURRENT, still-open transaction. Fixing a committed mistake needs a NEW correcting transaction.
Watch out
The auto-commit surprise
Without BEGIN, SQLite runs each statement in its own tiny auto-commit transaction: fine for single statements, useless for linked pairs like the recheck.
And the reverse trap arrives in Unit 3: Python's sqlite3 does NOT auto-commit your changes. Run INSERTs from Python, forget conn.commit(), close the program, and every row silently vanishes. Students lose real project data to this every year; you have been warned early.
Theory
Where you already trust transactions
Every UPI payment is a transaction: debit one account, credit another, both or neither. Booking a train seat: reserve the seat, take the money, together. When this subject's Python unit updates marks from a CSV, you will wrap the whole import in one transaction, so a bad row aborts the lot instead of importing half a class.
Summary
Key takeaways
- A transaction = several statements executed as one all-or-nothing unit (atomicity).
- BEGIN TRANSACTION opens it; COMMIT makes all changes permanent; ROLLBACK undoes them all.
- A crash before COMMIT auto-rolls back to the last consistent state.
- ROLLBACK cannot undo a committed transaction: committed = permanent (durability).
- ACID: Atomicity, Consistency, Isolation, Durability; SQLite is fully ACID.
- Python's sqlite3 needs an explicit conn.commit(), the Unit 3 trap planted here.
- Memory hook: pencil, pen, eraser.