Savepoints within transactions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a savepoint in a database transaction?

A savepoint is a marker within a transaction that allows you to roll back part of the transaction without undoing the entire transaction.

beginner

How do you create a savepoint in SQL?

You create a savepoint using the SQL command: SAVEPOINT savepoint_name;

intermediate

What happens when you roll back to a savepoint?

Rolling back to a savepoint undoes all changes made after that savepoint, but keeps changes made before it within the same transaction.

intermediate

Can you release a savepoint? If yes, how?

Yes, you can release a savepoint to free resources using: RELEASE SAVEPOINT savepoint_name;. After releasing, you cannot roll back to it.

beginner

Why are savepoints useful in transactions?

Savepoints let you handle errors or partial failures inside a transaction by rolling back only part of the work, not the entire transaction.

Which SQL command creates a savepoint named 'sp1'?

A ROLLBACK TO sp1;

B RELEASE SAVEPOINT sp1;

C SAVEPOINT sp1;

D COMMIT sp1;

The command to create a savepoint is SAVEPOINT followed by the savepoint name.

What does the command ROLLBACK TO SAVEPOINT sp1; do?

A Rolls back changes made after savepoint 'sp1'

B Rolls back the entire transaction

C Commits changes made after savepoint 'sp1'

D Deletes savepoint 'sp1'

ROLLBACK TO SAVEPOINT undoes changes made after the savepoint but keeps earlier changes.

After releasing a savepoint, what happens if you try to roll back to it?

A Savepoint is recreated automatically

B Rollback succeeds

C Transaction commits automatically

D Rollback fails because the savepoint no longer exists

Releasing a savepoint removes it, so you cannot roll back to it afterward.

Which of the following is NOT true about savepoints?

A They automatically commit the transaction

B They can be nested inside a transaction

C They help manage errors inside transactions

D They allow partial rollback within a transaction

Savepoints do not commit transactions; they only mark points for partial rollback.

What is the main benefit of using savepoints in transactions?

A To speed up query execution

B To allow partial undo of work without aborting the whole transaction

C To permanently save data to disk

D To create multiple transactions at once

Savepoints let you undo part of a transaction without losing all progress.

Explain what a savepoint is and how it is used within a database transaction.

Think about marking a spot inside a transaction to go back to if needed.

Describe the difference between rolling back a whole transaction and rolling back to a savepoint.

Consider what happens to changes made before and after the savepoint.

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