Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Transactions and Performance

SQL SAVEPOINT

Reference for SQL SAVEPOINT: mark a point inside a transaction, ROLLBACK TO it, RELEASE it, nest savepoints, and the syntax differences between databases.

A savepoint is a named marker inside a transaction. You can later roll back to the marker and undo only the work done after it, while everything before it stays pending. This is how you try a risky step without losing the rest of the transaction, and it is how application frameworks implement "nested transactions".

After ROLLBACK TO name, the transaction is still open and the savepoint still exists, so you can try again. RELEASE name forgets the savepoint but keeps its changes.

SQL
BEGIN;
UPDATE books SET stock = stock - 1 WHERE id = 1;
SAVEPOINT before_discount;
UPDATE books SET price = price * 0.5 WHERE id = 1;
ROLLBACK TO before_discount;   -- undoes only the price change
COMMIT;                        -- keeps the stock change

Try it

SQL Roll back to a savepoint ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Nested savepoints ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In the second example, rolling back to inner_step undoes only the change to product 3. Rolling back to outer_step then also undoes product 2, and the final ROLLBACK discards product 1 as well.

Syntax

SQL
SAVEPOINT name;
ROLLBACK [TRANSACTION] TO [SAVEPOINT] name;
RELEASE [SAVEPOINT] name;
Part Description
SAVEPOINT name Creates a marker. Reusing a name creates a newer marker with the same name.
ROLLBACK TO name Undoes changes made after the savepoint. The savepoint, and the transaction, stay open. Savepoints created after it are dropped.
RELEASE name Removes the savepoint (and any created after it) and keeps the changes. They are only committed when the outer transaction commits.

Dialect differences

Database Create Roll back Release
SQLite SAVEPOINT name ROLLBACK TO name RELEASE name
PostgreSQL SAVEPOINT name ROLLBACK TO [SAVEPOINT] name RELEASE [SAVEPOINT] name
MySQL SAVEPOINT name ROLLBACK TO [SAVEPOINT] name RELEASE SAVEPOINT name
SQL Server SAVE TRANSACTION name ROLLBACK TRANSACTION name No release statement
Oracle SAVEPOINT name ROLLBACK TO [SAVEPOINT] name No release statement
SQL
-- SQL Server
BEGIN TRANSACTION;
SAVE TRANSACTION before_discount;
UPDATE books SET price = price * 0.5 WHERE id = 1;
ROLLBACK TRANSACTION before_discount;
COMMIT TRANSACTION;

Usage notes

  • Savepoints are part of the outer transaction. Nothing is permanent until the outermost COMMIT. A full ROLLBACK discards everything, including released savepoints.
  • SQLite quirk: a SAVEPOINT outside any transaction starts one, and RELEASE of the outermost savepoint commits it, just like COMMIT. Inside BEGIN ... COMMIT it behaves as described above.
  • PostgreSQL error recovery. After an error inside a transaction, PostgreSQL refuses further commands until you roll back. Wrapping a risky statement in a savepoint lets you recover with ROLLBACK TO and continue.
  • Frameworks (ORMs, atomic() blocks and similar) create a savepoint for each nested transaction block and roll back to it when an inner block fails.
  • Cost. Each savepoint takes some memory, and PostgreSQL has performance cliffs with very many open subtransactions. Release savepoints you no longer need inside loops.
  • Locks are not released by ROLLBACK TO in most databases.
  • Name reuse: ROLLBACK TO and RELEASE use the most recent savepoint with that name.