1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Query Language: SAVEPOINT</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
120
<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>SAVEPOINT</h2><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>
121
<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>
122
<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>
125
<p> SAVEPOINTs are a method of creating transactions, similar to
126
<a href="lang_transaction.html">BEGIN</a> and <a href="lang_transaction.html">COMMIT</a>, except that the SAVEPOINT and RELEASE commands
127
are named and may be nested.</p>
129
<p> The SAVEPOINT command starts a new transaction with a name.
130
The transaction names need not be unique.
131
A SAVEPOINT can be started either within or outside of
132
a <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a>. When a SAVEPOINT is the outer-most savepoint
133
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
134
same as BEGIN DEFERRED TRANSACTION.</p>
136
<p>The ROLLBACK TO command reverts the state of the database back to what
137
it was just after the corresponding SAVEPOINT. Note that unlike that
138
plain <a href="lang_transaction.html">ROLLBACK</a> command (without the TO keyword) the ROLLBACK TO command
139
does not cancel the transaction. Instead of cancelling the transaction,
140
the ROLLBACK TO command restarts the transaction again at the beginning.
141
All intervening SAVEPOINTs are canceled, however.</p>
143
<p>The RELEASE command is like a <a href="lang_transaction.html">COMMIT</a> for a SAVEPOINT.
144
The RELEASE command causes all savepoints back to and including the
145
most recent savepoint with a matching name to be removed from the
146
transaction stack. The RELEASE of an inner transaction
147
does not cause any changes to be written to the database file; it merely
148
removes savepoints from the transaction stack such that it is
149
no longer possible to ROLLBACK TO those savepoints.
150
If a RELEASE command releases the outermost savepoint, so
151
that the transaction stack becomes empty, then RELEASE is the same
152
as <a href="lang_transaction.html">COMMIT</a>.
153
The <a href="lang_transaction.html">COMMIT</a> command may be used to release all savepoints and
154
commit the transaction even if the transaction was originally started
155
by a SAVEPOINT command instead of a <a href="lang_transaction.html">BEGIN</a> command.</p>
157
<p>If the savepoint-name in a RELEASE command does not match any
158
savepoint currently in the transaction stack, then no savepoints are
159
released, the database is unchanged, and the RELEASE command returns
162
<p>Note that an inner transaction might commit (using the RELEASE command)
163
but then later have its work undone by a ROLLBACK in an outer transaction.
164
A power failure or program crash or OS crash will cause the outer-most
165
transaction to rollback, undoing all changes that have occurred within
166
that outer transaction, even changes that have supposedly been "committed"
167
by the RELEASE command. Content is not actually committed on the disk
168
until the outermost transaction commits.</p>
170
<p>There are several ways of thinking about the RELEASE command:</p>
174
Some people view RELEASE as the equivalent of COMMIT for a SAVEPOINT.
175
This is an acceptable point of view as long as one remembers that the
176
changes committed by an inner transaction might later be undone by a
177
rollback in an outer transaction.</p></li>
180
Another view of RELEASE is that it merges a named transaction into its
181
parent transaction, so that the named transaction and its parent become
182
the same transaction. After RELEASE, the named transaction and its parent
183
will commit or rollback together, whatever their fate may be.
187
One can also think of savepoints as
188
"marks" in the transaction timeline. In this view, the SAVEPOINT command
189
creates a new mark, the ROLLBACK TO command rewinds the timeline back
190
to a point just after the named mark, and the RELEASE command
191
erases marks from the timeline without actually making any
192
changes to the database.
198
<h3>Transaction Nesting Rules</h3>
200
<p>The last transaction started will be the first
201
transaction committed or rolled back.</p>
203
<p>The <a href="lang_transaction.html">BEGIN</a> command only works if the transaction stack is empty, or
204
in other words if there are no pending transactions. If the transaction
205
stack is not empty when the <a href="lang_transaction.html">BEGIN</a> command is invoked, then the command
206
fails with an error.</p>
208
<p>The <a href="lang_transaction.html">COMMIT</a> command commits all outstanding transactions and leaves
209
the transaction stack empty.</p>
211
<p>The RELEASE command starts with the most recent addition to the
212
transaction stack and releases savepoints backwards
213
in time until it releases a savepoint with a matching savepoint-name.
214
Prior savepoints, even savepoints with matching savepoint-names, are
216
If the RELEASE command causes the
217
transaction stack to become empty (if the RELEASE command releases the
218
outermost transaction from the stack) then the transaction commits.</p>
220
<p>The <a href="lang_transaction.html">ROLLBACK</a> command without a TO clause rolls backs all transactions
221
and leaves the transaction stack empty.</p>
223
<p>The ROLLBACK command with a TO clause rolls back transactions going
224
backwards in time back to the most recent SAVEPOINT with a matching name.
225
The SAVEPOINT with the matching name remains on the transaction stack,
226
but all database changes that occurred after that SAVEPOINT was created
227
are rolled back. If the savepoint-name in a ROLLBACK TO command does not
228
match any SAVEPOINT on the stack, then the ROLLBACK command fails with an
229
error and leaves the state of the database unchanged.</p>
added
removed
lang_savepoint.html
