Savepoints in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output after rolling back to a savepoint

Consider the following MySQL transaction:

START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
SELECT balance FROM accounts WHERE id = 1;

What will be the output of the SELECT query?

MySQL

START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
SELECT balance FROM accounts WHERE id = 1;

A [{"balance": 1000}]

B [{"balance": 800}]

C Empty result set

D Syntax error

💡 Hint

Rolling back to a savepoint undoes changes after that savepoint.

The UPDATE reduces balance to 800, but rolling back to savepoint 'sp1' undoes that update, so balance remains 1000.

🧠 Conceptual

intermediate

Purpose of savepoints in transactions

What is the main purpose of using savepoints within a database transaction?

A To permanently save data to the database immediately

B To speed up query execution by caching results

C To allow partial rollback of a transaction without aborting the entire transaction

D To create a backup copy of the database

💡 Hint

Think about what rollback to a savepoint does.

Savepoints let you undo part of a transaction without rolling back everything, so you can fix mistakes without losing all progress.

📝 Syntax

advanced

Identify the syntax error in savepoint usage

Which of the following MySQL statements contains a syntax error related to savepoints?

A ROLLBACK SAVEPOINT sp1;

B ROLLBACK TO SAVEPOINT sp1;

C RELEASE SAVEPOINT sp1;

D SAVEPOINT sp1;

💡 Hint

Check the correct syntax for rolling back to a savepoint.

The correct syntax is 'ROLLBACK TO SAVEPOINT sp1;'. 'ROLLBACK SAVEPOINT sp1;' is invalid and causes a syntax error.

🔧 Debug

advanced

Why does releasing a savepoint cause an error?

Given the following sequence:

START TRANSACTION;
SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

Why does the last statement cause an error?

MySQL

START TRANSACTION;
SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;

A Because rollback to savepoint is not allowed inside transactions

B Because the savepoint 'sp1' no longer exists after being released

C Because savepoints cannot be released once created

D Because the transaction was already committed

💡 Hint

What does releasing a savepoint do?

Releasing a savepoint removes it. Trying to rollback to a released savepoint causes an error because it does not exist anymore.

❓ optimization

expert

Optimizing transaction control with savepoints

You have a long transaction with multiple steps. You want to undo only the last step if it fails, without aborting the entire transaction. Which approach is best?

A Avoid using transactions and rely on auto-commit

B Commit the transaction before the last step and start a new transaction

C Use multiple separate transactions for each step

D Use a savepoint before the last step and rollback to it if the step fails

💡 Hint

Think about partial rollback within a transaction.

Savepoints allow you to rollback only part of a transaction, so you can undo the last step without losing earlier changes.

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