A savepoint is a way of implementing subtransactions (also known as
nested transactions) within a
relational database management system
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
by indicating a point within a
transaction that can be "
rolled back to" without affecting any work done in the transaction before the savepoint was created. Multiple savepoints can exist within a single transaction. Savepoints are useful for implementing complex error recovery in database applications. If an error occurs in the midst of a multiple-statement transaction, the application may be able to recover from the error (by rolling back to a savepoint) without needing to abort the entire transaction.
A savepoint can be declared by issuing a
SAVEPOINT ''name'' statement. All changes made after a savepoint has been declared can be undone by issuing a
ROLLBACK TO SAVEPOINT ''name'' command. Issuing
RELEASE SAVEPOINT ''name'' will cause the named savepoint to be discarded, but will not otherwise affect anything. Issuing the commands
ROLLBACK or
COMMIT will also discard any savepoints created since the start of the main transaction.
Savepoints are defined in the
SQL standard and are supported by all established SQL relational databases, including
PostgreSQL
PostgreSQL ( ) also known as Postgres, is a free and open-source software, free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL features transaction processing, transactions ...
,
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a proprietary multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for ru ...
,
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
,
MySQL
MySQL () is an Open-source software, open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A rel ...
,
IBM Db2,
SQLite
SQLite ( "S-Q-L-ite", "sequel-ite") is a free and open-source relational database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it ...
(since 3.6.8),
Firebird,
H2 Database Engine, and
Informix
Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) and multi-model database offerings. The Informix products were originally developed by Inform ...
(since version 11.50xC3).
{{databases
Data management
Transaction processing
Database management systems