SQL ROLLBACK TO SAVEPOINT
SQL ROLLBACK TO SAVEPOINT helps analysts discard changes after a named savepoint while keeping earlier transaction work pending.
What & Why
The ROLLBACK TO SAVEPOINT pattern is used to discard changes after a named savepoint while keeping earlier transaction work pending. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants to validate qualified leads at a savepoint before keeping or discarding only the later work.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
-- SAVEPOINT lead_checkpoint; marks the partial rollback boundary.
-- ROLLBACK TO SAVEPOINT lead_checkpoint; discards only later work.
SELECT
id AS lead_id,
email,
qualified_at,
converted_at
FROM marketing.leads
WHERE qualified_at IS NOT NULL
ORDER BY lead_id;RESULT — qualified leads
| lead_id | qualified_at | converted_at | |
|---|---|---|---|
| 301 | ana@example.com | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 |
| 303 | chloe@example.com | 2024-02-18 14:20:00+00 | NULL |
| 304 | dev@example.com | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 |
The practice result is read-only while the lesson explains partial rollback behavior.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario