A DBA is running a long data-correction transaction in several steps. After step 3 succeeds, step 4 might need to be undone if its row counts look wrong, but the work from steps 1 through 3 must be kept. Which approach does this within the same transaction?
Select an answer to reveal the explanation.
Short Explanation
Think of a savepoint as a bookmark in your transaction. If step 4 goes sideways, you flip back to the bookmark instead of tearing up the whole book. The trap is plain ROLLBACK, which doesn't undo one step; it throws away everything since BEGIN.
Full Explanation
A savepoint marks a point inside an open transaction. ROLLBACK TO SAVEPOINT undoes every change made after that marker while keeping the transaction open and preserving the work done before it. Placing a savepoint after step 3 therefore lets the DBA run step 4, check its row counts, and either continue or roll back only step 4, then still COMMIT steps 1 through 3 together. A plain ROLLBACK is not a single-statement undo; it ends the transaction and reverses every change since it began, so steps 1 through 3 would be lost as well. Issuing another BEGIN does not create an independently reversible inner transaction in standard SQL; depending on the engine it raises an error, is ignored, or merely increments a counter, and a later ROLLBACK still affects the outer work. Setting the isolation level controls what concurrent sessions can see and how locks behave; it provides no mechanism for undoing part of a transaction, and in many engines it must be set before the transaction's first statement anyway. Exam caveat: savepoint syntax is standard, but whether DDL inside the transaction can be rolled back varies by engine. Operational check: in a test copy, set the savepoint, run step 4, roll back to the savepoint, and confirm the step 1 to 3 changes remain visible while step 4's changes are gone.