Savepoints within transactions in SQL - Step-by-Step Execution

Concept Flow - Savepoints within transactions

Start Transaction

↓

Execute SQL Statements

↓

Create Savepoint

↓

Execute More SQL Statements

↓

Rollback to Savepoint?

Yes→Undo to Savepoint

↓

Continue

↓

Commit or Rollback Transaction

↓

End

You start a transaction, run some commands, create savepoints to mark spots, and can rollback to these savepoints without undoing everything. Finally, you commit or rollback the whole transaction.

Execution Sample

SQL

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

This transaction inserts a row, sets a savepoint, updates the balance, then rolls back to the savepoint undoing the update, and finally commits.

Execution Table

Step	Action	Effect on Data	Transaction State
1	BEGIN;	Transaction started, no data changed yet	Active
2	INSERT INTO accounts VALUES (1, 'Alice', 100);	Row added: Alice with balance 100	Active
3	SAVEPOINT sp1;	Savepoint 'sp1' created	Active
4	UPDATE accounts SET balance = balance - 50 WHERE id = 1;	Alice's balance changed from 100 to 50	Active
5	ROLLBACK TO sp1;	Undo update, balance back to 100	Active
6	COMMIT;	All changes before commit saved permanently	Committed

💡 Transaction committed, changes saved except the undone update rolled back to savepoint

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
accounts table (Alice's balance)	N/A	100	50	100	100
Transaction State	Inactive	Active	Active	Active	Committed
Savepoint sp1	None	None	Created	None	None

Key Moments - 3 Insights

Why does the balance return to 100 after rolling back to the savepoint?

Because the rollback to savepoint undoes all changes made after the savepoint was created, as shown in step 5 of the execution_table.

Does rolling back to a savepoint end the transaction?

No, the transaction remains active after rollback to savepoint, allowing further commands before commit or full rollback, as seen in the transaction state column.

What happens if we rollback the entire transaction instead of to a savepoint?

All changes made in the transaction are undone and the transaction ends without committing, unlike rollback to savepoint which only undoes partial changes.

Visual Quiz - 3 Questions

Look at the execution_table, what is Alice's balance after step 4?

A 50

B 100

C 0

D 150

💡 Hint

Check the 'Effect on Data' column at step 4 in the execution_table.

At which step does the transaction state change to 'Committed'?

A Step 3

B Step 5

C Step 6

D Step 2

💡 Hint

Look at the 'Transaction State' column in the execution_table for when it changes to 'Committed'.

If we remove the rollback to savepoint command, what would Alice's balance be after commit?

A 100

B 50

C 0

D Rollback error

💡 Hint

Refer to the variable_tracker for balance changes and consider what rollback to savepoint does.

Concept Snapshot

BEGIN starts a transaction
SAVEPOINT marks a point to rollback to
ROLLBACK TO savepoint undoes changes after that point
COMMIT saves all changes permanently
Rollback to savepoint keeps transaction active
Full rollback undoes all and ends transaction

Full Transcript

This visual execution shows how savepoints work inside a transaction. First, the transaction begins. Then, a row is inserted into the accounts table. A savepoint named sp1 is created to mark this spot. Next, the balance for Alice is updated from 100 to 50. Rolling back to the savepoint undoes this update, restoring the balance to 100. Finally, the transaction commits, saving all changes except the undone update. The transaction state changes from inactive to active at the start, and to committed at the end. Savepoints allow partial undo without ending the transaction, unlike a full rollback which cancels everything.