Savepoints within transactions in SQL - Mini Project: Build & Apply

Using Savepoints within Transactions

📖 Scenario: You are managing a simple bank database where you need to update account balances carefully. Sometimes, you want to save your progress during a transaction so you can undo part of it if needed without losing all changes.

🎯 Goal: Build a SQL transaction that uses savepoints to partially rollback changes if an error occurs, ensuring the database stays consistent.

📋 What You'll Learn

Create a transaction using BEGIN and COMMIT

Set a savepoint named sp1 after the first update

Perform a second update after the savepoint

Rollback to the savepoint sp1 if the second update fails

Commit the transaction at the end

💡 Why This Matters

🌍 Real World

Savepoints help manage complex database changes safely, allowing partial undo without losing all progress.

💼 Career

Database administrators and developers use savepoints to ensure data integrity during multi-step operations.

1

Start a transaction and update the first account

Write SQL code to start a transaction with BEGIN and update the balance of account number 101 to 5000 in the accounts table.

SQL

BEGIN;
-- Your code here

Hint

Use BEGIN; to start the transaction and an UPDATE statement to change the balance.

2

Set a savepoint after the first update

Add a savepoint named sp1 right after the first update statement.

SQL

BEGIN;
UPDATE accounts SET balance = 5000 WHERE account_number = 101;
-- Your code here

Hint

Use the SAVEPOINT keyword followed by the name sp1.

3

Update the second account after the savepoint

Write an UPDATE statement to set the balance of account number 102 to 3000 after the savepoint.

SQL

BEGIN;
UPDATE accounts SET balance = 5000 WHERE account_number = 101;
SAVEPOINT sp1;
-- Your code here

Hint

Use another UPDATE statement for account 102.

4

Rollback to savepoint if needed and commit

Add a rollback to the savepoint sp1 to undo the second update if needed, then commit the transaction with COMMIT.

SQL

BEGIN;
UPDATE accounts SET balance = 5000 WHERE account_number = 101;
SAVEPOINT sp1;
UPDATE accounts SET balance = 3000 WHERE account_number = 102;
-- Your code here

Hint

Use ROLLBACK TO sp1; to undo changes after the savepoint and COMMIT; to finish the transaction.

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