Questions: Savepoints: Partial Rollback Within Transactions

Question 1

A transaction executes 10 INSERT statements, then sets a savepoint, then attempts an 11th INSERT that violates a constraint. The application issues ROLLBACK TO SAVEPOINT. What is the state of the database after this command?

• A. The first 10 inserts are committed and the 11th is discarded
• B. All 11 inserts are rolled back, returning to the state before the transaction began
• C. The first 10 inserts are preserved within the transaction (still uncommitted), the 11th is undone
• D. The 11th insert is retried automatically with the constraint relaxed

Answer: (C)

ROLLBACK TO SAVEPOINT undoes everything *after* the savepoint while preserving work done before it — but 'preserving' means it remains inside the open transaction, not committed. The first 10 inserts are intact and will be committed when the transaction issues COMMIT. Option A is the most common misconception: it conflates 'preserved by rollback-to-savepoint' with 'committed.' Nothing is persisted to disk until the final COMMIT; the savepoint only controls what portion of the transaction's in-progress work is undone.

Question 2

A batch import transaction sets a savepoint before each of 100 record inserts. Records 1–50 succeed; record 51 fails a constraint, so the application rolls back to that record's savepoint and logs the error; records 52–100 succeed. The transaction then issues COMMIT. What happens?

• A. Only records 52–100 are committed, because rolling back to a savepoint discards all prior work
• B. Records 1–50 and 52–100 are committed; record 51's failed insert is not
• C. Nothing is committed — the ROLLBACK TO SAVEPOINT invalidated the entire transaction
• D. All 100 records are committed because savepoints do not affect COMMIT behavior

Answer: (B)

Savepoints enable exactly this pattern: fine-grained error recovery within a transaction. Rolling back to a savepoint only undoes the work done after that specific savepoint — records 1–50 remain intact in the transaction. After the rollback-to-savepoint and logging, the application continues processing records 52–100, all within the same transaction. The final COMMIT writes records 1–50 and 52–100. This is the key value proposition of savepoints: handle expected individual failures without sacrificing successfully processed records.

Question 3

Rolling back to a savepoint commits most changes made before that savepoint, making them permanent in the database.

• T. True
• F. False

Answer: False

This is the most dangerous misconception about savepoints. A savepoint is a recovery marker, not a commit point. ROLLBACK TO SAVEPOINT undoes work after the savepoint but leaves work before it in an uncommitted state within the still-open transaction. Only a COMMIT can write data permanently. If the entire transaction is later rolled back — or the connection drops before COMMIT — even the work 'preserved' by the savepoint is lost. Developers who mistake savepoints for mini-commits may believe their data is safely stored when it is actually still vulnerable.

Question 4

After rolling back to a savepoint, the application can re-execute the failed operation with corrected values and continue the transaction normally.

• T. True
• F. False

Answer: True

This is precisely what makes savepoints useful. After ROLLBACK TO SAVEPOINT, the transaction is still open at the state as of that savepoint. The application can examine the error, correct the input (different values, skip the record, log and continue), and then issue new SQL statements within the same transaction. The savepoint essentially lets you say 'try this risky operation; if it fails, undo it and take an alternative path, all without abandoning everything else.' This error-recovery loop within a single transaction is impossible without savepoints.

Question 5

What is the fundamental difference between a savepoint and a COMMIT, and why does this distinction matter when designing bulk-processing transactions?

Model answer: A COMMIT permanently writes all uncommitted changes to the database and ends the transaction. A savepoint creates a named marker inside a transaction that enables partial rollback — undoing work done after that point without touching work done before it — but commits nothing. The transaction remains open and all data remains uncommitted until COMMIT. For bulk processing, this means savepoints let you skip or log bad records without losing good ones, but the entire batch remains at risk until the final COMMIT. If the process crashes after setting savepoints but before COMMIT, all work is lost.

A common design mistake is to use savepoints assuming that passing a savepoint means the preceding work is 'safe.' It is safe from partial rollback, but not from a full transaction rollback, connection failure, or crash. If durability is required for each successfully processed record, you need intermediate COMMITs (separate transactions per record or per batch), not savepoints. Savepoints optimize for the case where you want to handle individual failures gracefully within a single atomic unit — they do not substitute for the durability guarantee that only COMMIT provides.