#readwise # SQLite Savepoints  ## Metadata - Author: [[sqlite.org]] - Full Title: SQLite Savepoints - URL: https://www.sqlite.org/lang_savepoint.html ## Summary `SAVEPOINT`-s allow for named transactions that can be nested within other transactions. You can use the `ROLLBACK TO` command to revert the database to a specific `SAVEPOINT` without canceling the whole transaction. The `RELEASE` command removes savepoints from the transaction stack but does not change the database until the outermost transaction is committed. If there is an error in the savepoint during a `RELEASE` or `ROLLBACK`, the database remains unchanged. ## Highlights **`SAVEPOINT`-s are a method of creating transactions, similar to [[SQLite Transactions|BEGIN]] and [[SQLite Transactions|COMMIT]], except that the `SAVEPOINT` and `RELEASE` commands are named and may be nested.** ([View Highlight](https://read.readwise.io/read/01jjhswxr7zjbyb79tjr80rtm5)) ^t3djkc --- The `SAVEPOINT` command starts a new transaction with a name. The transaction names need not be unique. A `SAVEPOINT` can be started either within or outside of a [[SQLite Transactions|BEGIN..COMMIT]]. **When a `SAVEPOINT` is the outer-most savepoint and it is not within a [[SQLite Transactions|BEGIN..COMMIT]] then the behavior is the same as `BEGIN DEFERRED TRANSACTION`.** ^s7oqgc The `ROLLBACK TO` command reverts the state of the database back to what it was just after the corresponding `SAVEPOINT`. Note that **unlike that plain [[SQLite Transactions|ROLLBACK]] command (without the TO keyword) the `ROLLBACK TO` command does not cancel the transaction. Instead of cancelling the transaction, the `ROLLBACK TO` command restarts the transaction again at the beginning. All intervening `SAVEPOINT`-s are canceled, however.** ^mn014g **The `RELEASE` command is like a [[SQLite Transactions|COMMIT]] for a `SAVEPOINT`. 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.** 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. **If a `RELEASE` command releases the outermost savepoint, so that the transaction stack becomes empty, then `RELEASE` is the same as [[SQLite Transactions|COMMIT]].** The [[SQLite Transactions|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 [[SQLite Transactions|BEGIN]] command. ([View Highlight](https://read.readwise.io/read/01jjht44nvkvymkawnwp0s65k7)) ^pzqavu --- 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. 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. Content is not actually committed on the disk until the outermost transaction commits. ([View Highlight](https://read.readwise.io/read/01jjht7vgk4vy6h5005ttx14p6)) --- There are several ways of thinking about the `RELEASE` command: - Some people view `RELEASE` as the equivalent of `COMMIT` for a `SAVEPOINT`. This is an acceptable point of view as long as one remembers that the changes committed by an inner transaction might later be undone by a rollback in an outer transaction. - Another view of `RELEASE` is that it merges a named transaction into its parent transaction, so that the named transaction and its parent become the same transaction. After RELEASE, the named transaction and its parent will commit or rollback together, whatever their fate may be. - One can also think of savepoints as "marks" in the transaction timeline. In this view, the `SAVEPOINT` command creates a new mark, the `ROLLBACK TO` command rewinds the timeline back to a point just after the named mark, and the `RELEASE` command erases marks from the timeline without actually making any changes to the database. ([View Highlight](https://read.readwise.io/read/01jjht9j9vvzt5874tc28n38mx)) --- **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.** Prior savepoints, even savepoints with matching savepoint-names, are unchanged. 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. ([View Highlight](https://read.readwise.io/read/01jjhtbk8rrby73vkjq5vcgdb2)) ^1rb5h2 --- **The `ROLLBACK` command with a `TO` clause rolls back transactions going backwards in time back to the most recent `SAVEPOINT` with a matching name.** 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. 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. ([View Highlight](https://read.readwise.io/read/01jjhtctnqr0fr8x9kq7arz3pe)) ^c2smey ---