Savepoints within transactions in SQL

Introduction

Savepoints let you mark spots inside a transaction so you can go back to them if something goes wrong, without undoing the whole transaction.

When you want to try multiple steps inside a transaction but only undo some if a problem happens.

When you have a long transaction and want to fix errors without starting over.

When you want to test parts of a transaction and keep the rest intact.

When you want to improve control over error handling inside a transaction.

Syntax

SQL

SAVEPOINT savepoint_name;
-- Do some SQL commands
ROLLBACK TO savepoint_name;
-- Or commit the whole transaction later
COMMIT;

Savepoint names must be unique within the transaction.

ROLLBACK TO savepoint_name undoes commands after the savepoint but keeps earlier changes.

Examples

Starts a transaction, sets a savepoint, inserts a row, then undoes the insert by rolling back to the savepoint, and finally commits with no changes.

SQL

BEGIN;
SAVEPOINT sp1;
INSERT INTO accounts VALUES (1, 'Alice', 100);
ROLLBACK TO sp1;
COMMIT;

Updates account 1, sets a second savepoint, updates account 2, then undoes the second update only, and commits the first update.

SQL

BEGIN;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
ROLLBACK TO sp2;
COMMIT;

Sample Program

This transaction creates a temporary accounts table, inserts two rows, sets two savepoints, updates balances, rolls back the second update only, commits, and then shows the final table state.

SQL

BEGIN;
CREATE TEMP TABLE accounts(id INT, name TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Alice', 100), (2, 'Bob', 50);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
ROLLBACK TO sp2;
COMMIT;
SELECT * FROM accounts ORDER BY id;

Important Notes

Not all databases support savepoints, but many popular ones like PostgreSQL and MySQL do.

You can have multiple savepoints in one transaction to control rollback precisely.

After a ROLLBACK TO savepoint_name, you can continue the transaction or set new savepoints.

Summary

Savepoints let you mark places inside a transaction to roll back to without undoing everything.

Use SAVEPOINT to create a savepoint, ROLLBACK TO savepoint_name to undo to it, and COMMIT to finish.

They help manage errors and partial undo inside complex transactions.

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