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

Need a 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

Need a 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

Need a 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

Need a hint?

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