Debian Codegraph @

www/lang_savepoint.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Savepoints</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Savepoints
122 </div>
123 </div>
124
125
126
127
128 <h1 id="syntax"><span>1. </span>Syntax</h1>
129
130 <p><b><a href="syntax/savepoint-stmt.html">savepoint-stmt:</a></b>
131 <button id='xb9721ecc' onclick='hideorshow("xb9721ecc","x4076c644")'>hide</button></p>
132 <div id='x4076c644' class='imgcontainer'>
133 <div style="max-width:346px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 346.003 34.56" data-pikchr-date="20250319161943">
134 <circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
135 <polygon points="32.4,17.28 20.88,21.6 20.88,12.96" style="fill:rgb(0,0,0)"/>
136 <path d="M9.36,17.28L26.64,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
137 <path d="M47.52,32.4L131.789,32.4A15.12 15.12 0 0 0 146.909 17.28A15.12 15.12 0 0 0 131.789 2.16L47.52,2.16A15.12 15.12 0 0 0 32.4 17.28A15.12 15.12 0 0 0 47.52 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
138 <text x="89.6544" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
139 <polygon points="169.949,17.28 158.429,21.6 158.429,12.96" style="fill:rgb(0,0,0)"/>
140 <path d="M146.909,17.28L164.189,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
141 <path d="M185.069,32.4L298.483,32.4A15.12 15.12 0 0 0 313.603 17.28A15.12 15.12 0 0 0 298.483 2.16L185.069,2.16A15.12 15.12 0 0 0 169.949 17.28A15.12 15.12 0 0 0 185.069 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
142 <text x="241.776" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
143 <polygon points="336.643,17.28 325.123,21.6 325.123,12.96" style="fill:rgb(0,0,0)"/>
144 <path d="M313.603,17.28L330.883,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
145 <circle cx="340.243" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
146 </svg>
147 </div>
148 </div>
149 <p><b><a href="syntax/release-stmt.html">release-stmt:</a></b>
150 <button id='xa254d5c5' onclick='hideorshow("xa254d5c5","x9bcd8b3e")'>hide</button></p>
151 <div id='x9bcd8b3e' class='imgcontainer'>
152 <div style="max-width:488px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 488.736 54" data-pikchr-date="20250319161943">
153 <circle cx="5.76" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
154 <polygon points="32.4,17.28 20.88,21.6 20.88,12.96" style="fill:rgb(0,0,0)"/>
155 <path d="M9.36,17.28L26.64,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
156 <path d="M47.52,32.4L111.053,32.4A15.12 15.12 0 0 0 126.173 17.28A15.12 15.12 0 0 0 111.053 2.16L47.52,2.16A15.12 15.12 0 0 0 32.4 17.28A15.12 15.12 0 0 0 47.52 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
157 <text x="79.2864" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">RELEASE</text>
158 <polygon points="162.173,17.28 150.653,21.6 150.653,12.96" style="fill:rgb(0,0,0)"/>
159 <path d="M126.173,17.28L156.413,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
160 <path d="M177.293,32.4L261.562,32.4A15.12 15.12 0 0 0 276.682 17.28A15.12 15.12 0 0 0 261.562 2.16L177.293,2.16A15.12 15.12 0 0 0 162.173 17.28A15.12 15.12 0 0 0 177.293 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
161 <text x="219.427" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
162 <polygon points="312.682,17.28 301.162,21.6 301.162,12.96" style="fill:rgb(0,0,0)"/>
163 <path d="M276.682,17.28L306.922,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
164 <path d="M327.802,32.4L441.216,32.4A15.12 15.12 0 0 0 456.336 17.28A15.12 15.12 0 0 0 441.216 2.16L327.802,2.16A15.12 15.12 0 0 0 312.682 17.28A15.12 15.12 0 0 0 327.802 32.4Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
165 <text x="384.509" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
166 <polygon points="479.376,17.28 467.856,21.6 467.856,12.96" style="fill:rgb(0,0,0)"/>
167 <path d="M456.336,17.28L473.616,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
168 <circle cx="482.976" cy="17.28" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
169 <polygon points="219.427,47.52 207.907,51.84 207.907,43.2" style="fill:rgb(0,0,0)"/>
170 <path d="M126.173,17.28 L 133.673,17.28 Q 141.173,17.28 141.173,32.28 L 141.173,32.52 Q 141.173,47.52 156.173,47.52 L 198.667,47.52 L 213.667,47.52" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
171 <path d="M219.427,47.52 L 276.682,47.52 Q 291.682,47.52 291.682,32.52 L 291.682,32.28 Q 291.682,17.28 299.182,17.28 L 306.682,17.28" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
172 </svg>
173 </div>
174 </div>
175 <p><b><a href="syntax/rollback-stmt.html">rollback-stmt:</a></b>
176 <button id='xca7d6da2' onclick='hideorshow("xca7d6da2","x9b810f3e")'>hide</button></p>
177 <div id='x9b810f3e' class='imgcontainer'>
178 <div style="max-width:801px"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 801.734 67.392" data-pikchr-date="20250319161943">
179 <circle cx="5.76" cy="33.696" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
180 <polygon points="32.4,33.696 20.88,38.016 20.88,29.376" style="fill:rgb(0,0,0)"/>
181 <path d="M9.36,33.696L26.64,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
182 <path d="M47.52,48.816L125.107,48.816A15.12 15.12 0 0 0 140.227 33.696A15.12 15.12 0 0 0 125.107 18.576L47.52,18.576A15.12 15.12 0 0 0 32.4 33.696A15.12 15.12 0 0 0 47.52 48.816Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
183 <text x="86.3136" y="33.696" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">ROLLBACK</text>
184 <polygon points="176.227,33.696 164.707,38.016 164.707,29.376" style="fill:rgb(0,0,0)"/>
185 <path d="M140.227,33.696L170.467,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
186 <path d="M191.347,48.816L303.149,48.816A15.12 15.12 0 0 0 318.269 33.696A15.12 15.12 0 0 0 303.149 18.576L191.347,18.576A15.12 15.12 0 0 0 176.227 33.696A15.12 15.12 0 0 0 191.347 48.816Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
187 <text x="247.248" y="33.696" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TRANSACTION</text>
188 <polygon points="390.269,33.696 378.749,38.016 378.749,29.376" style="fill:rgb(0,0,0)"/>
189 <path d="M318.269,33.696L384.509,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
190 <path d="M405.389,48.816L411.091,48.816A15.12 15.12 0 0 0 426.211 33.696A15.12 15.12 0 0 0 411.091 18.576L405.389,18.576A15.12 15.12 0 0 0 390.269 33.696A15.12 15.12 0 0 0 405.389 48.816Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
191 <text x="408.24" y="33.696" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TO</text>
192 <polygon points="462.211,33.696 450.691,38.016 450.691,29.376" style="fill:rgb(0,0,0)"/>
193 <path d="M426.211,33.696L456.451,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
194 <path d="M477.331,48.816L561.6,48.816A15.12 15.12 0 0 0 576.72 33.696A15.12 15.12 0 0 0 561.6 18.576L477.331,18.576A15.12 15.12 0 0 0 462.211 33.696A15.12 15.12 0 0 0 477.331 48.816Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
195 <text x="519.466" y="33.696" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">SAVEPOINT</text>
196 <polygon points="612.72,33.696 601.2,38.016 601.2,29.376" style="fill:rgb(0,0,0)"/>
197 <path d="M576.72,33.696L606.96,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
198 <path d="M627.84,48.816L741.254,48.816A15.12 15.12 0 0 0 756.374 33.696A15.12 15.12 0 0 0 741.254 18.576L627.84,18.576A15.12 15.12 0 0 0 612.72 33.696A15.12 15.12 0 0 0 627.84 48.816Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
199 <text x="684.547" y="33.696" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">savepoint-name</text>
200 <polygon points="792.374,33.696 780.854,38.016 780.854,29.376" style="fill:rgb(0,0,0)"/>
201 <path d="M756.374,33.696L786.614,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
202 <circle cx="795.974" cy="33.696" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
203 <polygon points="519.466,6.48 507.946,10.8 507.946,2.16" style="fill:rgb(0,0,0)"/>
204 <path d="M426.211,33.696 L 433.711,33.696 Q 441.211,33.696 441.211,20.088 Q 441.211,6.48 456.211,6.48 L 498.706,6.48 L 513.706,6.48" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
205 <path d="M519.466,6.48 L 576.72,6.48 Q 591.72,6.48 591.72,20.088 Q 591.72,33.696 599.22,33.696 L 606.72,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
206 <polygon points="519.466,60.912 507.946,65.232 507.946,56.592" style="fill:rgb(0,0,0)"/>
207 <path d="M352.229,33.696 L 359.729,33.696 Q 367.229,33.696 367.229,47.304 Q 367.229,60.912 382.229,60.912 L 498.706,60.912 L 513.706,60.912" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
208 <path d="M519.466,60.912 L 756.374,60.912 Q 771.374,60.912 771.374,47.304 Q 771.374,33.696 778.874,33.696 L 786.374,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
209 <polygon points="247.248,60.912 235.728,65.232 235.728,56.592" style="fill:rgb(0,0,0)"/>
210 <path d="M140.227,33.696 L 147.727,33.696 Q 155.227,33.696 155.227,47.304 Q 155.227,60.912 170.227,60.912 L 226.488,60.912 L 241.488,60.912" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
211 <path d="M247.248,60.912 L 318.269,60.912 Q 333.269,60.912 333.269,47.304 Q 333.269,33.696 340.769,33.696 L 348.269,33.696" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
212 </svg>
213 </div>
214 </div>
215
216
217 <h1 id="savepoints"><span>2. </span>Savepoints</h1>
218
219 <p> SAVEPOINTs are a method of creating transactions, similar to
220 <a href="lang_transaction.html">BEGIN</a> and <a href="lang_transaction.html">COMMIT</a>, except that the SAVEPOINT and RELEASE commands
221 are named and may be nested.</p>
222
223 <p> The SAVEPOINT command starts a new transaction with a name.
224 The transaction names need not be unique.
225 A SAVEPOINT can be started either within or outside of
226 a <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a>. When a SAVEPOINT is the outer-most savepoint
227 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
228 same as BEGIN DEFERRED TRANSACTION.</p>
229
230 <p>The ROLLBACK TO command reverts the state of the database back to what
231 it was just after the corresponding SAVEPOINT. Note that unlike that
232 plain <a href="lang_transaction.html">ROLLBACK</a> command (without the TO keyword) the ROLLBACK TO command
233 does not cancel the transaction. Instead of cancelling the transaction,
234 the ROLLBACK TO command restarts the transaction again at the beginning.
235 All intervening SAVEPOINTs are canceled, however.</p>
236
237 <p>The RELEASE command is like a <a href="lang_transaction.html">COMMIT</a> for a SAVEPOINT.
238 The RELEASE command causes all savepoints back to and including the
239 most recent savepoint with a matching name to be removed from the
240 transaction stack. The RELEASE of an inner transaction
241 does not cause any changes to be written to the database file; it merely
242 removes savepoints from the transaction stack such that it is
243 no longer possible to ROLLBACK TO those savepoints.
244 If a RELEASE command releases the outermost savepoint, so
245 that the transaction stack becomes empty, then RELEASE is the same
246 as <a href="lang_transaction.html">COMMIT</a>.
247 The <a href="lang_transaction.html">COMMIT</a> command may be used to release all savepoints and
248 commit the transaction even if the transaction was originally started
249 by a SAVEPOINT command instead of a <a href="lang_transaction.html">BEGIN</a> command.</p>
250
251 <p>If the savepoint-name in a RELEASE command does not match any
252 savepoint currently in the transaction stack, then no savepoints are
253 released, the database is unchanged, and the RELEASE command returns
254 an error.</p>
255
256 <p>Note that an inner transaction might commit (using the RELEASE command)
257 but then later have its work undone by a ROLLBACK in an outer transaction.
258 A power failure or program crash or OS crash will cause the outer-most
259 transaction to rollback, undoing all changes that have occurred within
260 that outer transaction, even changes that have supposedly been "committed"
261 by the RELEASE command. Content is not actually committed on the disk
262 until the outermost transaction commits.</p>
263
264 <p>There are several ways of thinking about the RELEASE command:</p>
265
266 <ul>
267 <li><p>
268 Some people view RELEASE as the equivalent of COMMIT for a SAVEPOINT.
269 This is an acceptable point of view as long as one remembers that the
270 changes committed by an inner transaction might later be undone by a
271 rollback in an outer transaction.</p></li>
272
273 <li><p>
274 Another view of RELEASE is that it merges a named transaction into its
275 parent transaction, so that the named transaction and its parent become
276 the same transaction. After RELEASE, the named transaction and its parent
277 will commit or rollback together, whatever their fate may be.
278 </p></li>
279
280 <li><p>
281 One can also think of savepoints as
282 "marks" in the transaction timeline. In this view, the SAVEPOINT command
283 creates a new mark, the ROLLBACK TO command rewinds the timeline back
284 to a point just after the named mark, and the RELEASE command
285 erases marks from the timeline without actually making any
286 changes to the database.
287 </p></li>
288 </ul>
289
290
291
292 <h1 id="transaction_nesting_rules"><span>3. </span>Transaction Nesting Rules</h1>
293
294 <p>The last transaction started will be the first
295 transaction committed or rolled back.</p>
296
297 <p>The <a href="lang_transaction.html">BEGIN</a> command only works if the transaction stack is empty, or
298 in other words if there are no pending transactions. If the transaction
299 stack is not empty when the <a href="lang_transaction.html">BEGIN</a> command is invoked, then the command
300 fails with an error.</p>
301
302 <p>The <a href="lang_transaction.html">COMMIT</a> command commits all outstanding transactions and leaves
303 the transaction stack empty.</p>
304
305 <p>The RELEASE command starts with the most recent addition to the
306 transaction stack and releases savepoints backwards
307 in time until it releases a savepoint with a matching savepoint-name.
308 Prior savepoints, even savepoints with matching savepoint-names, are
309 unchanged.
310 If the RELEASE command causes the
311 transaction stack to become empty (if the RELEASE command releases the
312 outermost transaction from the stack) then the transaction commits.</p>
313
314 <p>The <a href="lang_transaction.html">ROLLBACK</a> command without a TO clause rolls backs all transactions
315 and leaves the transaction stack empty.</p>
316
317 <p>The ROLLBACK command with a TO clause rolls back transactions going
318 backwards in time back to the most recent SAVEPOINT with a matching name.
319 The SAVEPOINT with the matching name remains on the transaction stack,
320 but all database changes that occurred after that SAVEPOINT was created
321 are rolled back. If the savepoint-name in a ROLLBACK TO command does not
322 match any SAVEPOINT on the stack, then the ROLLBACK command fails with an
323 error and leaves the state of the database unchanged.</p>
324 <p align="center"><small><i>This page was last updated on 2022-01-08 05:02:57Z </small></i></p>
325