Debian Codegraph @

www/sharedcache.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>SQLite Shared-Cache Mode</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 SQLite Shared-Cache Mode
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#sqlite_shared_cache_mode">1. SQLite Shared-Cache Mode</a></div>
126 <div class="fancy-toc2"><a href="#use_of_shared_cache_is_discouraged">1.1. Use of shared-cache is discouraged</a></div>
127 <div class="fancy-toc1"><a href="#shared_cache_locking_model">2. Shared-Cache Locking Model</a></div>
128 <div class="fancy-toc2"><a href="#transaction_level_locking">2.1. Transaction Level Locking</a></div>
129 <div class="fancy-toc2"><a href="#table_level_locking">2.2. Table Level Locking</a></div>
130 <div class="fancy-toc3"><a href="#read_uncommitted_isolation_mode">2.2.1. Read-Uncommitted Isolation Mode</a></div>
131 <div class="fancy-toc2"><a href="#schema_sqlite_schema_level_locking">2.3. Schema (sqlite_schema) Level Locking</a></div>
132 <div class="fancy-toc1"><a href="#thread_related_issues">3. Thread Related Issues</a></div>
133 <div class="fancy-toc1"><a href="#shared_cache_and_virtual_tables">4. Shared Cache And Virtual Tables</a></div>
134 <div class="fancy-toc1"><a href="#enabling_shared_cache_mode">5. Enabling Shared-Cache Mode</a></div>
135 <div class="fancy-toc1"><a href="#shared_cache_and_in_memory_databases">6. Shared Cache And In-Memory Databases</a></div>
136 </div>
137 </details>
138 </div>
139
140
141
142
143
144 <a name="sqlite_shared_cache_mode"></a>
145
146 <h1 id="sqlite_shared_cache_mode"><span>1. </span>SQLite Shared-Cache Mode</h1>
147
148 <p>Starting with <a href="releaselog/3_3_0.html">version 3.3.0</a> (2006-01-11),
149 SQLite includes a special "shared-cache"
150 mode (disabled by default) intended for use in embedded servers. If
151 shared-cache mode is enabled and a thread establishes multiple connections
152 to the same database, the connections share a single data and schema cache.
153 This can significantly reduce the quantity of memory and IO required by
154 the system.</p>
155
156 <p>In <a href="releaselog/3_5_0.html">version 3.5.0</a> (2007-09-04),
157 shared-cache mode was modified so that the same
158 cache can be shared across an entire process rather than just within
159 a single thread. Prior to this change, there were restrictions on
160 passing database connections between threads. Those restrictions were
161 dropped in 3.5.0 update. This document describes shared-cache mode
162 as of version 3.5.0.</p>
163
164 <p>Shared-cache mode changes the semantics
165 of the locking model in some cases. The details are described by
166 this document. A basic understanding of the normal SQLite locking model (see
167 <a href="lockingv3.html">File Locking And Concurrency In SQLite Version 3</a>
168 for details) is assumed.</p>
169
170 <a name="dontuse"></a>
171
172 <h2 id="use_of_shared_cache_is_discouraged"><span>1.1. </span>Use of shared-cache is discouraged</h2>
173
174 <p>Shared-cache mode is an obsolete feature. The use of shared-cache mode
175 is discouraged. Most use cases for shared-cache are better served by
176 <a href="wal.html">WAL mode</a>.
177
178 </p><p>Shared-cache mode was invented in 2006 at the request of developers
179 of <a href="https://en.wikipedia.org/wiki/Symbian">Symbian</a>. Their problem was that
180 if the contacts database on the phone was being synced, that would lock the
181 database file. Then if a call came in, the database lock would prevent them
182 from querying the contacts database in order to find the appropriate
183 ring-tone for the incoming call, or a photo of the caller to show on screen,
184 and so forth.
185 <a href="wal.html">WAL mode</a> (circa 2010) is a better solution to this problem as it permits
186 simultaneous access without breaking transaction isolation.
187
188 </p><p>Applications that build their own copy of SQLite from source code
189 are encouraged to use the <a href="compile.html#omit_shared_cache">-DSQLITE_OMIT_SHARED_CACHE</a> compile-time option,
190 as the resulting binary will be both smaller and faster.
191
192 </p><p>The shared-cache interfaces described here will continue to be supported
193 in SQLite, to insure full backwards compatibility. However, the use of
194 shared-cache is discouraged.
195
196 </p><h1 id="shared_cache_locking_model"><span>2. </span>Shared-Cache Locking Model</h1>
197
198 <p>Externally, from the point of view of another process or thread, two
199 or more <a href="c3ref/sqlite3.html">database connections</a> using a shared-cache appear as a single
200 connection. The locking protocol used to arbitrate between multiple
201 shared-caches or regular database users is described elsewhere.
202 </p>
203
204 <table style="margin:auto">
205 <tr><td>
206 <img src="images/shared.gif">
207
208 </td></tr></table>
209 <p style="font-style:italic;text-align:center">Figure 1</p>
210
211 <p>Figure 1 depicts an example runtime configuration where three
212 database connections have been established. Connection 1 is a normal
213 SQLite database connection. Connections 2 and 3 share a cache
214 The normal locking
215 protocol is used to serialize database access between connection 1 and
216 the shared cache. The internal protocol used to serialize (or not, see
217 "Read-Uncommitted Isolation Mode" below) access to the shared-cache by
218 connections 2 and 3 is described in the remainder of this section.
219 </p>
220
221 <p>There are three levels to the shared-cache locking model,
222 transaction level locking, table level locking and schema level locking.
223 They are described in the following three sub-sections.</p>
224
225 <h2 id="transaction_level_locking"><span>2.1. </span>Transaction Level Locking</h2>
226
227 <p>SQLite connections can open two kinds of transactions, read and write
228 transactions. This is not done explicitly, a transaction is implicitly a
229 read-transaction until it first writes to a database table, at which point
230 it becomes a write-transaction.
231 </p>
232 <p>At most one connection to a single shared cache may open a
233 write transaction at any one time. This may co-exist with any number of read
234 transactions.
235 </p>
236
237 <h2 id="table_level_locking"><span>2.2. </span>Table Level Locking</h2>
238
239 <p>When two or more connections use a shared-cache, locks are used to
240 serialize concurrent access attempts on a per-table basis. Tables support
241 two types of locks, "read-locks" and "write-locks". Locks are granted to
242 connections - at any one time, each database connection has either a
243 read-lock, write-lock or no lock on each database table.
244 </p>
245
246 <p>At any one time, a single table may have any number of active read-locks
247 or a single active write lock. To read data from a table, a connection must
248 first obtain a read-lock. To write to a table, a connection must obtain a
249 write-lock on that table. If a required table lock cannot be obtained,
250 the query fails and SQLITE_LOCKED is returned to the caller.
251 </p>
252
253 <p>Once a connection obtains a table lock, it is not released until the
254 current transaction (read or write) is concluded.
255 </p>
256
257 <h3 id="read_uncommitted_isolation_mode"><span>2.2.1. </span>Read-Uncommitted Isolation Mode</h3>
258
259 <p>The behaviour described above may be modified slightly by using the
260 <a href="pragma.html#pragma_read_uncommitted">read_uncommitted</a> pragma to change the isolation level from serialized
261 (the default), to read-uncommitted.</p>
262
263 <p> A database connection in read-uncommitted mode does not attempt
264 to obtain read-locks before reading from database tables as described
265 above. This can lead to inconsistent query results if another database
266 connection modifies a table while it is being read, but it also means that
267 a read-transaction opened by a connection in read-uncommitted mode can
268 neither block nor be blocked by any other connection.</p>
269
270 <p>Read-uncommitted mode has no effect on the locks required to write to
271 database tables (i.e. read-uncommitted connections must still obtain
272 write-locks and hence database writes may still block or be blocked).
273 Also, read-uncommitted mode has no effect on the <a href="schematab.html">sqlite_schema</a>
274 locks required by the rules enumerated below (see section
275 "Schema (sqlite_schema) Level Locking").
276 </p>
277
278 <blockquote><pre>
279 /* Set the value of the read-uncommitted flag:
280 **
281 ** True -> Set the connection to read-uncommitted mode.
282 ** False -> Set the connection to serialized (the default) mode.
283 */
284 PRAGMA read_uncommitted = &lt;boolean&gt;;
285
286 /* Retrieve the current value of the read-uncommitted flag */
287 PRAGMA read_uncommitted;
288 </pre></blockquote>
289
290 <h2 id="schema_sqlite_schema_level_locking"><span>2.3. </span>Schema (sqlite_schema) Level Locking</h2>
291
292 <p>The <a href="schematab.html">sqlite_schema table</a> supports shared-cache read and write
293 locks in the same way as all other database tables (see description
294 above). The following special rules also apply:
295 </p>
296
297 <ul>
298 <li>A connection must obtain a read-lock on <i>sqlite_schema</i> before
299 accessing any database tables or obtaining any other read or write locks.</li>
300 <li>Before executing a statement that modifies the database schema (i.e.
301 a CREATE or DROP TABLE statement), a connection must obtain a write-lock on
302 <i>sqlite_schema</i>.
303 </li>
304 <li>A connection may not compile an SQL statement if any other connection
305 is holding a write-lock on the <i>sqlite_schema</i> table of any attached
306 database (including the default database, "main").
307 </li>
308 </ul>
309
310 <h1 id="thread_related_issues"><span>3. </span>Thread Related Issues</h1>
311
312 <p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled,
313 a database connection may only be
314 used by the thread that called <a href="c3ref/open.html">sqlite3_open()</a> to create it.
315 And a connection could only share cache with another connection in the
316 same thread.
317 These restrictions were dropped beginning with SQLite
318 <a href="releaselog/3_5_0.html">version 3.5.0</a> (2007-09-04).
319 </p>
320
321 <h1 id="shared_cache_and_virtual_tables"><span>4. </span>Shared Cache And Virtual Tables</h1>
322
323 <p>
324 In older versions of SQLite,
325 shared cache mode could not be used together with virtual tables.
326 This restriction was removed in SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a> (2009-08-10).
327
328 </p><h1 id="enabling_shared_cache_mode"><span>5. </span>Enabling Shared-Cache Mode</h1>
329
330 <p>Shared-cache mode is enabled on a per-process basis. Using the C
331 interface, the following API can be used to globally enable or disable
332 shared-cache mode:
333 </p>
334
335 <blockquote><pre>
336 int sqlite3_enable_shared_cache(int);
337 </pre></blockquote>
338
339 <p>Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> affects subsequent database
340 connections created using <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or
341 <a href="c3ref/open.html">sqlite3_open_v2()</a>. Database connections that already exist are
342 unaffected. Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> overrides
343 all previous calls within the same process.
344 </p>
345
346 <p>Individual database connections created using <a href="c3ref/open.html">sqlite3_open_v2()</a> can
347 choose to participate or not participate in shared cache mode by using
348 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 for the
349 third parameter. The use of either of these flags overrides the
350 global shared cache mode setting established by <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a>.
351 No more than one of the flags should be used; if both SQLITE_OPEN_SHAREDCACHE
352 and SQLITE_OPEN_PRIVATECACHE flags are used in the third argument to
353 <a href="c3ref/open.html">sqlite3_open_v2()</a> then the behavior is undefined.</p>
354
355 <p>When <a href="uri.html">URI filenames</a> are used, the "cache" query parameter can be used
356 to specify whether or not the database will use shared cache. Use
357 "cache=shared" to enable shared cache and "cache=private" to disable
358 shared cache. The ability to use URI query parameters to specify the
359 cache sharing behavior of a database connection allows cache sharing to
360 be controlled in <a href="lang_attach.html">ATTACH</a> statements. For example:</p>
361
362 <blockquote><pre>
363 ATTACH 'file:aux.db?cache=shared' AS aux;
364 </pre></blockquote>
365
366 <a name="inmemsharedcache"></a>
367
368 <h1 id="shared_cache_and_in_memory_databases"><span>6. </span>Shared Cache And In-Memory Databases</h1>
369
370 <p>
371 Beginning with SQLite <a href="releaselog/3_7_13.html">version 3.7.13</a> (2012-06-11),
372 shared cache can be used on
373 <a href="inmemorydb.html">in-memory databases</a>, provided that the database is created using
374 a <a href="uri.html">URI filename</a>. For backwards compatibility, shared cache is always
375 disabled for in-memory
376 databases if the unadorned name ":memory:" is used to open the database.
377 Prior to version 3.7.13, shared cache was always
378 disabled for in-memory databases regardless of the database name used,
379 current system shared cache setting, or query parameters or flags.
380 </p>
381
382 <p>
383 Enabling shared-cache for an in-memory database allows two or more
384 database connections in the same process to have access to the same
385 in-memory database. An in-memory database in shared cache is automatically
386 deleted and memory is reclaimed when the last connection to that database
387 closes.
388 </p>
389 <p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p>
390