Transactions & ACID
When RecipeBox saves a new recipe, it doesn't just insert one row — it inserts the recipe, then several ingredient rows tied to it. If the app crashed halfway through, you'd want either all of those rows saved, or none of them — never a recipe with half its ingredients missing. That's exactly what a transaction guarantees, and it's the mechanism behind SQLite being fully ACID-compliant.
ACID, Briefly
| Property | Meaning |
|---|---|
| Atomicity | All statements in a transaction succeed together, or none of them take effect |
| Consistency | A transaction always leaves the database satisfying its constraints (foreign keys, unique indexes, etc.) |
| Isolation | Concurrent transactions don't see each other's uncommitted changes |
| Durability | Once a transaction is committed, it survives a crash or power loss |
BEGIN, COMMIT, ROLLBACK
BEGIN (or BEGIN TRANSACTION) starts
an explicit transaction. COMMIT saves every change made since
BEGIN, permanently. ROLLBACK discards every change made
since BEGIN, as if none of it ever happened.
-- Save a new recipe together with all of its ingredients, atomically
BEGIN TRANSACTION;
INSERT INTO recipes (title, category, prep_minutes, user_id)
VALUES ('Apple Pie', 'Dessert', 60, 7);
INSERT INTO ingredients (recipe_id, name, quantity)
VALUES (last_insert_rowid(), 'Apples', '6 cups');
INSERT INTO ingredients (recipe_id, name, quantity)
VALUES (last_insert_rowid(), 'Sugar', '3/4 cup');
INSERT INTO ingredients (recipe_id, name, quantity)
VALUES (last_insert_rowid(), 'Pie Crust', '2 crusts');
COMMIT;
If any statement fails partway through — say a NOT NULL constraint rejects one ingredient
row — you can undo everything since BEGIN instead of committing a half-saved recipe:
BEGIN TRANSACTION;
INSERT INTO recipes (title, category, prep_minutes, user_id)
VALUES ('Broken Recipe', 'Dessert', 45, 7);
-- Suppose this insert fails (e.g. quantity is NOT NULL but omitted here)
-- INSERT INTO ingredients (recipe_id, name) VALUES (last_insert_rowid(), 'Flour');
-- Undo everything since BEGIN -- the recipe row is never saved either
ROLLBACK;Implicit Transactions and Why Explicit Ones Matter for Performance
By default, every single SQL statement you run that isn't wrapped in an explicit
BEGIN/COMMIT runs as its own implicit transaction — SQLite automatically
begins and commits around it. That's convenient, but it has a real performance cost: each commit
involves an fsync to disk to guarantee durability, and doing that once per statement is slow when
you're inserting many rows in a loop.
-- Slow: 500 implicit transactions, 500 fsyncs
-- (running one INSERT per loop iteration with no BEGIN/COMMIT around it)
INSERT INTO ingredients (recipe_id, name, quantity) VALUES (1, 'Salt', '1 tsp');
-- ...499 more separate INSERT statements...
-- Fast: wrap all 500 inserts in one explicit transaction, one fsync
BEGIN TRANSACTION;
INSERT INTO ingredients (recipe_id, name, quantity) VALUES (1, 'Salt', '1 tsp');
-- ...499 more INSERT statements...
COMMIT;