Building tables and changing data · Lesson 15 of 20

Transactions: all or nothing

BEGIN, COMMIT, ROLLBACK, and what ACID means.

9 minutes2 games

Imagine a student sending 100 XP to a friend: two steps, subtract from one and add to the other. If the server crashes between them, XP vanishes. A transaction groups the steps into one unit: all of them happen, or none do.

SQL

BEGIN;
UPDATE students SET xp = xp - 100 WHERE name = 'Laila';
UPDATE students SET xp = xp + 100 WHERE name = 'Omar';
COMMIT;

SELECT name, xp FROM students WHERE name IN ('Laila', 'Omar');

BEGIN starts, COMMIT makes everything permanent. If something goes wrong midway, ROLLBACK undoes everything back to before BEGIN:

SQL

BEGIN;
DELETE FROM students;              -- oops
SELECT COUNT(*) AS during FROM students;
ROLLBACK;

SELECT COUNT(*) AS after FROM students;

ACID	Meaning
Atomicity	all or nothing
Consistency	constraints hold before and after
Isolation	concurrent transactions don't see each other's half-done work
Durability	after COMMIT data survives even a power cut

What's the output?

BEGIN;
UPDATE students SET xp = 0 WHERE id = 1;
ROLLBACK;
SELECT xp FROM students WHERE id = 1;

What will this code print?

Exercise

Inside a transaction: move 100 XP from Nour (id = 4) to Hana (id = 7) and COMMIT. Then show both students' name and xp, ordered by id.

Expected result:

name | xp
Nour | 310
Hana | 160

SQL

BEGIN;

COMMIT;

Learn more from

Played 0 of 2 games

SQL