Description

Login
Back

Intermediate · 18 min

Transactions and rollback

Group changes and undo a transaction when it should not be kept.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Roll back a change

BEGIN opens a transaction and ROLLBACK discards its uncommitted changes. Real applications use transactions for related writes that must succeed together. A statement error does not always roll back the whole transaction automatically.

BEGIN;
UPDATE sessions SET minutes = 99 WHERE id = 1;
ROLLBACK;
SELECT minutes FROM sessions WHERE id = 1;

Output

minutes
20

Commit related writes

COMMIT keeps the changes within this database. In the playground, a new run still recreates the sample data. Do not confuse committing a transaction with synchronizing data to another device or saving a draft.

BEGIN;
UPDATE sessions SET completed = 1 WHERE id = 2;
DELETE FROM sessions WHERE id = 5;
COMMIT;
SELECT COUNT(*) AS sessions, SUM(completed) AS completed FROM sessions;

Output

sessions | completed
5 | 5

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Try changing session 1 to 99 minutes, then undo the change before reading its duration.

Show solution
BEGIN;
UPDATE sessions SET minutes = 99 WHERE id = 1;
ROLLBACK;
SELECT minutes FROM sessions WHERE id = 1;

ROLLBACK restores the original 20-minute duration.

Practice