SAVEPOINTs

A transaction normally gives you two options: COMMIT everything or ROLLBACK everything. A savepoint adds a third option — it marks a named checkpoint in the middle of a transaction that you can roll back to without throwing away the work that happened before it. Think of it as an undo point inside a larger unit of work.

This matters whenever a transaction is made up of several independent-ish steps and you want the freedom to discard just one of them — a failed item in a batch, an optional step that turned out to be invalid — while keeping everything else intact.

Syntax

SAVEPOINT name; — creates a named checkpoint at the current point in the transaction.
ROLLBACK TO SAVEPOINT name; — undoes everything done since that savepoint was created, but keeps the transaction open and keeps everything done before the savepoint.
RELEASE SAVEPOINT name; — forgets the savepoint (you no longer need to roll back to it), without undoing anything. The transaction continues normally.

A savepoint is not a commit

Rolling back to a savepoint, releasing a savepoint, and the transaction itself all still depend on a final COMMIT or ROLLBACK to actually take effect. Savepoints only control what happens within the still-open transaction.

Worked example: a multi-step transaction

Imagine placing an order that involves three related updates. Suppose the second step — reserving inventory — fails a business rule check. Without a savepoint you would have to roll back the entire transaction, including the customer record update that was perfectly fine. With a savepoint, you can back out just the failed step:

Rolling back one step, keeping the rest

SQL

BEGIN;

UPDATE customers SET last_order_at = now() WHERE id = 42;

SAVEPOINT before_inventory;

UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 7;
-- suppose this leaves quantity negative, which violates a business rule

ROLLBACK TO SAVEPOINT before_inventory;
-- the inventory update above is undone; the customers update is NOT

INSERT INTO order_issues (product_id, reason)
VALUES (7, 'insufficient stock');

COMMIT;

After this transaction commits, customers.last_order_at was updated, the inventory change never happened, and a new row exists explaining why. All of that is one atomic unit — either the whole sequence lands, or none of it does.

Releasing a savepoint

Once you are confident you no longer need to roll back to a savepoint, you can release it. This does not undo anything — it simply removes the checkpoint so it can no longer be targeted by ROLLBACK TO SAVEPOINT:

Releasing when a step succeeds

SQL

BEGIN;

SAVEPOINT step_one;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- step succeeded, we no longer need to be able to undo just this step
RELEASE SAVEPOINT step_one;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Use case: batch processing that skips failures

A common real-world pattern is importing many rows in a single transaction, where a handful of bad rows shouldn't abort the whole batch. Application code can wrap each row in its own savepoint, and roll back only the rows that fail validation:

Skipping a bad row without losing the batch

SQL

BEGIN;

SAVEPOINT row_1;
INSERT INTO products (sku, price) VALUES ('ABC-1', 19.99);
RELEASE SAVEPOINT row_1;

SAVEPOINT row_2;
INSERT INTO products (sku, price) VALUES ('ABC-2', -5.00);
-- a CHECK constraint rejects the negative price
ROLLBACK TO SAVEPOINT row_2;
-- log the failure and move on; the transaction is still alive

SAVEPOINT row_3;
INSERT INTO products (sku, price) VALUES ('ABC-3', 29.99);
RELEASE SAVEPOINT row_3;

COMMIT;
-- rows 1 and 3 are saved, row 2 was skipped, and none of it required
-- restarting the whole import

Savepoints can be nested

You can create multiple savepoints in sequence and roll back to any of them — rolling back to an earlier savepoint also discards any savepoints created after it. This lets you model fairly deep "undo trees" within a single transaction, though in practice most applications only need one level.