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.
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
Press Run (or Ctrl + Enter) to see the result here.
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
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 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 fullROLLBACKdiscards everything, including released savepoints. - SQLite quirk: a
SAVEPOINToutside any transaction starts one, andRELEASEof the outermost savepoint commits it, just likeCOMMIT. InsideBEGIN ... COMMITit 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 TOand 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 TOin most databases. - Name reuse:
ROLLBACK TOandRELEASEuse the most recent savepoint with that name.