BlitzNG
/
database.mod
spogulis no https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: SAVEPOINT</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>SAVEPOINT</h1><h4><a href="syntaxdiagrams.html#savepoint-stmt">savepoint-stmt:</a></h4><blockquote> <img alt="syntax diagram savepoint-stmt" src="images/syntax/savepoint-stmt.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#release-stmt">release-stmt:</a></h4><blockquote> <img alt="syntax diagram release-stmt" src="images/syntax/release-stmt.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#rollback-stmt">rollback-stmt:</a></h4><blockquote> <img alt="syntax diagram rollback-stmt" src="images/syntax/rollback-stmt.gif"></img> </blockquote>


<p>SAVEPOINTs are a method of creating transactions, similar to
<a href="lang_transaction.html">BEGIN</a> and <a href="lang_transaction.html">COMMIT</a>, except that the SAVEPOINT and RELEASE commands
are named and may be nested.</p>

<p>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 <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a>.  When a SAVEPOINT is the outer-most savepoint
and it is not within a <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a> then the behavior is the
same as BEGIN DEFERRED TRANSACTION.</p>

<p>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 <a href="lang_transaction.html">ROLLBACK</a> 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 SAVEPOINTs are cancelled, however.</p>

<p>The RELEASE is like a <a href="lang_transaction.html">COMMIT</a> for a SAVEPOINT.
The RELEASE command causes all savepoints back to and including the first
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 <a href="lang_transaction.html">COMMIT</a>.
The <a href="lang_transaction.html">COMMIT</a> command may used to release all savepoints and
commit the transaction even if the transaction was originally started
by a SAVEPOINT command instead of a <a href="lang_transaction.html">BEGIN</a> command.</p>

<p>If the savepoint-name in a RELEASE command does not match any
savepoint currently in the tranaction stack, then no savepoints are
released, the database is unchanged, and the RELEASE command returns
an error.</p>

<p>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.</p>

<p>There are several ways of thinking about the RELEASE command:</p>

<ul>
<li><p>
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.</p></li>

<li><p>
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.
</p></li>

<li><p>
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.
</p></li>
</ul>


<h3>Transaction Nesting Rules</h3>

<p>Transactions stack.  The last transaction started will be the first
transaction committed or rolled back.</p>

<p>The <a href="lang_transaction.html">BEGIN</a> command only works if the transaction stack is empty, or
in other words if there are no pending transactions.  If the transaction
stack is not empty when the <a href="lang_transaction.html">BEGIN</a> command is invoked, then the command
fails with an error.</p>

<p>The <a href="lang_transaction.html">COMMIT</a> command commits all outstanding transactions and leaves
the transaction stack empty.</p>

<p>The RELEASE command starts with the most recent addition to the
transaction stack and releases savepoints backwards 
in time until it releases a savepoint mark 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.</p>

<p>The <a href="lang_transaction.html">ROLLBACK</a> command without a TO clause rolls backs all transactions
and leaves the transaction stack empty.</p>

<p>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.</p>