1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>SQLite Shared-Cache Mode</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
125
<h2>1.0 SQLite Shared-Cache Mode</h2>
127
<p>Starting with version 3.3.0, SQLite includes a special "shared-cache"
128
mode (disabled by default) intended for use in embedded servers. If
129
shared-cache mode is enabled and a thread establishes multiple connections
130
to the same database, the connections share a single data and schema cache.
131
This can significantly reduce the quantity of memory and IO required by
134
<p>In version 3.5.0, shared-cache mode was modified so that the same
135
cache can be shared across an entire process rather than just within
136
a single thread. Prior to this change, there were restrictions on
137
passing database connections between threads. Those restrictions were
138
dropped in 3.5.0 update. This document describes shared-cache mode
139
as of version 3.5.0.</p>
141
<p>Shared-cache mode changes the semantics
142
of the locking model in some cases. The details are described by
143
this document. A basic understanding of the normal SQLite locking model (see
144
<a href="lockingv3.html">File Locking And Concurrency In SQLite Version 3</a>
145
for details) is assumed.</p>
147
<h2>2.0 Shared-Cache Locking Model</h2>
149
<p>Externally, from the point of view of another process or thread, two
150
or more <a href="c3ref/sqlite3.html">database connections</a> using a shared-cache appear as a single
151
connection. The locking protocol used to arbitrate between multiple
152
shared-caches or regular database users is described elsewhere.
155
<table style="margin:auto">
157
<img src="images/shared.gif">
159
+--------------+ +--------------+
160
| Connection 2 | | Connection 3 |
161
+--------------+ +--------------+
164
+--------------+ +--------------+
165
| Connection 1 | | Shared cache |
166
+--------------+ +--------------+
174
<p style="font-style:italic;text-align:center">Figure 1</p>
176
<p>Figure 1 depicts an example runtime configuration where three
177
database connections have been established. Connection 1 is a normal
178
SQLite database connection. Connections 2 and 3 share a cache
180
protocol is used to serialize database access between connection 1 and
181
the shared cache. The internal protocol used to serialize (or not, see
182
"Read-Uncommitted Isolation Mode" below) access to the shared-cache by
183
connections 2 and 3 is described in the remainder of this section.
186
<p>There are three levels to the shared-cache locking model,
187
transaction level locking, table level locking and schema level locking.
188
They are described in the following three sub-sections.</p>
190
<h3>2.1 Transaction Level Locking</h3>
192
<p>SQLite connections can open two kinds of transactions, read and write
193
transactions. This is not done explicitly, a transaction is implicitly a
194
read-transaction until it first writes to a database table, at which point
195
it becomes a write-transaction.
197
<p>At most one connection to a single shared cache may open a
198
write transaction at any one time. This may co-exist with any number of read
202
<h3>2.2 Table Level Locking</h3>
204
<p>When two or more connections use a shared-cache, locks are used to
205
serialize concurrent access attempts on a per-table basis. Tables support
206
two types of locks, "read-locks" and "write-locks". Locks are granted to
207
connections - at any one time, each database connection has either a
208
read-lock, write-lock or no lock on each database table.
211
<p>At any one time, a single table may have any number of active read-locks
212
or a single active write lock. To read data a table, a connection must
213
first obtain a read-lock. To write to a table, a connection must obtain a
214
write-lock on that table. If a required table lock cannot be obtained,
215
the query fails and SQLITE_LOCKED is returned to the caller.
218
<p>Once a connection obtains a table lock, it is not released until the
219
current transaction (read or write) is concluded.
222
<h4>2.2.1 Read-Uncommitted Isolation Mode</h4>
224
<p>The behaviour described above may be modified slightly by using the
225
<a href="pragma.html#pragma_read_uncommitted">read_uncommitted</a> pragma to change the isolation level from serialized
226
(the default), to read-uncommitted.</p>
228
<p> A database connection in read-uncommitted mode does not attempt
229
to obtain read-locks before reading from database tables as described
230
above. This can lead to inconsistent query results if another database
231
connection modifies a table while it is being read, but it also means that
232
a read-transaction opened by a connection in read-uncommitted mode can
233
neither block nor be blocked by any other connection.</p>
235
<p>Read-uncommitted mode has no effect on the locks required to write to
236
database tables (i.e. read-uncommitted connections must still obtain
237
write-locks and hence database writes may still block or be blocked).
238
Also, read-uncommitted mode has no effect on the <i>sqlite_master</i>
239
locks required by the rules enumerated below (see section
240
"Schema (sqlite_master) Level Locking").
244
/* Set the value of the read-uncommitted flag:
246
** True -> Set the connection to read-uncommitted mode.
247
** False -> Set the connection to serialized (the default) mode.
249
PRAGMA read_uncommitted = <boolean>;
251
/* Retrieve the current value of the read-uncommitted flag */
252
PRAGMA read_uncommitted;
255
<h3>2.3 Schema (sqlite_master) Level Locking</h3>
257
<p>The <i>sqlite_master</i> table supports shared-cache read and write
258
locks in the same way as all other database tables (see description
259
above). The following special rules also apply:
263
<li>A connection must obtain a read-lock on <i>sqlite_master</i> before
264
accessing any database tables or obtaining any other read or write locks.</li>
265
<li>Before executing a statement that modifies the database schema (i.e.
266
a CREATE or DROP TABLE statement), a connection must obtain a write-lock on
267
<i>sqlite_master</i>.
269
<li>A connection may not compile an SQL statement if any other connection
270
is holding a write-lock on the <i>sqlite_master</i> table of any attached
271
database (including the default database, "main").
275
<h2>3.0 Thread Related Issues</h2>
277
<p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled,
278
a database connection may only be
279
used by the thread that called <a href="c3ref/open.html">sqlite3_open()</a> to create it.
280
And a connection could only share cache with another connection in the
282
These restrictions were dropped beginning with SQLite version 3.5.0.
285
<h2>4.0 Shared Cache And Virtual Tables</h2>
288
In older versions of SQLite,
289
shared cache mode could not be used together with virtual tables.
290
This restriction was removed in SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a>.
292
<h2>5.0 Enabling Shared-Cache Mode</h2>
294
<p>Shared-cache mode is enabled on a per-process basis. Using the C
295
interface, the following API can be used to enable or disable shared-cache
296
mode for the calling thread:
300
int sqlite3_enable_shared_cache(int);
303
<p>Each call <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> effects subsequent database
304
connections created using <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
305
<a href="c3ref/open.html">sqlite3_open_v2()</a>. Database connections that already exist are
306
unaffected. Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> overrides
307
all previous calls within the same process.
310
<p>Individual database connections created using <a href="c3ref/open.html">sqlite3_open_v2()</a> can
311
choose to participate or not participate in shared cache mode by using
312
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
313
third parameter. The use of either of these flags overrides the
314
global shared cache mode setting established by <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a>.
315
No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE
316
and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to
317
<a href="c3ref/open.html">sqlite3_open_v2()</a> then the behavior is undefined.</p>
319
<p>When <a href="uri.html">URI filenames</a> are used, the "cache" query parameter can be used
320
to specify whether or not the database will use shared cache. Use
321
"cache=shared" to enable shared cache and "cache=private" to disable
322
shared cache. The ability to use URI query parameters to specify the
323
cache sharing behavior of a database connection allows cache sharing to
324
be controlled in <a href="lang_attach.html">ATTACH</a> statements. For example:</p>
327
ATTACH 'file:aux.db?cache=shared' AS aux;
330
<a name="inmemsharedcache"></a>
331
<h2>6.0 Shared Cache And In-Memory Databases</h2>
334
Beginning with SQLite <a href="releaselog/3_7_13.html">version 3.7.13</a>, shared cache can be used on
335
<a href="inmemorydb.html">in-memory databases</a>, provided that the database is created using
336
a <a href="uri.html">URI filename</a>. For backwards compatibility, shared cache is always
337
disable for in-memory
338
databases if the unadorned name ":memory:" is used to open the database.
339
Prior to version 3.7.13, shared cache was always
340
disabled for in-memory databases regardless of the database name used,
341
current system shared cache setting, or query parameters or flags.
345
Enabling shared-cache for an in-mmeory database allows two or more
346
database connections in the same process to have access to the same
347
in-memory database. An in-memory database in shared cache is automatically
348
deleted and memory is reclaimed when the last connection to that database
added
removed
sharedcache.html
