SQL Transactions
Group related statements so they commit together or roll back together.
What & Why
A transaction is an all-or-nothing unit of work bounded by BEGIN and COMMIT or ROLLBACK. Transactions protect consistency when several dependent changes must succeed as one business action.
See How It Works
BUSINESS QUESTION
Illustrate changing one campaign status inside a transaction after reviewing the exact target row.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
BEGIN;
UPDATE marketing.campaigns
SET status = 'paused'
WHERE id = 1
AND status = 'active';
COMMIT;TRANSACTION — campaign 1 status change
| step | campaign_id | status | durability |
|---|---|---|---|
| BEGIN | 1 | active | starting value |
| UPDATE | 1 | paused | pending |
| COMMIT | 1 | paused | persisted |
The finalized example updates Spring Launch (campaign ID 1), and COMMIT makes its paused status durable.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario