Savepoints within transactions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of nested savepoints in a transaction

Consider the following SQL transaction with savepoints:

BEGIN TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 100);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 30 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

What is the final balance of the account with id = 1 after this transaction?

A 100

B 80

C 130

D 50

💡 Hint

Rolling back to savepoint sp1 undoes changes after sp1 but keeps changes before it.

The initial insert sets balance to 100. The update subtracting 50 happens after sp1, then sp2 is set and another update adds 30. Rolling back to sp1 undoes both updates after sp1, so balance remains 100.

🧠 Conceptual

intermediate

Purpose of savepoints in transactions

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

A To create a backup copy of the database

B To permanently save data changes to the database immediately

C To speed up query execution by caching results

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

💡 Hint

Think about what happens when you want to undo some changes but keep others in the same transaction.

Savepoints let you mark points inside a transaction so you can roll back to them if needed without aborting the whole transaction.

📝 Syntax

advanced

Identify the syntax error in savepoint usage

Which of the following SQL statements will cause a syntax error when using savepoints inside a transaction?

A SAVEPOINT;

B SAVEPOINT sp1;

C RELEASE SAVEPOINT sp1;

D ROLLBACK TO sp1;

💡 Hint

Check if the savepoint statement includes all required parts.

The statement 'SAVEPOINT;' is missing the savepoint name, causing a syntax error.

🔧 Debug

advanced

Why does this rollback not undo changes?

Given this transaction:

BEGIN TRANSACTION;
INSERT INTO orders (id, amount) VALUES (1, 200);
SAVEPOINT sp1;
UPDATE orders SET amount = 300 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp2;
COMMIT;

Why does the rollback not undo the update?

A Because rollback to savepoint requires a semicolon after the savepoint name

B Because savepoint sp2 was never created, so rollback to it fails silently

C Because rollback to savepoint only works after commit

D Because rollback to savepoint undoes only inserts, not updates

💡 Hint

Check if the savepoint name used in rollback exists.

The rollback references sp2 which was never created, so it does nothing and the update remains.

❓ optimization

expert

Optimizing transaction with multiple savepoints

You have a long transaction with multiple savepoints to handle errors in different steps. Which approach optimizes performance and resource usage best?

A Use savepoints but never release them until the transaction ends

B Create savepoints before every single statement to maximize rollback options

C Use savepoints only before steps that are likely to fail, and release savepoints after they are no longer needed

D Avoid using savepoints and instead commit after each step to isolate errors

💡 Hint

Think about balancing rollback flexibility with overhead of managing savepoints.

Creating savepoints only where needed and releasing them when done reduces overhead and improves performance.