Savepoints in MySQL - Step-by-Step Execution

Concept Flow - Savepoints

Start Transaction

↓

Set Savepoint A

↓

Execute SQL Statements

↓

Set Savepoint B

↓

Execute More SQL Statements

↓

ROLLBACK TO Savepoint A?

Yes→Undo to Savepoint A

↓

Continue

↓

COMMIT Transaction

Start a transaction, create savepoints to mark positions, optionally rollback to a savepoint to undo partial work, then commit or rollback the whole transaction.

Execution Sample

MySQL

START TRANSACTION;
INSERT INTO accounts VALUES (1, 'Alice', 100);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

This code starts a transaction, inserts a row, sets a savepoint, updates it, then rolls back to the savepoint undoing the update but keeping the insert, and finally commits.

Execution Table

Step	Action	State Change	Result
1	START TRANSACTION	Transaction begins	No data committed yet
2	INSERT INTO accounts VALUES (1, 'Alice', 100)	Row inserted	accounts table has 1 row
3	SAVEPOINT sp1	Savepoint sp1 created	Marks state after insert
4	UPDATE accounts SET balance = balance - 50 WHERE id = 1	Row updated	Alice's balance is now 50
5	ROLLBACK TO SAVEPOINT sp1	Undo changes after sp1	Update undone, balance back to 100
6	COMMIT	Transaction ends	Insert is saved, update is not

💡 Transaction committed after rollback to savepoint sp1, so only changes before sp1 remain

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
accounts table	empty	[{id:1, name:'Alice', balance:100}]	[{id:1, name:'Alice', balance:100}]	[{id:1, name:'Alice', balance:50}]	[{id:1, name:'Alice', balance:100}]	[{id:1, name:'Alice', balance:100}]

Key Moments - 3 Insights

Why does the update disappear after rolling back to savepoint sp1?

Because the rollback to sp1 undoes all changes made after sp1, including the update at step 4, as shown in execution_table row 5.

Does the insert at step 2 get undone by the rollback to sp1?

No, the insert happened before sp1 was created, so it remains after rollback, as shown in variable_tracker and execution_table.

What happens if we rollback without specifying a savepoint?

The entire transaction is undone, losing all changes since START TRANSACTION, unlike rollback to a savepoint which only undoes partial changes.

Visual Quiz - 3 Questions

Look at the execution table, what is Alice's balance after step 4?

A 100

B 50

C 0

D Rollback to sp1

💡 Hint

Check the 'State Change' and 'Result' columns at step 4 in the execution_table.

At which step does the rollback undo the update?

A Step 5

B Step 3

C Step 4

D Step 6

💡 Hint

Look for the 'ROLLBACK TO SAVEPOINT sp1' action in the execution_table.

If we remove the rollback to savepoint, what would be the final balance of Alice after commit?

A 0

B 100

C 50

D No change

💡 Hint

Without rollback, the update at step 4 remains, see variable_tracker after step 4.

Concept Snapshot

Savepoints let you mark points inside a transaction.
You can rollback to a savepoint to undo partial changes.
Use SAVEPOINT name; to create one.
Use ROLLBACK TO SAVEPOINT name; to undo to it.
COMMIT saves all changes after rollback.
Useful for complex transactions with partial undo.

Full Transcript

Savepoints in MySQL allow you to mark specific points inside a transaction. You start a transaction with START TRANSACTION, then create savepoints using SAVEPOINT name. You can execute SQL statements after each savepoint. If you want to undo some changes but keep others, you use ROLLBACK TO SAVEPOINT name, which undoes all changes made after that savepoint. Finally, you use COMMIT to save the remaining changes. This lets you control partial rollbacks inside a transaction instead of undoing everything. In the example, after inserting a row and updating it, rolling back to the first savepoint undoes the update but keeps the insert. Then committing saves the insert permanently.