SAVEPOINTTransactions
Buy Me A Coffee

A named marker within a transaction to which you can partially roll back without aborting the entire transaction. Enables nested or selective error recovery inside a larger unit of work.

A named marker within a transaction allowing partial rollback without aborting the entire transaction. Useful for recovering from errors mid-transaction. Can be nested in some databases. Release savepoints once no longer needed to free resources.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 4.0.14 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT supported for InnoDB tables since MySQL 4.0.14. Not available for MyISAM (which has no transaction support). Savepoints are session-scoped and discarded on COMMIT or full ROLLBACK. Nested savepoints with the same name replace the previous one.
PostgreSQL ✓ Supported 8.0 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT fully supported. PostgreSQL also uses savepoints internally to implement subtransactions. In PostgreSQL, any error inside a transaction puts it into an error state that blocks further commands — ROLLBACK TO SAVEPOINT is the only way to recover a usable transaction state after an error without aborting everything. This makes savepoints especially important in PostgreSQL for error handling inside application code.
SQL Server ✓ Supported 6.5 SAVE TRANSACTION (note: SAVE, not SAVEPOINT) creates a savepoint. ROLLBACK TRANSACTION <name> rolls back to the savepoint without ending the transaction. SAVE TRANSACTION does not support a full RELEASE equivalent. Nested BEGIN TRANSACTION increments @@TRANCOUNT but the outermost COMMIT is the only one that actually commits — inner COMMITs decrement @@TRANCOUNT only.
Oracle ✓ Supported 7 SAVEPOINT and ROLLBACK TO SAVEPOINT supported. Oracle auto-starts transactions (no explicit BEGIN needed — the first DML statement starts a transaction). RELEASE SAVEPOINT is not supported in Oracle — savepoints are implicitly released on COMMIT. Oracle's autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) are a related but distinct concept.
SQLite ✓ Supported 3.6.8 SAVEPOINT, ROLLBACK TO, and RELEASE supported since SQLite 3.6.8. SQLite's SAVEPOINT also doubles as a BEGIN — nesting SAVEPOINTs creates subtransactions. RELEASE is equivalent to committing the savepoint's work into the outer transaction (like COMMIT for the savepoint). Rolling back to a savepoint and then RELEASEing it is not the same as canceling it — RELEASE after ROLLBACK TO simply removes the savepoint marker.

Details

The SQL standard keyword is SAVEPOINT; SQL Server uses SAVE TRANSACTION instead. RELEASE SAVEPOINT is supported in MySQL, PostgreSQL, and SQLite, but not in Oracle (implicit release on COMMIT) or SQL Server (no equivalent). PostgreSQL's behavior makes savepoints especially critical in application code: any unhandled error puts the transaction into an aborted state that rejects all further commands — ROLLBACK TO SAVEPOINT is the escape hatch that clears the error without losing the entire transaction. SQLite's SAVEPOINT is unique in that it can replace BEGIN entirely and supports arbitrary nesting.

Standard Syntax

BEGIN; INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVEPOINT after_order; INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- If this fails: ROLLBACK TO SAVEPOINT after_order; -- Order insert is still intact; continue or commit: COMMIT;

Version Support

MySQL: Since 4.0.14 PostgreSQL: Since 8.0 SQL Server: Since 6.5 Oracle: Since 7 SQLite: Since 3.6.8

Per-Database Syntax & Notes

MySQL

SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT supported for InnoDB tables since MySQL 4.0.14. Not available for MyISAM (which has no transaction support). Savepoints are session-scoped and discarded on COMMIT or full ROLLBACK. Nested savepoints with the same name replace the previous one.

START TRANSACTION; INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVEPOINT sp1; INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- Partial rollback: ROLLBACK TO SAVEPOINT sp1; -- order_items INSERT is undone; orders INSERT remains -- Release savepoint (optional, removes the savepoint marker): RELEASE SAVEPOINT sp1; COMMIT;

PostgreSQL

SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT fully supported. PostgreSQL also uses savepoints internally to implement subtransactions. In PostgreSQL, any error inside a transaction puts it into an error state that blocks further commands — ROLLBACK TO SAVEPOINT is the only way to recover a usable transaction state after an error without aborting everything. This makes savepoints especially important in PostgreSQL for error handling inside application code.

BEGIN; INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVEPOINT after_order; INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- Suppose this raises an error: ROLLBACK TO SAVEPOINT after_order; -- Transaction is now in a clean state; the error is cleared INSERT INTO order_items (order_id, product_id) VALUES (101, 10); -- Try a different item COMMIT; -- Release (optional, frees the savepoint name): RELEASE SAVEPOINT after_order;

SQL Server

SAVE TRANSACTION (note: SAVE, not SAVEPOINT) creates a savepoint. ROLLBACK TRANSACTION <name> rolls back to the savepoint without ending the transaction. SAVE TRANSACTION does not support a full RELEASE equivalent. Nested BEGIN TRANSACTION increments @@TRANCOUNT but the outermost COMMIT is the only one that actually commits — inner COMMITs decrement @@TRANCOUNT only.

BEGIN TRANSACTION; INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVE TRANSACTION after_order; -- SQL Server uses SAVE TRANSACTION INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- Partial rollback: ROLLBACK TRANSACTION after_order; -- order_items insert is undone; orders insert remains -- Note: can also use shorthand: -- SAVE TRAN sp1 / ROLLBACK TRAN sp1 COMMIT TRANSACTION;

Oracle

SAVEPOINT and ROLLBACK TO SAVEPOINT supported. Oracle auto-starts transactions (no explicit BEGIN needed — the first DML statement starts a transaction). RELEASE SAVEPOINT is not supported in Oracle — savepoints are implicitly released on COMMIT. Oracle's autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) are a related but distinct concept.

-- No explicit BEGIN in Oracle: INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVEPOINT after_order; INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- Partial rollback: ROLLBACK TO SAVEPOINT after_order; -- order_items insert rolled back; orders insert intact COMMIT; -- Savepoint is implicitly released on COMMIT

SQLite

SAVEPOINT, ROLLBACK TO, and RELEASE supported since SQLite 3.6.8. SQLite's SAVEPOINT also doubles as a BEGIN — nesting SAVEPOINTs creates subtransactions. RELEASE is equivalent to committing the savepoint's work into the outer transaction (like COMMIT for the savepoint). Rolling back to a savepoint and then RELEASEing it is not the same as canceling it — RELEASE after ROLLBACK TO simply removes the savepoint marker.

BEGIN; INSERT INTO orders (customer_id, total) VALUES (1, 99.99); SAVEPOINT after_order; INSERT INTO order_items (order_id, product_id) VALUES (101, 9); -- Partial rollback: ROLLBACK TO after_order; -- Release (removes the savepoint marker, merges into outer transaction): RELEASE after_order; COMMIT; -- SQLite SAVEPOINT can also nest: SAVEPOINT outer; INSERT INTO t VALUES (1); SAVEPOINT inner; INSERT INTO t VALUES (2); RELEASE inner; -- commits inner work into outer ROLLBACK TO outer; -- undoes everything including inner