File: sharedcache.html

package info (click to toggle)
sqlite3 3.46.1-8
links: PTS
area: main
in suites: forky, sid
size: 150,944 kB
sloc: ansic: 281,304; tcl: 19,550; javascript: 12,978; sh: 10,768; java: 8,151; makefile: 1,689; yacc: 1,644; cpp: 440; cs: 307; sql: 45
file content (406 lines) | stat: -rw-r--r-- 17,068 bytes
parent folder | download | duplicates (2)
<!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">&#x25ba;</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 = "&#x25bc;";
} else {
sub.style.display = "none";
mk.innerHTML = "&#x25ba;";
}
}
</script>
</div>





<a name="sqlite_shared_cache_mode"></a>

<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 = &lt;boolean&gt;;

  /* 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=4a965f6f8f">2023-01-02 14:22:42</a> UTC </small></i></p>