Transactions

Some changes belong together. A new order is a row in orders and a row in order_items for each of its games. What if the order gets in, and then something goes wrong before its items do? A game's ID is wrong, the app crashes, the server loses power. The shop then has an order with nothing in it. Changes like that must happen all or nothing.

That's what a transaction is for. BEGIN starts it, and the changes after it are part of it. COMMIT ends it and saves all its changes at once. Max Baker's order of Goal Rush, made safely:

BEGIN;

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (14, '2026-01-08', 'paid');

INSERT INTO order_items (order_id, game_id, quantity, price)
VALUES (31, 8, 1, 50);

COMMIT;

Until the COMMIT, nobody else sees the changes: other users of the database see the shop as it was before BEGIN. After it, they see all the changes at once, never just half of them. And when something goes wrong in the middle, the app ends the transaction with ROLLBACK instead, which undoes every change since BEGIN, as if it had never happened. The half-made order disappears.

Inside a transaction, you see your own changes, so you can also use one to try something out, look at the result and take it back:

BEGIN;

DELETE FROM reviews;

SELECT COUNT(*) AS reviews
FROM reviews;

ROLLBACK;

SELECT COUNT(*) AS reviews
FROM reviews;

The first query finds no reviews, the second one all 25 of them again: ROLLBACK has brought them back.

Without BEGIN, each statement is a small transaction of its own. That's why an UPDATE that fails in the middle changes nothing, as you saw with the prices. A transaction does the same for several statements together. (In other databases, you may also see START TRANSACTION instead of BEGIN.)

In this course, every Run starts with a fresh database, so you won't see the difference COMMIT makes, but in a real database, it's what makes the changes last. Typical mistake: forgetting COMMIT. Your own queries see the changes, so everything looks fine, but when the app disconnects, the database rolls the unfinished transaction back and the changes are lost. Here, you'll notice it at the next BEGIN: one transaction can't start inside another, so it ends with cannot start a transaction within a transaction. And a COMMIT without a BEGIN is an error too: cannot commit - no transaction is active.