Debian Codegraph @

www/isolation.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>Isolation In SQLite</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
119
120
121 <h1 align="center">
122 Isolation In SQLite
123 </h1>
124
125 <p>
126 The "isolation" property of a database determines when changes made to
127 the database by one operation become visible to other concurrent operations.
128 </p>
129
130 <h2>Isolation Between Database Connections</h2>
131
132 <p>
133 If the same database is being read and written using two different
134 <a href="c3ref/sqlite3.html">database connections</a> (two different <a href="c3ref/sqlite3.html">sqlite3</a> objects returned by
135 separate calls to <a href="c3ref/open.html">sqlite3_open()</a>) and the two database connections
136 do not have a <a href="sharedcache.html">shared cache</a>, then the reader is only able to
137 see complete committed transactions from the writer. Partial changes
138 by the writer that have not been committed are invisible to the reader.
139 This is true regardless of whether the two database connections are in
140 the same thread, in different threads of the same process, or in
141 different processes. This
142 is the usual and expected behavior for SQL database systems.
143 </p>
144
145 <p>
146 The previous paragraph is also true (separate database connections are
147 isolated from one another) in <a href="sharedcache.html">shared cache mode</a> as long as the
148 <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a> remains turned off. The <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a>
149 is off by default and so if the application does nothing to turn it on,
150 it will remain off. Hence, unless the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a> is used
151 to change the default behavior, changes made by one database connection
152 are invisible to readers on a different database connection sharing the
153 same cache until the writer commits its transaction.
154 </p>
155
156 <p>
157 If two database connections share the same cache and the reader has
158 enabled the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a>, then the reader will be able to
159 see changes made by the writer before the writer transaction commits.
160 The combined use of <a href="sharedcache.html">shared cache mode</a> and the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted pragma</a>
161 is the only way that one database connection can see uncommitted changes
162 on a different database connection. In all other circumstances, separate
163 database connections are completely isolated from one another.
164 </p>
165
166 <p>Except in the case of <a href="sharedcache.html">shared cache</a> database connections with
167 <a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> turned on, all transactions in SQLite show
168 "serializable" isolation. SQLite implements serializable transactions
169 by actually serializing the writes. There can only be a single writer
170 at a time to an SQLite database. There can be multiple database connections
171 open at the same time, and all of those database connections can write
172 to the database file, but they have to take turns. SQLite uses locks
173 to serialize the writes automatically; this is not something that
174 the applications using SQLite need to worry about.</p>
175
176
177 <h2>Isolation And Concurrency</h2>
178
179 <p>
180 SQLite implements isolation and concurrency control (and atomicity) using
181 transient journal files that appear in the same directory as the database file.
182 There are two major "journal modes".
183 The older "rollback mode" corresponds to using the "DELETE", "PERSIST",
184 or "TRUNCATE" options to the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>. In rollback mode,
185 changes are written directly into the database file, while simultaneously
186 a separate rollback journal file is constructed that is able to restore
187 the database to its original state if the transaction rolls back.
188 Rollback mode (specifically DELETE mode, meaning that the rollback journal
189 is deleted from disk at the conclusion of each transaction) is the current
190 default behavior.
191 </p>
192
193 <p>Since <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21),
194 SQLite also supports "<a href="wal.html">WAL mode</a>". In WAL mode,
195 changes are not written to the original database file. Instead, changes
196 go into a separate "write-ahead log" or "WAL" file.
197 Later, after the transaction
198 commits, those changes will be moved from the WAL file back into the
199 original database in an operation called "checkpoint". WAL mode is
200 enabled by running "<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=WAL</a>".
201 </p>
202
203 <p>
204 In rollback mode, SQLite implements isolation by locking the database
205 file and preventing any reads by other database connections
206 while each write transaction is underway.
207 Readers can be active at the beginning of a write, before any content
208 is flushed to disk and while all changes are still held in the writer's
209 private memory space. But before any changes are made to the database file
210 on disk, all readers must be (temporarily) expelled in order to give the writer
211 exclusive access to the database file.
212 Hence, readers are prohibited from seeing incomplete
213 transactions by virtue of being locked out of the database while the
214 transaction is being written to disk. Only after the transaction is
215 completely written and synced to disk and committed are the readers allowed
216 back into the database. Hence readers never get a chance to see partially
217 written changes.
218 </p>
219
220 <p>
221 WAL mode permits simultaneous readers and writers. It can do this because
222 changes do not overwrite the original database file, but rather go
223 into the separate write-ahead log file. That means that readers can continue
224 to read the old, original, unaltered content from the original database file
225 at the same time that the writer is appending to the write-ahead log.
226 In <a href="wal.html">WAL mode</a>, SQLite exhibits "snapshot isolation". When a read transaction
227 starts, that reader continues to see an unchanging "snapshot" of the database
228 file as it existed at the moment in time when the read transaction started.
229 Any write transactions that commit while the read transaction is
230 active are still invisible to the read transaction, because the reader is
231 seeing a snapshot of database file from a prior moment in time.
232 </p>
233
234 <p>
235 An example: Suppose there are two database connections X and Y. X starts
236 a read transaction using <a href="lang_transaction.html">BEGIN</a> followed by one or more <a href="lang_select.html">SELECT</a> statements.
237 Then Y comes along and runs an <a href="lang_update.html">UPDATE</a> statement to modify the database.
238 X can subsequently do a <a href="lang_select.html">SELECT</a> against the records that Y modified but
239 X will see the older unmodified entries because Y's changes are all
240 invisible to X while X is holding a read transaction. If X wants to see
241 the changes that Y made, then X must end its read transaction and
242 start a new one (by running <a href="lang_transaction.html">COMMIT</a> followed by another <a href="lang_transaction.html">BEGIN</a>.)
243 </p>
244
245 <p>
246 Another example: X starts a read transaction using <a href="lang_transaction.html">BEGIN</a> and <a href="lang_select.html">SELECT</a>, then
247 Y makes a changes to the database using <a href="lang_update.html">UPDATE</a>. Then X tries to make a
248 change to the database using <a href="lang_update.html">UPDATE</a>. The attempt by X to escalate its
249 transaction from a read transaction to a write transaction fails with an
250 <a href="rescode.html#busy_snapshot">SQLITE_BUSY_SNAPSHOT</a> error because the snapshot of the database being
251 viewed by X is no longer the latest version of the database. If X were
252 allowed to write, it would fork the history of the database file, which is
253 something SQLite does not support. In order for X to write to the database,
254 it must first release its snapshot (using <a href="lang_transaction.html">ROLLBACK</a> for example) then
255 start a new transaction with a subsequent <a href="lang_transaction.html">BEGIN</a>.
256 </p>
257
258 <p>
259 If X starts a transaction that will initially only read but X knows it
260 will eventually want to write and does not want to be troubled with
261 possible SQLITE_BUSY_SNAPSHOT errors that arise because another connection
262 jumped ahead of it in line, then X can issue <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a> to start
263 its transaction instead of just an ordinary BEGIN. The <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a>
264 command goes ahead and starts a write transaction, and thus blocks all
265 other writers. If the <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a> operation succeeds, then no
266 subsequent operations in that transaction will ever fail with an
267 <a href="rescode.html#busy">SQLITE_BUSY</a> error.
268 </p>
269
270 <h2>No Isolation Between Operations On The Same Database Connection</h2>
271
272 <p>SQLite provides isolation between operations in separate database
273 connections. However, there is no isolation between operations that
274 occur within the same database connection.</p>
275
276 <p>In other words, if X begins a write transaction using <a href="lang_transaction.html#immediate">BEGIN IMMEDIATE</a>
277 then issues one or more <a href="lang_update.html">UPDATE</a>, <a href="lang_delete.html">DELETE</a>, and/or <a href="lang_insert.html">INSERT</a>
278 statements, then those changes are visible to subsequent <a href="lang_select.html">SELECT</a> statements
279 that are evaluated in database connection X. <a href="lang_select.html">SELECT</a> statements on
280 a different database connection Y will show no changes until the X
281 transaction commits. But <a href="lang_select.html">SELECT</a> statements in X will show the changes
282 prior to the commit.</p>
283
284 <p>Within a single database connection X, a SELECT statement always sees all
285 changes to the database that are completed prior to the start of the SELECT
286 statement, whether committed or uncommitted. And the SELECT statement
287 obviously does not see any changes that occur after the SELECT statement
288 completes. But what about changes that occur while the SELECT statement
289 is running? What if a SELECT statement is started and the <a href="c3ref/step.html">sqlite3_step()</a>
290 interface steps through roughly half of its output, then some <a href="lang_update.html">UPDATE</a>
291 statements are run by the application that modify the table that the
292 SELECT statement is reading, then more calls to <a href="c3ref/step.html">sqlite3_step()</a> are made
293 to finish out the SELECT statement? Will the later steps of the SELECT
294 statement see the changes made by the UPDATE or not? The answer is that
295 this behavior is undefined. In particular, whether or not the SELECT statement
296 sees the concurrent changes depends on which release of SQLite is
297 running, the schema of the database file, whether or not <a href="lang_analyze.html">ANALYZE</a> has
298 been run, and the details of the query. In some cases, it might depend
299 on the content of the database file, too. There is no good way to know whether
300 or not a SELECT statement will see changes that were made to the database
301 by the same database connection after the SELECT statement was started.
302 And hence, developers should diligently avoid writing applications
303 that make assumptions about what will occur in that circumstance.</p>
304
305 <p>
306 If an application issues a SELECT statement on a single table like
307 "<i>SELECT rowid, * FROM table WHERE ...</i>" and starts stepping through
308 the output of that statement using <a href="c3ref/step.html">sqlite3_step()</a> and examining each
309 row, then it is safe for the application to delete the current row or
310 any prior row using "DELETE FROM table WHERE rowid=?". It is also safe
311 (in the sense that it will not harm the database) for the application to
312 delete a row that expected to appear later in the query but has not
313 appeared yet. If a future row is deleted, however, it might happen that
314 the row turns up after a subsequent sqlite3_step(), even after it has
315 allegedly been deleted. Or it might not. That behavior is undefined.
316 The application can
317 also INSERT new rows into the table while the SELECT statement is
318 running, but whether or not the new rows appear
319 in subsequent sqlite3_step()s of the query is undefined. And the application
320 can UPDATE the current row or any prior row, though doing so might cause
321 that row to reappear in a subsequent sqlite3_step(). As long as the
322 application is prepared to deal with these ambiguities, the operations
323 themselves are safe and will not harm the database file.</p>
324
325 <p>
326 For the purposes of the previous two paragraphs, two database connections
327 that have the same <a href="sharedcache.html">shared cache</a> and which have enabled
328 <a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> are considered to be the same database connection.
329 </p>
330
331 <h2>Summary</h2>
332
333 <ol>
334 <li><p>
335 Transactions in SQLite are SERIALIZABLE.
336 </p>
337
338 <li><p>
339 Changes made in one database connection are invisible to all other database
340 connections prior to commit.
341 </p>
342
343 <li><p>
344 A query sees all changes that are completed on the same database connection
345 prior to the start of the query, regardless of whether or not those changes
346 have been committed.
347 </p>
348
349 <li><p>
350 If changes occur on the same database connection after a query
351 starts running but before the query completes, then it is undefined whether
352 or not the query will see those changes.
353 </p>
354
355 <li><p>
356 If changes occur on the same database connection after a query
357 starts running but before the query completes, then the query might return
358 a changed row more than once, or it might return a row that was previously
359 deleted.
360 </p>
361
362 <li><p>
363 For the purposes of the previous four items, two database connections that
364 use the same <a href="sharedcache.html">shared cache</a> and which enable <a href="pragma.html#pragma_read_uncommitted">PRAGMA read_uncommitted</a> are
365 considered to be the same database connection, not separate database
366 connections.
367 </p>
368 </ol>
369 <p align="center"><small><i>This page was last updated on 2022-04-18 02:55:50Z </small></i></p>
370