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
|
<!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>SQLite Shared-Cache Mode</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">
SQLite Shared-Cache Mode
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#sqlite_shared_cache_mode">1. SQLite Shared-Cache Mode</a></div>
<div class="fancy-toc2"><a href="#use_of_shared_cache_is_discouraged">1.1. Use of shared-cache is discouraged</a></div>
<div class="fancy-toc1"><a href="#shared_cache_locking_model">2. Shared-Cache Locking Model</a></div>
<div class="fancy-toc2"><a href="#transaction_level_locking">2.1. Transaction Level Locking</a></div>
<div class="fancy-toc2"><a href="#table_level_locking">2.2. Table Level Locking</a></div>
<div class="fancy-toc3"><a href="#read_uncommitted_isolation_mode">2.2.1. Read-Uncommitted Isolation Mode</a></div>
<div class="fancy-toc2"><a href="#schema_sqlite_schema_level_locking">2.3. Schema (sqlite_schema) Level Locking</a></div>
<div class="fancy-toc1"><a href="#thread_related_issues">3. Thread Related Issues</a></div>
<div class="fancy-toc1"><a href="#shared_cache_and_virtual_tables">4. Shared Cache And Virtual Tables</a></div>
<div class="fancy-toc1"><a href="#enabling_shared_cache_mode">5. Enabling Shared-Cache Mode</a></div>
<div class="fancy-toc1"><a href="#shared_cache_and_in_memory_databases">6. Shared Cache And In-Memory Databases</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="sqlite_shared_cache_mode"><span>1. </span>SQLite Shared-Cache Mode</h1>
<p>Starting with <a href="releaselog/3_3_0.html">version 3.3.0</a> (2006-01-11),
SQLite includes a special "shared-cache"
mode (disabled by default) intended for use in embedded servers. If
shared-cache mode is enabled and a thread establishes multiple connections
to the same database, the connections share a single data and schema cache.
This can significantly reduce the quantity of memory and IO required by
the system.</p>
<p>In <a href="releaselog/3_5_0.html">version 3.5.0</a> (2007-09-04),
shared-cache mode was modified so that the same
cache can be shared across an entire process rather than just within
a single thread. Prior to this change, there were restrictions on
passing database connections between threads. Those restrictions were
dropped in 3.5.0 update. This document describes shared-cache mode
as of version 3.5.0.</p>
<p>Shared-cache mode changes the semantics
of the locking model in some cases. The details are described by
this document. A basic understanding of the normal SQLite locking model (see
<a href="lockingv3.html">File Locking And Concurrency In SQLite Version 3</a>
for details) is assumed.</p>
<a name="dontuse"></a>
<h2 id="use_of_shared_cache_is_discouraged"><span>1.1. </span>Use of shared-cache is discouraged</h2>
<p>Shared-cache mode is an obsolete feature. The use of shared-cache mode
is discouraged. Most use cases for shared-cache are better served by
<a href="wal.html">WAL mode</a>.
</p><p>Shared-cache mode was invented in 2006 at the request of developers
of <a href="https://en.wikipedia.org/wiki/Symbian">Symbian</a>. Their problem was that
if the contacts database on the phone was being synced, that would lock the
database file. Then if a call came in, the database lock would prevent them
from querying the contacts database in order to find the appropriate
ring-tone for the incoming call, or a photo of the caller to show on screen,
and so forth.
<a href="wal.html">WAL mode</a> (circa 2010) is a better solution to this problem as it permits
simultaneous access without breaking transaction isolation.
</p><p>Applications that build their own copy of SQLite from source code
are encouraged to use the <a href="compile.html#omit_shared_cache">-DSQLITE_OMIT_SHARED_CACHE</a> compile-time option,
as the resulting binary will be both smaller and faster.
</p><p>The shared-cache interfaces described here will continue to be supported
in SQLite, to insure full backwards compatibility. However, the use of
shared-cache is discouraged.
</p><h1 id="shared_cache_locking_model"><span>2. </span>Shared-Cache Locking Model</h1>
<p>Externally, from the point of view of another process or thread, two
or more <a href="c3ref/sqlite3.html">database connections</a> using a shared-cache appear as a single
connection. The locking protocol used to arbitrate between multiple
shared-caches or regular database users is described elsewhere.
</p>
<table style="margin:auto">
<tr><td>
<img src="images/shared.gif">
</td></tr></table>
<p style="font-style:italic;text-align:center">Figure 1</p>
<p>Figure 1 depicts an example runtime configuration where three
database connections have been established. Connection 1 is a normal
SQLite database connection. Connections 2 and 3 share a cache
The normal locking
protocol is used to serialize database access between connection 1 and
the shared cache. The internal protocol used to serialize (or not, see
"Read-Uncommitted Isolation Mode" below) access to the shared-cache by
connections 2 and 3 is described in the remainder of this section.
</p>
<p>There are three levels to the shared-cache locking model,
transaction level locking, table level locking and schema level locking.
They are described in the following three sub-sections.</p>
<h2 id="transaction_level_locking"><span>2.1. </span>Transaction Level Locking</h2>
<p>SQLite connections can open two kinds of transactions, read and write
transactions. This is not done explicitly, a transaction is implicitly a
read-transaction until it first writes to a database table, at which point
it becomes a write-transaction.
</p>
<p>At most one connection to a single shared cache may open a
write transaction at any one time. This may co-exist with any number of read
transactions.
</p>
<h2 id="table_level_locking"><span>2.2. </span>Table Level Locking</h2>
<p>When two or more connections use a shared-cache, locks are used to
serialize concurrent access attempts on a per-table basis. Tables support
two types of locks, "read-locks" and "write-locks". Locks are granted to
connections - at any one time, each database connection has either a
read-lock, write-lock or no lock on each database table.
</p>
<p>At any one time, a single table may have any number of active read-locks
or a single active write lock. To read data from a table, a connection must
first obtain a read-lock. To write to a table, a connection must obtain a
write-lock on that table. If a required table lock cannot be obtained,
the query fails and SQLITE_LOCKED is returned to the caller.
</p>
<p>Once a connection obtains a table lock, it is not released until the
current transaction (read or write) is concluded.
</p>
<h3 id="read_uncommitted_isolation_mode"><span>2.2.1. </span>Read-Uncommitted Isolation Mode</h3>
<p>The behaviour described above may be modified slightly by using the
<a href="pragma.html#pragma_read_uncommitted">read_uncommitted</a> pragma to change the isolation level from serialized
(the default), to read-uncommitted.</p>
<p> A database connection in read-uncommitted mode does not attempt
to obtain read-locks before reading from database tables as described
above. This can lead to inconsistent query results if another database
connection modifies a table while it is being read, but it also means that
a read-transaction opened by a connection in read-uncommitted mode can
neither block nor be blocked by any other connection.</p>
<p>Read-uncommitted mode has no effect on the locks required to write to
database tables (i.e. read-uncommitted connections must still obtain
write-locks and hence database writes may still block or be blocked).
Also, read-uncommitted mode has no effect on the <a href="schematab.html">sqlite_schema</a>
locks required by the rules enumerated below (see section
"Schema (sqlite_schema) Level Locking").
</p>
<blockquote><pre>
/* Set the value of the read-uncommitted flag:
**
** True -> Set the connection to read-uncommitted mode.
** False -> Set the connection to serialized (the default) mode.
*/
PRAGMA read_uncommitted = <boolean>;
/* Retrieve the current value of the read-uncommitted flag */
PRAGMA read_uncommitted;
</pre></blockquote>
<h2 id="schema_sqlite_schema_level_locking"><span>2.3. </span>Schema (sqlite_schema) Level Locking</h2>
<p>The <a href="schematab.html">sqlite_schema table</a> supports shared-cache read and write
locks in the same way as all other database tables (see description
above). The following special rules also apply:
</p>
<ul>
<li>A connection must obtain a read-lock on <i>sqlite_schema</i> before
accessing any database tables or obtaining any other read or write locks.</li>
<li>Before executing a statement that modifies the database schema (i.e.
a CREATE or DROP TABLE statement), a connection must obtain a write-lock on
<i>sqlite_schema</i>.
</li>
<li>A connection may not compile an SQL statement if any other connection
is holding a write-lock on the <i>sqlite_schema</i> table of any attached
database (including the default database, "main").
</li>
</ul>
<h1 id="thread_related_issues"><span>3. </span>Thread Related Issues</h1>
<p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled,
a database connection may only be
used by the thread that called <a href="c3ref/open.html">sqlite3_open()</a> to create it.
And a connection could only share cache with another connection in the
same thread.
These restrictions were dropped beginning with SQLite
<a href="releaselog/3_5_0.html">version 3.5.0</a> (2007-09-04).
</p>
<h1 id="shared_cache_and_virtual_tables"><span>4. </span>Shared Cache And Virtual Tables</h1>
<p>
In older versions of SQLite,
shared cache mode could not be used together with virtual tables.
This restriction was removed in SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a> (2009-08-10).
</p><h1 id="enabling_shared_cache_mode"><span>5. </span>Enabling Shared-Cache Mode</h1>
<p>Shared-cache mode is enabled on a per-process basis. Using the C
interface, the following API can be used to globally enable or disable
shared-cache mode:
</p>
<blockquote><pre>
int sqlite3_enable_shared_cache(int);
</pre></blockquote>
<p>Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> affects subsequent database
connections created using <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
<a href="c3ref/open.html">sqlite3_open_v2()</a>. Database connections that already exist are
unaffected. Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> overrides
all previous calls within the same process.
</p>
<p>Individual database connections created using <a href="c3ref/open.html">sqlite3_open_v2()</a> can
choose to participate or not participate in shared cache mode by using
the <a href="c3ref/c_open_autoproxy.html">SQLITE_OPEN_SHAREDCACHE</a> or <a href="c3ref/c_open_autoproxy.html">SQLITE_OPEN_PRIVATECACHE</a> flags the
third parameter. The use of either of these flags overrides the
global shared cache mode setting established by <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a>.
No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE
and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to
<a href="c3ref/open.html">sqlite3_open_v2()</a> then the behavior is undefined.</p>
<p>When <a href="uri.html">URI filenames</a> are used, the "cache" query parameter can be used
to specify whether or not the database will use shared cache. Use
"cache=shared" to enable shared cache and "cache=private" to disable
shared cache. The ability to use URI query parameters to specify the
cache sharing behavior of a database connection allows cache sharing to
be controlled in <a href="lang_attach.html">ATTACH</a> statements. For example:</p>
<blockquote><pre>
ATTACH 'file:aux.db?cache=shared' AS aux;
</pre></blockquote>
<a name="inmemsharedcache"></a>
<h1 id="shared_cache_and_in_memory_databases"><span>6. </span>Shared Cache And In-Memory Databases</h1>
<p>
Beginning with SQLite <a href="releaselog/3_7_13.html">version 3.7.13</a> (2012-06-11),
shared cache can be used on
<a href="inmemorydb.html">in-memory databases</a>, provided that the database is created using
a <a href="uri.html">URI filename</a>. For backwards compatibility, shared cache is always
disabled for in-memory
databases if the unadorned name ":memory:" is used to open the database.
Prior to version 3.7.13, shared cache was always
disabled for in-memory databases regardless of the database name used,
current system shared cache setting, or query parameters or flags.
</p>
<p>
Enabling shared-cache for an in-memory database allows two or more
database connections in the same process to have access to the same
in-memory database. An in-memory database in shared cache is automatically
deleted and memory is reclaimed when the last connection to that database
closes.
</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/sharedcache.in?m=2e0ac9c477d633afd">2022-08-15 11:37:22</a> UTC </small></i></p>
|
