SQL and MySQL course Β· Module 10: Transactions and advanced SQL
Transactions - all or nothing
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. What is a transaction in a database?
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.