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.