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.

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