lesson 62 Database engineering · advanced

Transactions & savepoint recovery

Commit changes together or recover with savepoints.

the idea

A transaction groups changes. COMMIT keeps them in the current database; ROLLBACK undoes them. SAVEPOINT lets you undo a portion. In this app even committed changes vanish after the run because the entire database is disposable.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

BEGIN;
UPDATE lab_items
SET price=price+10;
ROLLBACK;
SELECT id,price
FROM lab_items;

Watch for: A successful statement is not the same as a committed transaction.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Note prices before a transaction. A transaction groups changes. ROLLBACK undoes them; COMMIT would keep them until this disposable database ends.

   step-1.sql Run

   sql›

   SELECT id, price
   FROM lab_items
   ORDER BY id;

2. 2

   Roll back a grouped change. BEGIN starts a transaction. After UPDATE, ROLLBACK restores the prior prices visible in the final SELECT.

   step-2.sql Run

   sql›

   BEGIN;
   UPDATE lab_items
   SET price=price+10;
   ROLLBACK;
   SELECT id,price
   FROM lab_items;