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.

Practice

1. What is the main purpose of a SAVEPOINT in MySQL transactions?

easy

A. To start a new transaction automatically

B. To permanently save changes to the database

C. To mark a point in a transaction to which you can roll back later

D. To commit all changes immediately

Solution

1. Step 1: Understand what a savepoint does

   A savepoint marks a specific point inside a transaction to which you can roll back without affecting earlier changes.

2. Step 2: Differentiate from commit and start

   Unlike commit, which saves all changes permanently, savepoints allow partial rollback. It does not start a new transaction.

3. Final Answer:

   To mark a point in a transaction to which you can roll back later -> Option C

4. Quick Check:

   Savepoint = partial rollback marker [OK]

Hint: Savepoints let you undo part of a transaction, not all [OK]

Common Mistakes:

• Confusing savepoint with commit
• Thinking savepoint starts a new transaction
• Assuming savepoint permanently saves changes

2. Which of the following is the correct syntax to create a savepoint named sp1 in MySQL?

easy

A. SAVEPOINT sp1;

B. CREATE SAVEPOINT sp1;

C. SET SAVEPOINT sp1;

D. BEGIN SAVEPOINT sp1;

Solution

1. Step 1: Recall MySQL savepoint syntax

   The correct syntax to create a savepoint is simply SAVEPOINT savepoint_name;.

2. Step 2: Check other options for correctness

   Options B, C, and D use incorrect keywords like CREATE, SET, or BEGIN which are not used for savepoints.

3. Final Answer:

   SAVEPOINT sp1; -> Option A

4. Quick Check:

   Savepoint syntax = SAVEPOINT name; [OK]

Hint: Use just SAVEPOINT plus name to create one [OK]

Common Mistakes:

• Adding CREATE or SET before SAVEPOINT
• Using BEGIN with SAVEPOINT
• Missing semicolon at end

3. Consider this transaction sequence:

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

What will be the final balance for account with id=1?

medium

A. 100

B. 200

C. 0

D. NULL

Solution

1. Step 1: Follow the transaction steps

   First, a row with balance 100 is inserted. Then a savepoint sp1 is created. Next, balance is updated to 200.

2. Step 2: Rollback to savepoint sp1

   Rolling back to sp1 undoes the update but keeps the insert. So balance returns to 100.

3. Final Answer:

   100 -> Option A

4. Quick Check:

   Rollback to savepoint undoes changes after it [OK]

Hint: Rollback to savepoint undoes later changes only [OK]

Common Mistakes:

• Thinking rollback undoes entire transaction
• Assuming balance stays 200 after rollback
• Confusing commit effects

4. What is wrong with this MySQL transaction code?

START TRANSACTION;
SAVEPOINT sp1;
ROLLBACK TO sp2;
COMMIT;

medium

A. You must commit before creating a savepoint

B. ROLLBACK TO requires a semicolon after savepoint name

C. SAVEPOINT must come after ROLLBACK

D. You cannot rollback to a savepoint that was not created

Solution

1. Step 1: Check savepoint creation

   The code creates savepoint sp1 but tries to rollback to sp2 which does not exist.

2. Step 2: Understand rollback to savepoint rules

   You can only rollback to savepoints that were previously created in the current transaction.

3. Final Answer:

   You cannot rollback to a savepoint that was not created -> Option D

4. Quick Check:

   Rollback to unknown savepoint = error [OK]

Hint: Rollback only to existing savepoints in current transaction [OK]

Common Mistakes:

• Trying to rollback to a non-existent savepoint
• Misordering savepoint and rollback commands
• Assuming semicolon placement causes error

5. You want to update multiple rows in a transaction but be able to undo only the last two updates if needed. Which sequence correctly uses savepoints to achieve this?

hard

A. START TRANSACTION; SAVEPOINT sp1; UPDATE row1; SAVEPOINT sp2; UPDATE row2; UPDATE row3; ROLLBACK TO sp2; COMMIT;

B. START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT;

C. START TRANSACTION; UPDATE row1; UPDATE row2; SAVEPOINT sp1; UPDATE row3; ROLLBACK TO sp1; COMMIT;

D. START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT;

Solution

1. Step 1: Understand the goal

   You want to undo only the last two updates, meaning updates to row2 and row3, but keep update to row1.

2. Step 2: Analyze each option

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT;: START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp2; COMMIT; sets sp1 after row1 update, sp2 after row2 update, then updates row3. Rolling back to sp2 undoes only the update to row3, keeping updates to row1 and row2.

3. Step 3: Re-examine options carefully

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT; rolls back to sp1, undoing updates to row2 and row3, which matches the goal of undoing last two updates.

4. Final Answer:

   START TRANSACTION; UPDATE row1; SAVEPOINT sp1; UPDATE row2; SAVEPOINT sp2; UPDATE row3; ROLLBACK TO sp1; COMMIT; -> Option B

5. Quick Check:

   Rollback to earlier savepoint undoes all later changes [OK]

Hint: Rollback to savepoint before last two updates to undo them [OK]

Common Mistakes:

• Rolling back to wrong savepoint
• Not placing savepoints after each update
• Confusing which updates get undone