How do SQLite savepoints support partial rollback inside a transaction?

A savepoint marks a position you can return to. If you make several changes, then decide that only the latest section should be undone, use ROLLBACK TO savepoint_name. SQLite restores the state to just after that named mark. The surrounding transaction remains in progress, so earlier work is not discarded. The savepoint itself remains available, and savepoints created after it are canceled. [S1]

Think of it as a bookmark in a draft: rolling back to it removes the edits after the bookmark, not the whole draft. This is useful when one step is optional or may need to be retried, while earlier steps should stay intact.

Assume a disposable database has an inventory table and the example updates rows with the shown SKUs. The table and rows are illustrative placeholders; adapt them to your own schema. The inner savepoint isolates one update:

SAVEPOINT import_work;

UPDATE inventory
SET quantity = quantity + 5
WHERE sku = 'A1';

SAVEPOINT one_item;

UPDATE inventory
SET quantity = quantity + 2
WHERE sku = 'B2';

ROLLBACK TO one_item;
-- The B2 update is undone; the A1 update remains.
-- one_item still exists, so release it explicitly:
RELEASE one_item;

RELEASE import_work;
-- Because this is the outermost savepoint, this commits.

The key detail is that ROLLBACK TO one_item returns to the state just after one_item was created. It does not remove one_item; the later RELEASE one_item removes that marker. [S1] The final release is included to show the outermost case, so run this example only where committing the illustrated change is acceptable.

Neither description is right for every case. RELEASE removes the matching savepoint and any savepoints nested inside it; it does not undo their changes. Releasing an inner savepoint does not itself commit changes to the database file. Releasing the outermost savepoint does commit. [S1]

So, in the example, releasing one_item removes the inner marker, while the earlier A1 change is still part of the larger unit of work. Releasing import_work is different because it is outermost. If you are unsure which savepoint is outermost in your code, trace where each savepoint is created and released before running a committing path.

Write down the intended boundary before adding commands: which changes must survive a partial rollback, and which later changes should be discarded? Then give each savepoint a distinct, meaningful name and keep its rollback and release paths easy to follow.

A practical control-flow sketch is: create the outer mark; do work that should be retained; create an inner mark; do optional work; if that optional work should be undone, roll back to the inner mark and then release it; finally decide whether to release the outermost mark. The rollback target and the outermost release have different effects, so don’t treat them as interchangeable. [S1]

Try the intended paths with disposable data and inspect the resulting rows before applying the pattern to important data. Savepoint behavior does not determine what your application should retain or whether a particular update is correct; those choices depend on your schema and task.