lesson 62 Database engineering · advanced

Transactions & savepoint recovery

Commit changes together or recover with savepoints.

the idea

A transaction groups changes so they succeed or fail together. BEGIN starts it, COMMIT keeps the changes and ROLLBACK undoes everything since BEGIN. A SAVEPOINT marks a point inside the transaction; ROLLBACK TO that savepoint undoes only the later changes.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Roll back an entire transaction. BEGIN adds 100 cents to account 1, then ROLLBACK throws that change away—you should see the original opening_balance_cents again.the pattern

   example.sql Run

   sql›

   BEGIN;
   UPDATE accounts
   SET opening_balance_cents = opening_balance_cents + 100
   WHERE account_id = 1;
   ROLLBACK;
   SELECT account_id, opening_balance_cents
   FROM accounts
   WHERE account_id = 1;

2. 2

   Undo only after a savepoint. The first UPDATE on account 3 stays; ROLLBACK TO the savepoint drops the zero-out, and COMMIT keeps the earlier change.

   step-2.sql Run

   sql›

   BEGIN;
   UPDATE accounts
   SET opening_balance_cents = opening_balance_cents + 1
   WHERE account_id = 3;
   SAVEPOINT s;
   UPDATE accounts
   SET opening_balance_cents = 0
   WHERE account_id = 3;
   ROLLBACK TO s;
   COMMIT;
   SELECT account_id, opening_balance_cents
   FROM accounts
   WHERE account_id = 3;

common mistake

ROLLBACK TO a savepoint does not end the transaction; you still need COMMIT to keep the earlier changes.