SQL and MySQL course Β· Module 10: Transactions and advanced SQL

Transactions - all or nothing

1 min read Β·
In this lesson3

A transaction is a group of operations that the database treats as one indivisible whole. Either all of them run, or - if something goes wrong - none of them do.

The Keeper's three spells

  • START TRANSACTION (or BEGIN) - opens a transaction. From this moment the changes are "on hold".
  • COMMIT - confirms. All changes become permanent and visible to others.
  • ROLLBACK - undoes. The database returns to the state before START TRANSACTION, as if nothing happened.

Example: a safe loan

START TRANSACTION;

INSERT INTO wypozyczenia (ksiazka_id, czytelnik_id, data_wypozyczenia)
VALUES (1, 1, '2026-06-22');

UPDATE ksiazki SET dostepna = 0 WHERE id = 1;

COMMIT;

These two operations will happen together. If the second one fails, the Keeper gives the order:

ROLLBACK;

and even the first INSERT is wiped out. The book will never hang in an in-between state.

When to use transactions?

Whenever several changes must be consistent together: a transfer (take from one account, add to another), a loan, deleting a reader together with their history. This is your shield against inconsistency.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is a transaction in a database?

  2. 2. Which command undoes all changes made during an ongoing transaction?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Horizontal ordering

    Arrange in one line the command that opens a transaction:

  • Code editor

    Complete the transaction: start it with START TRANSACTION, then INSERT into the wypozyczenia table (ksiazka_id, czytelnik_id, data_wypozyczenia) with the values (1, 1, '2026-06-22'), then UPDATE the ksiazki table set dostepna = 0 for id = 1, and finally confirm the changes with COMMIT.

  • Click in order

    Click the transaction commands in the order they are used: open, write, commit:

  • Code editor

    Write a transaction: start it with START TRANSACTION, then UPDATE the wypozyczenia table set data_zwrotu = '2026-06-22' for id = 1, then UPDATE the ksiazki table set dostepna = 1 for id = 1, and finally confirm the changes with COMMIT.

  1. QuestionWhat is a transaction
  2. QuestionUndoing changes
  3. QuestionOpening a transaction
  4. Put in orderThe START TRANSACTION command
  5. Coding taskA loan in a transaction
  6. QuestionCommitting changes
  7. Click in orderOpen, write and commit
  8. Coding taskA safe book return

Useful articles