Writes and transaction rollback

Understand the mechanism

WHERE controls the scope of UPDATE and DELETE. Preview matching rows with SELECT first. Without WHERE, the operation normally affects the whole table. A primary-key predicate targets a specific row.

A transaction groups operations into a commit unit. BEGIN starts it, COMMIT retains it and ROLLBACK undoes uncommitted work. Transactions cannot undo arbitrary previously committed history; backups serve a separate purpose.

This example cancels one edit before committing the intended update. changes() describes recent statement changes, not a complete system audit log.

Runnable example

This course uses SQLite 3. Open a fresh in-memory database, for example with sqlite3 :memory:, and run the complete script. Each lesson supplies its own schema and sample data, independent of the website database.

CREATE TABLE progress(id INTEGER PRIMARY KEY, done INTEGER NOT NULL);
INSERT INTO progress VALUES(1,0),(2,0);
BEGIN;
UPDATE progress SET done=99 WHERE id=1;
ROLLBACK;
BEGIN;
UPDATE progress SET done=1 WHERE id=1;
COMMIT;
SELECT id, done FROM progress ORDER BY id;

Trace and verify

The 99 is rolled back. The final values are 1 for id1 and 0 for id2. An intermediate transaction value is not the final committed state.

Common mistakes and boundaries

An application must roll back explicitly on failure rather than assume every SQL error cancels the whole transaction. This script does not model concurrent writers.

Practice challenge

Delete id2 inside a new transaction, roll it back and verify the row remains.

Reference solution and reasoning

Run BEGIN; DELETE FROM progress WHERE id=2; ROLLBACK; then SELECT. Both rows remain because the deletion was not committed.

Further reading

• SQLite SQL reference