SAVEPOINT — Complete Guide
SAVEPOINT — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of MySQL Tutorial on Toolliyo Academy.
On this page
MySQL Tutorial · Lesson 47 of 100
SAVEPOINT
Basics ✓ → Advanced
Advanced · 2 — Production · ~6 min · MySQL — Transactions & Concurrency
What is this?
SAVEPOINT names a point inside a transaction to ROLLBACK TO later without undoing the whole transaction. Useful for try/fallback steps within one txn.
Why should you care?
Bulk import may fail on row 900 — rollback to checkpoint and skip bad batch instead of losing 899 good rows.
See it live — copy this example
Run in MySQL Workbench or the mysql CLI.
START TRANSACTION;
INSERT INTO customers (full_name, city) VALUES ('Batch A', 'Delhi');
SAVEPOINT sp1;
INSERT INTO customers (full_name, city) VALUES ('Bad', NULL); -- fails if NOT NULL
ROLLBACK TO sp1;
INSERT INTO customers (full_name, city) VALUES ('Batch B', 'Mumbai');
COMMIT;What happened?
- First insert stays.
- Failed insert triggers ROLLBACK TO sp1 — removes attempts after sp1 only.
- Batch B still commits with Batch A in one transaction.
Practice next
- Run script with NOT NULL on city.
- Verify Batch A and B exist; Bad absent.
- Try RELEASE SAVEPOINT sp1; (optional cleanup).
- Nested SAVEPOINT sp2 after more inserts.
- ROLLBACK entire txn — all savepoints gone.
Remember
SAVEPOINT marks partial rollback target. ROLLBACK TO sp1 keeps work before sp1. COMMIT persists all non-rolled partial work.
DataFlow bulk customer import
ETL rolls back to savepoint per CSV chunk on validation error.
Outcome: Good chunks commit; bad chunk retried separately.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
