Small. Fast. Reliable.
Choose any three.
Savepoints

1. Syntax

savepoint-stmt:

SAVEPOINT savepoint-name

release-stmt:

RELEASE SAVEPOINT savepoint-name

rollback-stmt:

ROLLBACK TRANSACTION TO SAVEPOINT savepoint-name

2. Savepoints

R-04005-08111:[SAVEPOINTs are a method of creating transactions, similar to BEGIN and COMMIT, except that the SAVEPOINT and RELEASE commands are named and may be nested. ]

R-57963-05936:[The SAVEPOINT command starts a new transaction with a name. ] R-62101-33764:[The transaction names need not be unique. ] R-18818-06259:[A SAVEPOINT can be started either within or outside of a BEGIN...COMMIT. ] R-38828-57497:[When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION. ]

R-18401-29434:[The ROLLBACK TO command reverts the state of the database back to what it was just after the corresponding SAVEPOINT. ] R-10477-59572:[Note that unlike that plain ROLLBACK command (without the TO keyword) the ROLLBACK TO command does not cancel the transaction. ] R-07727-56631:[Instead of cancelling the transaction, the ROLLBACK TO command restarts the transaction again at the beginning. ] R-10722-45310:[All intervening SAVEPOINTs are canceled, however. ]

R-07434-39946:[The RELEASE command is like a COMMIT for a SAVEPOINT. ] R-60758-29241:[The RELEASE command causes all savepoints back to and including the most recent savepoint with a matching name to be removed from the transaction stack. ] R-45106-55778:[The RELEASE of an inner transaction does not cause any changes to be written to the database file; it merely removes savepoints from the transaction stack such that it is no longer possible to ROLLBACK TO those savepoints. ] R-23935-31469:[If a RELEASE command releases the outermost savepoint, so that the transaction stack becomes empty, then RELEASE is the same as COMMIT. ] R-00695-31519:[The COMMIT command may be used to release all savepoints and commit the transaction even if the transaction was originally started by a SAVEPOINT command instead of a BEGIN command. ]

R-65068-25814:[If the savepoint-name in a RELEASE command does not match any savepoint currently in the transaction stack, then no savepoints are released, the database is unchanged, and the RELEASE command returns an error. ]

R-28351-07091:[Note that an inner transaction might commit (using the RELEASE command) but then later have its work undone by a ROLLBACK in an outer transaction. ] R-02961-26666:[A power failure or program crash or OS crash will cause the outer-most transaction to rollback, undoing all changes that have occurred within that outer transaction, even changes that have supposedly been "committed" by the RELEASE command. ] R-28530-22001:[Content is not actually committed on the disk until the outermost transaction commits. ]

There are several ways of thinking about the RELEASE command:

3. Transaction Nesting Rules

R-51202-43754:[The last transaction started will be the first transaction committed or rolled back. ]

R-19672-38383:[The BEGIN command only works if the transaction stack is empty, or in other words if there are no pending transactions. ] R-59172-51198:[If the transaction stack is not empty when the BEGIN command is invoked, then the command fails with an error. ]

R-48711-46457:[The COMMIT command commits all outstanding transactions and leaves the transaction stack empty. ]

R-12830-59321:[The RELEASE command starts with the most recent addition to the transaction stack and releases savepoints backwards in time until it releases a savepoint with a matching savepoint-name. ] R-38293-42495:[Prior savepoints, even savepoints with matching savepoint-names, are unchanged. ] R-55606-57227:[If the RELEASE command causes the transaction stack to become empty (if the RELEASE command releases the outermost transaction from the stack) then the transaction commits. ]

R-18711-35549:[The ROLLBACK command without a TO clause rolls backs all transactions and leaves the transaction stack empty. ]

R-29144-37270:[The ROLLBACK command with a TO clause rolls back transactions going backwards in time back to the most recent SAVEPOINT with a matching name. ] R-19003-27813:[The SAVEPOINT with the matching name remains on the transaction stack, but all database changes that occurred after that SAVEPOINT was created are rolled back. ] R-29830-14325:[If the savepoint-name in a ROLLBACK TO command does not match any SAVEPOINT on the stack, then the ROLLBACK command fails with an error and leaves the state of the database unchanged. ]

This page last modified on 2022-01-08 05:02:57 UTC