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.