diff options
Diffstat (limited to 'www/sharedcache.html')
| -rw-r--r-- | www/sharedcache.html | 406 |
1 files changed, 406 insertions, 0 deletions
diff --git a/www/sharedcache.html b/www/sharedcache.html new file mode 100644 index 0000000..c82f0a1 --- /dev/null +++ b/www/sharedcache.html @@ -0,0 +1,406 @@ +<!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> + + + + + +<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 = <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=4a965f6f8f">2023-01-02 14:22:42</a> UTC </small></i></p> + |