SQLite Rollback

ROLLBACK cancels all changes made since the last BEGIN (or since the named savepoint) and returns the database to its previous state. It is the emergency exit of a transaction — use it whenever an operation fails or produces unexpected results.

How ROLLBACK Works Internally

SQLite uses a journal file to enable rollback:

BEFORE any change:
  1. Copy original page(s) to the journal file
  2. Apply change to database

ON ROLLBACK:
  1. Read original page(s) from journal
  2. Write them back to the database
  3. Delete the journal file

Journal file: mydb.db-journal  (appears during transactions)
It disappears after COMMIT or ROLLBACK.

Basic ROLLBACK

BEGIN;

DELETE FROM employees WHERE dept_id = 10;
UPDATE payroll SET total = total - 85000;

-- Check the result
SELECT COUNT(*) FROM employees;  -- shows 0 for dept 10
-- Oops! We deleted the wrong department

ROLLBACK;

-- Verify restoration
SELECT COUNT(*) FROM employees WHERE dept_id = 10;
-- Returns original count — all rows restored

ROLLBACK to a SAVEPOINT

BEGIN;

UPDATE inventory SET price = price * 1.10;   -- 10% increase all
SAVEPOINT before_bags;

UPDATE inventory SET price = price * 1.20    -- extra 20% for Bags
WHERE category = 'Bags';

-- Review Bags prices — they seem too high
SELECT name, price FROM inventory WHERE category = 'Bags';

-- Cancel only the Bags increase
ROLLBACK TO before_bags;

-- The 10% increase for ALL categories is still active
-- Only the extra Bags increase was undone

COMMIT;

ROLLBACK vs COMMIT — Decision Flow

BEGIN
  │
  ├── Run SQL statements
  │
  ├── Check results
  │       │
  │    GOOD?─────YES──────► COMMIT  (save permanently)
  │       │
  │       NO
  │       │
  └───────────────────────► ROLLBACK (undo everything)

Automatic Rollback on Crash

If SQLite crashes or the process is killed mid-transaction, the journal file survives on disk. When SQLite opens the database next time, it detects the journal, reads the original pages from it, and automatically rolls back the incomplete transaction.

CRASH RECOVERY:
────────────────────────────────────────────────────────
Application crashes mid-transaction
  ↓
mydb.db-journal file remains on disk
  ↓
Next time SQLite opens mydb.db:
  → Detects journal file
  → Reads original pages
  → Rolls back incomplete transaction
  → Deletes journal
  ↓
Database is back to last committed state ✓

Journal Modes

PRAGMA journal_mode;   -- check current mode
PRAGMA journal_mode = WAL;  -- switch to WAL mode

DELETE (default)
  - Journal file written before changes
  - Journal deleted on commit/rollback

WAL (Write-Ahead Log)
  - Changes written to WAL file first
  - WAL periodically merged into main DB
  - Better for concurrent readers
  - Recommended for most applications

Rollback in Python

import sqlite3

conn = sqlite3.connect("school.db")

try:
    conn.execute("BEGIN")
    conn.execute("DELETE FROM students WHERE grade = 'F'")
    conn.execute("UPDATE summary SET failed = failed + 1")
    conn.execute("COMMIT")
    print("Changes saved")
except Exception as e:
    conn.execute("ROLLBACK")
    print(f"Error: {e} — changes rolled back")
finally:
    conn.close()

What ROLLBACK Does and Does Not Undo

ROLLBACK UNDOES:               ROLLBACK DOES NOT UNDO:
──────────────────────────     ─────────────────────────────────
INSERT statements              DDL already committed:
UPDATE statements                CREATE TABLE (already committed)
DELETE statements                DROP TABLE (already committed)
                               Changes in separate connections
                               Changes already COMMITted

Key Takeaways

• ROLLBACK cancels all uncommitted changes since BEGIN
• ROLLBACK TO savepoint_name undoes only changes since that savepoint
• SQLite automatically rolls back incomplete transactions on crash using the journal file
• WAL mode improves concurrent read performance while still supporting rollback
• ROLLBACK only undoes changes within the current uncommitted transaction