Savepoints in SQL – Partial Rollback in Transactions

What Is a Savepoint in SQL?

A SAVEPOINT in SQL is a marker within a transaction that allows you to partially roll back the transaction to that specific point, without canceling the entire transaction.

Savepoints are especially useful in complex transactions where you want to test or execute multiple steps, and only undo a specific part if needed.

Why Use Savepoints?

• To rollback only part of a transaction
• To manage error handling more precisely
• To test operations without ending the whole transaction
• To improve control in multi-step workflows

Basic Syntax

START TRANSACTION;

-- Some SQL operations
SAVEPOINT savepoint_name;

-- More SQL operations
ROLLBACK TO savepoint_name;

COMMIT;

Example: Using SAVEPOINT in a Transaction

START TRANSACTION;

INSERT INTO orders (order_id, customer_id) VALUES (1, 101);
SAVEPOINT order_inserted;

INSERT INTO inventory (product_id, stock) VALUES (10, -5); -- Invalid stock

-- An error occurred, rollback to the last good state
ROLLBACK TO order_inserted;

-- Continue with other valid operations
INSERT INTO logs (message) VALUES ('Order created, inventory skipped');

COMMIT;

Explanation:

• A new transaction starts.
• A savepoint is created after inserting into orders.
• An error occurs during the inventory insert.
• We roll back only to the order_inserted point, keeping the valid insert.
• Finally, we commit the valid part of the transaction.

Related Commands

Important Notes

• Savepoints can be nested in a transaction.
• Not all databases support RELEASE SAVEPOINT (e.g., MySQL does not require it).
• Savepoints are only valid within a transaction block.

Supported In

• PostgreSQL
• MySQL (InnoDB engine)
• Oracle
• SQL Server (using BEGIN TRANSACTION / SAVE TRANSACTION)