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.

Practice

1. What is the main purpose of using SAVEPOINT in a database transaction?

easy

A. To start a new transaction automatically

B. To permanently save changes to the database immediately

C. To mark a point in the transaction to which you can roll back without undoing the entire transaction

D. To lock a table for exclusive access

Solution

1. Step 1: Understand the role of SAVEPOINT

   SAVEPOINT creates a marker inside a transaction to allow partial rollback.

2. Step 2: Compare with other options

   It does not commit changes or lock tables; it only marks a rollback point.

3. Final Answer:

   To mark a point in the transaction to which you can roll back without undoing the entire transaction -> Option C

4. Quick Check:

   SAVEPOINT = partial rollback marker [OK]

Hint: SAVEPOINT marks rollback points inside transactions [OK]

Common Mistakes:

• Confusing SAVEPOINT with COMMIT
• Thinking SAVEPOINT locks tables
• Assuming SAVEPOINT starts a new transaction

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

easy

A. SET SAVEPOINT sp1;

B. SAVE sp1;

C. CREATE SAVEPOINT sp1;

D. SAVEPOINT sp1;

Solution

1. Step 1: Recall standard SQL syntax for savepoints

   The correct syntax is simply SAVEPOINT name; without extra keywords.

2. Step 2: Eliminate incorrect options

   Options A, B, and C use invalid or non-standard keywords.

3. Final Answer:

   SAVEPOINT sp1; -> Option D

4. Quick Check:

   Correct savepoint syntax = SAVEPOINT name [OK]

Hint: Use SAVEPOINT followed by name to create a savepoint [OK]

Common Mistakes:

• Adding CREATE or SET keywords incorrectly
• Using SAVE instead of SAVEPOINT
• Missing semicolon at the end

3. Consider the following SQL transaction sequence:

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

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

medium

A. 50

B. 100

C. 0

D. Rollback error

Solution

1. Step 1: Analyze the transaction steps

   Insert sets balance to 100, then savepoint sp1 is created. The update subtracts 50, making balance 50.

2. Step 2: Apply ROLLBACK TO SAVEPOINT

   Rollback to sp1 undoes the update, restoring balance to 100 before commit.

3. Final Answer:

   100 -> Option B

4. Quick Check:

   Rollback to savepoint undoes changes after it [OK]

Hint: Rollback to savepoint undoes changes after it [OK]

Common Mistakes:

• Assuming rollback undoes entire transaction
• Ignoring rollback and expecting updated balance
• Confusing rollback with commit

4. Identify the error in the following transaction code:

BEGIN TRANSACTION;
SAVEPOINT sp1;
INSERT INTO orders VALUES (101, 'Book');
ROLLBACK TO sp2;
COMMIT;

medium

A. ROLLBACK TO references a non-existent savepoint

B. Missing COMMIT before SAVEPOINT

C. SAVEPOINT must come after INSERT

D. ROLLBACK TO cannot be used inside transactions

Solution

1. Step 1: Check savepoint names

   Only sp1 is created, but rollback tries to use sp2 which does not exist.

2. Step 2: Validate transaction flow

   Rollback to a non-existent savepoint causes an error; other statements are valid.

3. Final Answer:

   ROLLBACK TO references a non-existent savepoint -> Option A

4. Quick Check:

   Rollback must use existing savepoint name [OK]

Hint: Rollback must refer to an existing savepoint [OK]

Common Mistakes:

• Using wrong savepoint name in rollback
• Thinking savepoints must follow inserts
• Believing rollback can't be inside transactions

5. You have a transaction that inserts multiple rows into a table. You want to undo only the last two inserts if an error occurs, but keep the earlier inserts. Which sequence correctly uses savepoints to achieve this?

hard

A. BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT;

B. BEGIN; SAVEPOINT sp1; INSERT row1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

C. BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

D. BEGIN; INSERT row1; INSERT row2; SAVEPOINT sp1; INSERT row3; ROLLBACK TO sp1; COMMIT;

Solution

1. Step 1: Understand the goal

   To keep row1 but undo row2 and row3, place a savepoint after row1 and rollback to it.

2. Step 2: Identify correct sequence

   BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; rolls back changes after sp1, undoing row2 and row3 while keeping row1.

3. Final Answer:

   BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; -> Option A

4. Quick Check:

   Rollback to savepoint before last two inserts undoes them [OK]

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

Common Mistakes:

• Rolling back to wrong savepoint
• Placing savepoints after all inserts
• Assuming rollback undoes all inserts