Savepoints within transactions in SQL

Introduction

Savepoints let you mark spots inside a transaction so you can go back to them if something goes wrong, without undoing the whole transaction.

When you want to try multiple steps inside a transaction but only undo some if a problem happens.

When you have a long transaction and want to fix errors without starting over.

When you want to test parts of a transaction and keep the rest intact.

When you want to improve control over error handling inside a transaction.

Syntax

SQL

SAVEPOINT savepoint_name;
-- Do some SQL commands
ROLLBACK TO savepoint_name;
-- Or commit the whole transaction later
COMMIT;

Savepoint names must be unique within the transaction.

ROLLBACK TO savepoint_name undoes commands after the savepoint but keeps earlier changes.

Examples

Starts a transaction, sets a savepoint, inserts a row, then undoes the insert by rolling back to the savepoint, and finally commits with no changes.

SQL

BEGIN;
SAVEPOINT sp1;
INSERT INTO accounts VALUES (1, 'Alice', 100);
ROLLBACK TO sp1;
COMMIT;

Updates account 1, sets a second savepoint, updates account 2, then undoes the second update only, and commits the first update.

SQL

BEGIN;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
ROLLBACK TO sp2;
COMMIT;

Sample Program

This transaction creates a temporary accounts table, inserts two rows, sets two savepoints, updates balances, rolls back the second update only, commits, and then shows the final table state.

SQL

BEGIN;
CREATE TEMP TABLE accounts(id INT, name TEXT, balance INT);
INSERT INTO accounts VALUES (1, 'Alice', 100), (2, 'Bob', 50);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
SAVEPOINT sp2;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
ROLLBACK TO sp2;
COMMIT;
SELECT * FROM accounts ORDER BY id;

Important Notes

Not all databases support savepoints, but many popular ones like PostgreSQL and MySQL do.

You can have multiple savepoints in one transaction to control rollback precisely.

After a ROLLBACK TO savepoint_name, you can continue the transaction or set new savepoints.

Summary

Savepoints let you mark places inside a transaction to roll back to without undoing everything.

Use SAVEPOINT to create a savepoint, ROLLBACK TO savepoint_name to undo to it, and COMMIT to finish.

They help manage errors and partial undo inside complex transactions.