BEGIN, COMMIT & ROLLBACK

Treat related changes as a transaction

Lesson 19 of 20 · 15 min · Module 6: Trustworthy database features

Related database changes should succeed or fail as a unit. A transaction creates a boundary around that set of changes.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

  • Group related writes into a transaction
  • Distinguish committed and uncommitted changes

The idea

BEGIN starts a transaction, COMMIT keeps its changes, and ROLLBACK discards uncommitted changes. This exercise operates only on the fresh in-memory example database for the current run.

BEGIN;
UPDATE learners SET xp = xp + 10 WHERE id = 1;
ROLLBACK;
-- The previous update is discarded.

01State the invariant

The transfer changes ownership of 20 XP without creating or losing XP. Both updates belong to the same logical operation.

02Place the boundaries carefully

After COMMIT, the transfer is durable within this temporary database. A later rollback discards only changes made in the second active transaction.

03Verify the final state

Read back individual balances and their total. A correct total alone would not prove the XP went to the intended learner.

Your turn

In a transaction, transfer 20 XP from Jordan (id 4) to Alex (id 1), then COMMIT. In a second transaction, accidentally set Alex’s XP to 9999, then ROLLBACK that second transaction. Finally return every learner’s name and xp, sorted by name. The final XP total must remain 940.

What the checks look for

  • Keeps the committed transfer and discards the later mistaken update
  • Preserves the total XP
  • Preserves the enrollment records
Try it in the editor →

Take this with you

Transaction boundaries express which changes belong together.

Go a little further

Try a failed foreign-key write inside a temporary transaction and inspect which earlier changes remain before explicitly rolling back.

Learn SQL Foundations for free

20 short lessons with real code and instant checks.

See the whole course →