BEGIN, COMMIT, ROLLBACK

In short: BEGIN starts a transaction, COMMIT saves its changes, ROLLBACK undoes them, and SAVEPOINT marks a point that ROLLBACK TO can undo back to.

Problem

The error after which nothing is finished

The program raises dairy prices by 10% and, in the same transaction, fixes the price of bread. The second query has a mistake: the price becomes negative. Run it:

SQLdatabase: shop

BEGIN;
UPDATE products SET price = ROUND(price * 1.1, 2) WHERE category_id = 4;
UPDATE products SET price = -1 WHERE id = 1;

The second query stopped. And the first? Was the dairy price rise undone, or did it go in?

The idea

The transaction stays open

Neither. SQLite undoes only the failing query. The transaction stays open, and the dairy price rise is in it, not yet confirmed.

Try it in the same field, without “Fresh database”. Delete the text, write SELECT name, price FROM products WHERE category_id = 4; and run it: the prices are up. Then write ROLLBACK; and run the price query again.

That is why a program that works with transactions always has two paths: all is well, so COMMIT; something failed, so ROLLBACK. If it says nothing, the transaction hangs. While it hangs, in SQLite nobody else can write to the database, and in PostgreSQL and MySQL everyone who wants to change the same rows waits.

More precisely, if you are curious

If you run BEGIN again in this field without closing the old transaction, SQLite says “cannot start a transaction within a transaction”. PostgreSQL is stricter: after an error the whole transaction is broken, and until ROLLBACK every next query refuses to run.

See it

What I see inside

Inside the transaction the changes are visible before they are confirmed, but only to it:

SQLdatabase: shop

BEGIN;
UPDATE products SET price = ROUND(price * 1.1, 2) WHERE category_id = 4;
SELECT name, price FROM products WHERE category_id = 4;

Another connection to the database (another user, another program) still sees the old prices. We will see the two sessions side by side in the next lesson.

The idea

SAVEPOINT: going back to a point

Sometimes you want to give up only part of the transaction. SAVEPOINT name sets a point, and ROLLBACK TO name goes back to it. Everything before the point stays, and the transaction goes on.

A delivery: the bread has arrived (+10), the drinks have not. They are added out of habit, then taken back:

SQLdatabase: shop

BEGIN;
UPDATE products SET stock = stock + 10 WHERE category_id = 3;
SAVEPOINT drinks;
UPDATE products SET stock = stock + 10 WHERE category_id = 6;
ROLLBACK TO drinks;
COMMIT;
SELECT name, stock FROM products WHERE category_id IN (3, 6);

The bread has 10 more, the drinks are unchanged.

Exercise

Only the bread goes up

The transaction raises the prices of bread and dairy by 5%. The boss says dairy keeps the old prices. Add one line so that only the dairy price rise is undone.

SQLdatabase: shop

BEGIN;
UPDATE products SET price = ROUND(price * 1.05, 2) WHERE category_id = 3;
SAVEPOINT dairy;
UPDATE products SET price = ROUND(price * 1.05, 2) WHERE category_id = 4;
-- here: drop only the dairy change
COMMIT;
SELECT name, price FROM products WHERE category_id IN (3, 4);

Solution

BEGIN;
UPDATE products SET price = ROUND(price * 1.05, 2) WHERE category_id = 3;
SAVEPOINT dairy;
UPDATE products SET price = ROUND(price * 1.05, 2) WHERE category_id = 4;
ROLLBACK TO dairy;
COMMIT;
SELECT name, price FROM products WHERE category_id IN (3, 4);

Test

Check yourself

1. Without BEGIN, in SQLite every query…Does not runIs a separate transaction and is confirmed straight awayWaits for a COMMIT that never comes

2. In a transaction the third query fails. What does SQLite do with the first two?Undoes them by itselfConfirms themKeeps them in the open transaction and waits for COMMIT or ROLLBACK

3. What does ROLLBACK TO point_name do?Confirms everything up to that pointUndoes the changes after that point, and the transaction goes onCancels the whole transaction

Made by Ivo Gergov as part of Evo Tech's campaign for accessible programming education ·

BEGIN, COMMIT, ROLLBACK

Cookies