BEGIN and COMMIT
Group changes so they all happen together, or not at all.
Lesson 28 of 33 · about 14 minutes
The lesson
Emma wants to give $20 from her gift card to her friend Oliver. That's two changes:
- Take $20 off Emma's card.
- Add $20 to Oliver's card.
What if the power cuts out between step 1 and step 2? Emma has lost $20, and Oliver never got it. The money has simply vanished.
A transaction fixes this. It wraps several changes into one package that either happens completely or not at all. These are the TCL (Transaction Control Language) commands:
BEGIN;
UPDATE gift_cards SET balance = balance - 20 WHERE id = 1;
UPDATE gift_cards SET balance = balance + 20 WHERE id = 2;
COMMIT;
- BEGIN says "start a package of changes".
- COMMIT says "I'm done, make all of it permanent".
Until COMMIT, the changes are a draft. If anything goes wrong before then, the database throws the draft away, so it's never left half-done. That "all or nothing" promise is called being atomic.
This lesson's practice database has a new gift_cards table:
| gift_cards.id | customer | balance |
|---|---|---|
| 1 | Emma Johnson (customer 1) | $50.00 |
| 2 | Oliver Brown (customer 4) | $20.00 |
| 3 | Yuki Tanaka (customer 5) | $35.00 |
| 4 | Sofia Rossi (customer 7) | $10.00 |
Run SELECT * FROM gift_cards; to see it.
- A transaction groups changes so they all happen, or none do.
- BEGIN starts the transaction, and COMMIT makes every change in it permanent.
- Use one whenever several changes only make sense together, like moving money.