sql · level 3 · unit 5 · lesson 1
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A makes several changes count as one.
Ava gives Leo 5 arcade tokens. That is two changes: take 5 from Ava, then add 5 to Leo. If the power cut out between them, 5 tokens would vanish. Real systems cannot allow that.
Wrap the changes in a transaction. starts it, and the changes after it are held back. then makes them all permanent together. The transaction is : it happens completely or not at all.
If anything fails before COMMIT, none of the changes stick. Banks, shops and games all move value this way.
BEGIN;
UPDATE wallets SET tokens = tokens - 5 WHERE owner = 'Ava';
UPDATE wallets SET tokens = tokens + 5 WHERE owner = 'Leo';
COMMIT;
SELECT owner, tokens FROM wallets ORDER BY owner;Other people using the database never see the halfway point, only the finished change.
guess first
Ten tokens on the move. What are the two totals?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
BEGIN;
UPDATE wallets SET tokens = tokens - 10 WHERE owner = 'Ava';
SELECT SUM(tokens) AS all_tokens FROM wallets;
UPDATE wallets SET tokens = tokens + 10 WHERE owner = 'Mia';
COMMIT;
SELECT SUM(tokens) AS all_tokens FROM wallets;your turn
Your turn. Sell Mia a yo-yo.
A yo-yo costs 10 tokens. Taking Mia's tokens and taking a yo-yo off the shelf must happen together. Fill the blanks to wrap both changes in one transaction.
result
Your answer appears here as a table.
fix it
Sam's tokens never arrive. Fix it!
result
Your answer appears here as a table.
- Read the error. How many transactions does this code try to start?
- Transactions do not nest. One
BEGINcovers every change until theCOMMIT. - Delete the BEGIN on line 3, then press run.
nailed it
your changes are all or nothing now. :D
BEGINstarts a transaction andCOMMITmakes it permanent.- A transaction is atomic: all of it happens or none does.
- Transactions do not nest: one
BEGIN, oneCOMMIT.
new words in your collection
- transaction
- BEGIN
- COMMIT
- atomic
This lesson isn’t saved. A free account remembers every lesson you finish.