Transactions

Lesson 20 of 21 · Part 5 · Changing data and tables · about 15 minutes

Some jobs take several changes to finish. When a customer places an order, three places in the database change: a new order in orders, what was bought in order_items, and less stock in products.

1INSERT INTO orders (id, customer_id, order_date, status)2VALUES (13, 10, '2025-10-28', 'pending');3INSERT INTO order_items (order_id, product_id, quantity, unit_price)4VALUES (13, 5, 1, 59.00);5UPDATE products SET stock = stock - 1 WHERE id = 5;6 7SELECT id, name, stock FROM products WHERE id = 5;

All three worked, and the headphones' stock went from 10 to 9. But what if only half of it works?

Failing halfway

This time the second statement has the wrong product number; there is no product 99:

1INSERT INTO orders (id, customer_id, order_date, status)2VALUES (13, 10, '2025-10-28', 'pending');3INSERT INTO order_items (order_id, product_id, quantity, unit_price)4VALUES (13, 99, 1, 59.00);5UPDATE products SET stock = stock - 1 WHERE id = 5;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`town`.`order_items`, CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

The second statement fails, and the one after it never runs. But the first one has already worked: the database now has an order 13 with nothing in it. Half-changed data like this is worse than no change at all: the customer sees an empty order, and the reports count one order too many.

What we want is for these three to all work, or none of them count. That's a transaction.

START TRANSACTION and COMMIT

START TRANSACTION begins a transaction. The changes after it are held back, and only take effect at COMMIT:

1START TRANSACTION;2INSERT INTO orders (id, customer_id, order_date, status)3VALUES (13, 10, '2025-10-28', 'pending');4INSERT INTO order_items (order_id, product_id, quantity, unit_price)5VALUES (13, 5, 1, 59.00);6UPDATE products SET stock = stock - 1 WHERE id = 5;7COMMIT;8 9SELECT id, customer_id, status FROM orders WHERE id = 13;

The result is the same as the first example. The difference shows when something goes wrong: until COMMIT, all these changes can still be undone.

ROLLBACK: undoing

ROLLBACK undoes every change in the transaction that hasn't been committed, and the data goes back to how it was before START TRANSACTION. Someone wants 20 pairs of headphones, but there are only 10:

1START TRANSACTION;2UPDATE products SET stock = stock - 20 WHERE id = 5;3SELECT name, stock FROM products WHERE id = 5;4 5ROLLBACK;6SELECT name, stock FROM products WHERE id = 5;

After the subtraction, the stock is -10, which is wrong. After ROLLBACK, it's 10 again, as if the UPDATE had never run. Press Step to watch the stock change one statement at a time.

1START TRANSACTION;2DELETE FROM order_items WHERE order_id = 4;3DELETE FROM orders WHERE id = 4;4ROLLBACK;5SELECT COUNT(*) FROM orders;

A transfer: two UPDATEs

The classic example of a transaction is a money transfer: A sends B 100, so A's balance goes down by 100 and B's goes up by 100. If only the first one works, the 100 simply vanishes. The two UPDATEs must succeed together.

The shop has jobs like this too. Order 7 hasn't shipped yet, and the customer cancels it: the order's status becomes "cancelled", and the backpack (1) and tote bags (2) go back into stock. All three together:

1START TRANSACTION;2UPDATE orders SET status = 'cancelled' WHERE id = 7;3UPDATE products SET stock = stock + 1 WHERE id = 3;4UPDATE products SET stock = stock + 2 WHERE id = 10;5COMMIT;6 7SELECT id, name, stock FROM products WHERE id IN (3, 10);

Undo when something fails

When a statement fails, the database just doesn't carry out that one; the changes made before it are not undone by themselves. So real programs (like the shop's back office) are written like this:

1. START TRANSACTION;
2. run the statements one by one;
3. if they all work, COMMIT; if any of them fails, ROLLBACK.

This page stops at an error, and won't write step 3 for you. For example, this stops at the second statement, and the changes in a transaction that never reaches COMMIT don't take effect in the end:

1START TRANSACTION;2INSERT INTO orders (id, customer_id, order_date, status)3VALUES (13, 10, '2025-10-28', 'pending');4INSERT INTO order_items (order_id, product_id, quantity, unit_price)5VALUES (13, 99, 1, 59.00);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`town`.`order_items`, CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

Without START TRANSACTION

Without a transaction, MySQL treats every statement as a little transaction of its own and commits it right away ("autocommit"). So here ROLLBACK has nothing to undo:

1UPDATE products SET price = 0 WHERE id = 5;2ROLLBACK;3 4SELECT name, price FROM products WHERE id = 5;

The price is still 0. To be able to undo, you have to START TRANSACTION first.

Note: In MySQL, statements that change a table's structure, like CREATE TABLE, ALTER TABLE and DROP TABLE, first commit any transaction in progress, so they can't be undone. Some other databases (PostgreSQL, for one) can undo them.

Practice

Write the code, then press Check. A hint costs nothing; the answer is there when you need it.

1SELECT o.id, c.name, p.name AS product, oi.quantity, p.stock2FROM orders AS o3JOIN customers AS c ON c.id = o.customer_id4JOIN order_items AS oi ON oi.order_id = o.id5JOIN products AS p ON p.id = oi.product_id6WHERE o.id = 13;

1START TRANSACTION;2UPDATE products SET price = price * 2;3 4SELECT name, price5FROM products6WHERE category = 'Stationery'7ORDER BY id;

1SELECT o.status, p.stock2FROM orders AS o3JOIN order_items AS oi ON oi.order_id = o.id4JOIN products AS p ON p.id = oi.product_id5WHERE o.id = 12;

What you learned

• A transaction ties several changes together: all of them take effect, or none do.
• START TRANSACTION begins one; COMMIT makes the changes take effect; ROLLBACK undoes every change not yet committed.
• When a statement fails, only that one is skipped; the earlier ones aren't undone by themselves, so a program must ROLLBACK on an error.
• Jobs that change several places together, like placing or cancelling an order or a money transfer, belong in a transaction.
• Without a transaction every statement commits itself, and ROLLBACK can't undo it; in MySQL, CREATE, ALTER and DROP can't be undone either.