All or nothing

A transaction

A transaction bundles queries into one whole. BEGIN starts it, COMMIT saves everything in one go. Until the COMMIT nobody else sees the changes, and if something goes wrong along the way, nothing is saved.

Here Pieter orders six croissants: an order, an order line and less stock. Below you see the stock after the COMMIT.

BEGIN;
INSERT INTO orders (id, customer_id, ordered_on, pickup_on) VALUES (21, 5, '2025-12-12', '2025-12-13');
INSERT INTO order_lines (order_id, product_id, quantity) VALUES (21, 5, 6);
UPDATE products SET stock = stock - 6 WHERE id = 5;
COMMIT

Back with ROLLBACK

Change your mind along the way? Then write ROLLBACK instead of COMMIT. Everything since the BEGIN is undone, as if it never happened.

Here someone accidentally sets every price to 0, but the ROLLBACK saves the day.

BEGIN;
UPDATE products SET price = 0;
ROLLBACK

Rules on guard

Some columns have a CHECK: a rule every row must meet. In this bakery the stock may never drop below 0 and nobody can have negative loyalty points.

If a query breaks such a rule, SQLite refuses that query with an error. The queries before it in the same transaction aren't saved yet. With ROLLBACK you throw them away cleanly.

Below are the customers with few points. That's where things can go wrong quickly.

SELECT name, points FROM customers WHERE points < 60