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
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
|
<!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>Transaction</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">
Transaction
</div>
</div>
<h1 id="transaction_control_syntax"><span>1. </span>Transaction Control Syntax</h1>
<p><b><a href="syntax/begin-stmt.html">begin-stmt:</a></b>
<button id='x2131' onclick='hideorshow("x2131","x2132")'>hide</button></p>
<div id='x2132' class='imgcontainer'>
<div style="max-width:560px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 560.669 140.4">
<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,32L86,32A15 15 0 0 0 101 17A15 15 0 0 0 86 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="66" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">BEGIN</text>
<polygon points="124,17 112,21 112,12" style="fill:rgb(0,0,0)"/>
<path d="M101,17L118,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="162,123 150,127 150,118" style="fill:rgb(0,0,0)"/>
<path d="M124,17 L 131,17 Q 139,17 139,32 L 139,108 Q 139,123 147,123 L 156,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M177,138L261,138A15 15 0 0 0 276 123A15 15 0 0 0 261 108L177,108A15 15 0 0 0 162 123A15 15 0 0 0 177 138Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="219" y="123" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">EXCLUSIVE</text>
<polygon points="299,123 287,127 287,118" style="fill:rgb(0,0,0)"/>
<path d="M276,123L293,123" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="337,17 325,21 325,12" style="fill:rgb(0,0,0)"/>
<path d="M299,123 L 306,123 Q 314,123 314,108 L 314,32 Q 314,17 322,17 L 331,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="373,17 361,21 361,12" style="fill:rgb(0,0,0)"/>
<path d="M337,17L367,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M388,32L500,32A15 15 0 0 0 515 17A15 15 0 0 0 500 2L388,2A15 15 0 0 0 373 17A15 15 0 0 0 388 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="444" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TRANSACTION</text>
<polygon points="551,17 539,21 539,12" style="fill:rgb(0,0,0)"/>
<path d="M515,17L545,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="554" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="444,47 432,51 432,43" style="fill:rgb(0,0,0)"/>
<path d="M337,17 L 344,17 Q 352,17 352,32 L 352,32 Q 352,47 367,47 L 423,47 L 438,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M444,47 L 511,47 Q 526,47 526,32 L 526,32 Q 526,17 533,17 L 541,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M124,17L325,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="162,47 150,51 150,43" style="fill:rgb(0,0,0)"/>
<path d="M139,32 L 139,39 Q 139,47 147,47 L 156,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M177,62L257,62A15 15 0 0 0 272 47L272,47A15 15 0 0 0 257 32L177,32A15 15 0 0 0 162 47L162,47A15 15 0 0 0 177 62Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="217" y="47" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">DEFERRED</text>
<polygon points="295,47 284,51 284,43" style="fill:rgb(0,0,0)"/>
<path d="M272,47L289,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M295,47 L 304,47 Q 314,47 314,40 L 314,32" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="162,85 150,89 150,81" style="fill:rgb(0,0,0)"/>
<path d="M139,32 L 139,70 Q 139,85 147,85 L 156,85" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M177,100L265,100A15 15 0 0 0 281 85A15 15 0 0 0 265 70L177,70A15 15 0 0 0 162 85A15 15 0 0 0 177 100Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="221" y="85" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">IMMEDIATE</text>
<polygon points="304,85 292,89 292,81" style="fill:rgb(0,0,0)"/>
<path d="M281,85L298,85" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M304,85 L 309,85 Q 314,85 314,77 L 314,70" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
</svg>
</div>
</div>
<p><b><a href="syntax/commit-stmt.html">commit-stmt:</a></b>
<button id='x2133' onclick='hideorshow("x2133","x2134")'>hide</button></p>
<div id='x2134' class='imgcontainer'>
<div style="max-width:434px"><svg xmlns='http://www.w3.org/2000/svg' class="pikchr" viewBox="0 0 434.506 72.36">
<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);" />
<polygon points="68,17 56,21 56,12" style="fill:rgb(0,0,0)"/>
<path d="M32,17L62,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M83,32L141,32A15 15 0 0 0 157 17A15 15 0 0 0 141 2L83,2A15 15 0 0 0 68 17A15 15 0 0 0 83 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="112" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">COMMIT</text>
<polygon points="202,17 190,21 190,12" style="fill:rgb(0,0,0)"/>
<path d="M157,17L196,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M202,17L238,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M253,32L365,32A15 15 0 0 0 380 17A15 15 0 0 0 365 2L253,2A15 15 0 0 0 238 17A15 15 0 0 0 253 32Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="309" y="17" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">TRANSACTION</text>
<polygon points="425,17 413,21 413,12" style="fill:rgb(0,0,0)"/>
<path d="M380,17L419,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<circle cx="428" cy="17" r="3.6" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M83,70L104,70A15 15 0 0 0 119 55L119,55A15 15 0 0 0 104 39L83,39A15 15 0 0 0 68 55L68,55A15 15 0 0 0 83 70Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<text x="94" y="55" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">END</text>
<polygon points="68,55 56,59 56,50" style="fill:rgb(0,0,0)"/>
<path d="M32,17 L 39,17 Q 47,17 47,32 L 47,40 Q 47,55 55,55 L 62,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="157,55 145,59 145,50" style="fill:rgb(0,0,0)"/>
<path d="M119,55L151,55" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M157,55 L 164,55 Q 172,55 172,40 L 172,32 Q 172,17 179,17 L 187,17" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<polygon points="309,47 297,51 297,43" style="fill:rgb(0,0,0)"/>
<path d="M202,17 L 209,17 Q 217,17 217,32 L 217,32 Q 217,47 232,47 L 288,47 L 303,47" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
<path d="M309,47 L 380,47 Q 395,47 395,32 L 395,32 Q 395,17 402,17 L 410,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='x2135' onclick='hideorshow("x2135","x2136")'>hide</button></p>
<div id='x2136' 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="transactions"><span>2. </span>Transactions</h1>
<p>
No reads or writes occur except within a transaction.
Any command that accesses the database (basically, any SQL command,
except a few <a href="pragma.html#syntax">PRAGMA</a> statements)
will automatically start a transaction if
one is not already in effect. Automatically started transactions
are committed when the last SQL statement finishes.
</p>
<p>
Transactions can be started manually using the BEGIN
command. Such transactions usually persist until the next
COMMIT or ROLLBACK command. But a transaction will also
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the <a href="lang_conflict.html">ON CONFLICT</a>
clause for additional information about the ROLLBACK
conflict resolution algorithm.
</p>
<p>
END TRANSACTION is an alias for COMMIT.
</p>
<p> Transactions created using BEGIN...COMMIT do not nest.
For nested transactions, use the <a href="lang_savepoint.html">SAVEPOINT</a> and <a href="lang_savepoint.html">RELEASE</a> commands.
The "TO SAVEPOINT <span class='yyterm'>name</span>" clause of the ROLLBACK command shown
in the syntax diagram above is only applicable to <a href="lang_savepoint.html">SAVEPOINT</a>
transactions. An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by <a href="lang_savepoint.html">SAVEPOINT</a> or a prior BEGIN.
The COMMIT command and the ROLLBACK command without the TO clause
work the same on <a href="lang_savepoint.html">SAVEPOINT</a> transactions as they do with transactions
started by BEGIN.</p>
<h2 id="read_transactions_versus_write_transactions"><span>2.1. </span>Read transactions versus write transactions</h2>
<p>SQLite supports multiple simultaneous read transactions
coming from separate database connections, possibly in separate
threads or processes, but only one simultaneous write transaction.
</p><p>
</p><p>A read transaction is used for reading only. A write transaction
allows both reading and writing. A read transaction is started
by a SELECT statement, and a write transaction is started by
statements like CREATE, DELETE, DROP, INSERT, or UPDATE (collectively
"write statements"). If a write statement occurs while
a read transaction is active, then the read transaction is upgraded
to a write transaction if possible. If some other database connection
has already modified the database or is already in the process of
modifying the database, then upgrading to a write transaction is
not possible and the write statement will fail with <a href="rescode.html#busy">SQLITE_BUSY</a>.
</p>
<p>
While a read transaction is active, any changes to the database that
are implemented by separate database connections will not be seen
by the database connection that started the read transaction. If database
connection X is holding a read transaction, it is possible that some
other database connection Y might change the content of the database
while X's transaction is still open, however X will not be able to see
those changes until after the transaction ends. While its read
transaction is active, X will continue to see an historic snapshot of
the database prior to the changes implemented by Y.
</p>
<a name="immediate"></a>
<h2 id="deferred_immediate_and_exclusive_transactions"><span>2.2. </span>DEFERRED, IMMEDIATE, and EXCLUSIVE transactions</h2>
<p>
Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE.
The default transaction behavior is DEFERRED.
</p>
<p>
DEFERRED means that the transaction does not actually
start until the database is first accessed. Internally,
the BEGIN DEFERRED statement merely sets a flag on the database
connection that turns off the automatic commit that would normally
occur when the last statement finishes. This causes the transaction
that is automatically started to persist until an explicit
COMMIT or ROLLBACK or until a rollback is provoked by an error
or an ON CONFLICT ROLLBACK clause. If the first statement after
BEGIN DEFERRED is a SELECT, then a read transaction is started.
Subsequent write statements will upgrade the transaction to a
write transaction if possible, or return SQLITE_BUSY. If the
first statement after BEGIN DEFERRED is a write statement, then
a write transaction is started.
</p>
<p>
IMMEDIATE cause the database connection to start a new write
immediately, without waiting for a write statement. The
BEGIN IMMEDIATE might fail with <a href="rescode.html#busy">SQLITE_BUSY</a> if another write
transaction is already active on another database connection.
</p>
<p>
EXCLUSIVE is similar to IMMEDIATE in that a write transaction
is started immediately. EXCLUSIVE and IMMEDIATE are the same
in <a href="wal.html">WAL mode</a>, but in other journaling modes, EXCLUSIVE prevents
other database connections from reading the database while the
transaction is underway.
</p>
<h2 id="implicit_versus_explicit_transactions"><span>2.3. </span>Implicit versus explicit transactions</h2>
<p>
An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes. A statement finishes when its
last cursor closes, which is guaranteed to happen when the
prepared statement is <a href="c3ref/reset.html">reset</a> or
<a href="c3ref/finalize.html">finalized</a>. Some statements might "finish"
for the purpose of transaction control prior to being reset or finalized,
but there is no guarantee of this. The only way to ensure that a
statement has "finished" is to invoke <a href="c3ref/reset.html">sqlite3_reset()</a> or
<a href="c3ref/finalize.html">sqlite3_finalize()</a> on that statement. An open <a href="c3ref/blob.html">sqlite3_blob</a> used for
incremental BLOB I/O also counts as an unfinished statement.
The <a href="c3ref/blob.html">sqlite3_blob</a> finishes when it is <a href="c3ref/blob_close.html">closed</a>.
</p>
<p>
The explicit COMMIT command runs immediately, even if there are
pending <a href="lang_select.html">SELECT</a> statements. However, if there are pending
write operations, the COMMIT command
will fail with an error code <a href="rescode.html#busy">SQLITE_BUSY</a>.
</p>
<p>
An attempt to execute COMMIT might also result in an <a href="rescode.html#busy">SQLITE_BUSY</a> return code
if an another thread or process has an open read connection.
When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
</p>
<p>
In very old versions of SQLite (before version 3.7.11 - 2012-03-20)
the ROLLBACK will fail with an error code
<a href="rescode.html#busy">SQLITE_BUSY</a> if there are any pending queries. In more recent
versions of SQLite, the ROLLBACK will proceed and pending statements
will often be aborted, causing them to return an <a href="rescode.html#abort">SQLITE_ABORT</a> or
<a href="rescode.html#abort_rollback">SQLITE_ABORT_ROLLBACK</a> error.
In SQLite version 3.8.8 (2015-01-16) and later,
a pending read will continue functioning
after the ROLLBACK as long as the ROLLBACK does not modify the database
schema.
</p>
<p>
If <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is set to OFF (thus disabling the rollback journal
file) then the behavior of the ROLLBACK command is undefined.
</p>
<h1 id="response_to_errors_within_a_transaction"><span>3. </span>Response To Errors Within A Transaction</h1>
<p> If certain kinds of errors occur within a transaction, the
transaction may or may not be rolled back automatically. The
errors that can cause an automatic rollback include:</p>
<ul>
<li> <a href="rescode.html#full">SQLITE_FULL</a>: database or disk full
</li><li> <a href="rescode.html#ioerr">SQLITE_IOERR</a>: disk I/O error
</li><li> <a href="rescode.html#busy">SQLITE_BUSY</a>: database in use by another process
</li><li> <a href="rescode.html#nomem">SQLITE_NOMEM</a>: out of memory
</li></ul>
<p>
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction. However,
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the entire transaction. An application can tell which
course of action SQLite took by using the
<a href="c3ref/get_autocommit.html">sqlite3_get_autocommit()</a> C-language interface.</p>
<p>It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command. If the
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.</p>
<p>Future versions of SQLite may extend the list of errors which
might cause automatic transaction rollback. Future versions of
SQLite might change the error response. In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.</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_transaction.in?m=44e02f89eb1112b5a">2020-07-09 15:12:58</a> UTC </small></i></p>
|
