A savepoint is a way of implementing subtransactions (also known as
nested transactions) within a
relational database management system
A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
by indicating a point within a
transaction
Transaction or transactional may refer to:
Commerce
* Financial transaction, an agreement, communication, or movement carried out between a buyer and a seller to exchange an asset for payment
*Debits and credits in a Double-entry bookkeeping sys ...
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 relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
,
Oracle Database
Oracle Database (commonly referred to as Oracle DBMS, Oracle Autonomous Database, or simply as Oracle) is a multi-model database management system produced and marketed by Oracle Corporation.
It is a database commonly used for running online ...
,
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...
,
MySQL
MySQL () is an 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 relational database ...
,
IBM Db2
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON and ...
,
SQLite
SQLite (, ) is a 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 belongs to the family of embedded databases. It is the mo ...
(since 3.6.8),
Firebird,
H2 Database Engine, and
Informix
IBM Informix is a product family within IBM's Information Management division that is centered on several relational database management system (RDBMS) offerings. The Informix products were originally developed by Informix Corporation, whose ...
(since version 11.50xC3).
{{databases
Data management
Transaction processing