1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Savepoints</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Savepoints
</div>
</div>
<h1 id="syntax"><span>1. </span>Syntax</h1>
<p><b><a href="syntax/savepoint-stmt.html">savepoint-stmt:</a></b>
<button id='x1855' onclick='hideorshow("x1855","x1856")'>hide</button></p>
<div id='x1856' class='imgcontainer'>
<div style="max-width:346px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 346.003 34.56">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,32L131,32A15 15 0 0 0 146 17A15 15 0 0 0 131 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="89" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
<polygon points="169,17 158,21 158,12" style="fill:rgb(0,0,0)"/>
<path d="M146,17L164,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M185,32L298,32A15 15 0 0 0 313 17A15 15 0 0 0 298 2L185,2A15 15 0 0 0 169 17A15 15 0 0 0 185 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="241" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
<polygon points="336,17 325,21 325,12" style="fill:rgb(0,0,0)"/>
<path d="M313,17L330,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="340" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p><b><a href="syntax/release-stmt.html">release-stmt:</a></b>
<button id='x1857' onclick='hideorshow("x1857","x1858")'>hide</button></p>
<div id='x1858' class='imgcontainer'>
<div style="max-width:488px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 488.736 54">
<circle cx="5" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,17 20,21 20,12" style="fill:rgb(0,0,0)"/>
<path d="M9,17L26,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,32L111,32A15 15 0 0 0 126 17A15 15 0 0 0 111 2L47,2A15 15 0 0 0 32 17A15 15 0 0 0 47 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="79" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RELEASE</text>
<polygon points="162,17 150,21 150,12" style="fill:rgb(0,0,0)"/>
<path d="M126,17L156,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M177,32L261,32A15 15 0 0 0 276 17A15 15 0 0 0 261 2L177,2A15 15 0 0 0 162 17A15 15 0 0 0 177 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="219" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
<polygon points="312,17 301,21 301,12" style="fill:rgb(0,0,0)"/>
<path d="M276,17L306,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M327,32L441,32A15 15 0 0 0 456 17A15 15 0 0 0 441 2L327,2A15 15 0 0 0 312 17A15 15 0 0 0 327 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="384" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
<polygon points="479,17 467,21 467,12" style="fill:rgb(0,0,0)"/>
<path d="M456,17L473,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="482" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="219,47 207,51 207,43" style="fill:rgb(0,0,0)"/>
<path d="M126,17 L 133,17 Q 141,17 141,32 L 141,32 Q 141,47 156,47 L 198,47 L 213,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M219,47 L 276,47 Q 291,47 291,32 L 291,32 Q 291,17 299,17 L 306,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p><b><a href="syntax/rollback-stmt.html">rollback-stmt:</a></b>
<button id='x1859' onclick='hideorshow("x1859","x1860")'>hide</button></p>
<div id='x1860' class='imgcontainer'>
<div style="max-width:801px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 801.734 67.392">
<circle cx="5" cy="33" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="32,33 20,38 20,29" style="fill:rgb(0,0,0)"/>
<path d="M9,33L26,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M47,48L125,48A15 15 0 0 0 140 33A15 15 0 0 0 125 18L47,18A15 15 0 0 0 32 33A15 15 0 0 0 47 48Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="86" y="33" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ROLLBACK</text>
<polygon points="176,33 164,38 164,29" style="fill:rgb(0,0,0)"/>
<path d="M140,33L170,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M191,48L303,48A15 15 0 0 0 318 33A15 15 0 0 0 303 18L191,18A15 15 0 0 0 176 33A15 15 0 0 0 191 48Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="247" y="33" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TRANSACTION</text>
<polygon points="390,33 378,38 378,29" style="fill:rgb(0,0,0)"/>
<path d="M318,33L384,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M405,48L411,48A15 15 0 0 0 426 33A15 15 0 0 0 411 18L405,18A15 15 0 0 0 390 33A15 15 0 0 0 405 48Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="408" y="33" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TO</text>
<polygon points="462,33 450,38 450,29" style="fill:rgb(0,0,0)"/>
<path d="M426,33L456,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M477,48L561,48A15 15 0 0 0 576 33A15 15 0 0 0 561 18L477,18A15 15 0 0 0 462 33A15 15 0 0 0 477 48Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="519" y="33" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
<polygon points="612,33 601,38 601,29" style="fill:rgb(0,0,0)"/>
<path d="M576,33L606,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M627,48L741,48A15 15 0 0 0 756 33A15 15 0 0 0 741 18L627,18A15 15 0 0 0 612 33A15 15 0 0 0 627 48Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="684" y="33" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
<polygon points="792,33 780,38 780,29" style="fill:rgb(0,0,0)"/>
<path d="M756,33L786,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="795" cy="33" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="519,6 507,10 507,2" style="fill:rgb(0,0,0)"/>
<path d="M426,33 L 433,33 Q 441,33 441,20 Q 441,6 456,6 L 498,6 L 513,6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M519,6 L 576,6 Q 591,6 591,20 Q 591,33 599,33 L 606,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="519,60 507,65 507,56" style="fill:rgb(0,0,0)"/>
<path d="M352,33 L 359,33 Q 367,33 367,47 Q 367,60 382,60 L 498,60 L 513,60" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M519,60 L 756,60 Q 771,60 771,47 Q 771,33 778,33 L 786,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="247,60 235,65 235,56" style="fill:rgb(0,0,0)"/>
<path d="M140,33 L 147,33 Q 155,33 155,47 Q 155,60 170,60 L 226,60 L 241,60" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M247,60 L 318,60 Q 333,60 333,47 Q 333,33 340,33 L 348,33" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<h1 id="savepoints"><span>2. </span>Savepoints</h1>
<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 canceled, however.</p>
<p>The RELEASE command is like a <a href="lang_transaction.html">COMMIT</a> 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 <a href="lang_transaction.html">COMMIT</a>.
The <a href="lang_transaction.html">COMMIT</a> 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 <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 transaction 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>
<h1 id="transaction_nesting_rules"><span>3. </span>Transaction Nesting Rules</h1>
<p>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 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>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/lang_savepoint.in?m=60d80524de533cc91">2020-02-10 17:23:44</a> UTC </small></i></p>
|
