Artifact 7335f6bc3e3e457a15cf990936c4a93bdb0b1f3e:
- File
pages/sharedcache.in
— part of check-in
[858d993381]
at
2016-09-14 18:40:10
on branch trunk
— Add and use the "[dateof:3.X.Y]" hyperlink target for automatically
inserting release dates into the text.
(user:
drh
size: 9667)
<title>SQLite Shared-Cache Mode</title>
<tcl>hd_keywords {SQLite Shared-Cache Mode} \
{shared cache} {shared cache mode}</tcl>
<table_of_contents>
<h1>SQLite Shared-Cache Mode</h1>
<p>Starting with [version 3.3.0] ([dateof:3.3.0]),
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 [version 3.5.0] ([dateof:3.5.0]),
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>
<h1>Shared-Cache Locking Model</h1>
<p>Externally, from the point of view of another process or thread, two
or more [sqlite3|database connections] 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">
<!-- <pre>
+--------------+ +--------------+
| Connection 2 | | Connection 3 |
+--------------+ +--------------+
| |
V V
+--------------+ +--------------+
| Connection 1 | | Shared cache |
+--------------+ +--------------+
| |
V V
+----------------+
| Database |
+----------------+
</pre> -->
</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>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>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 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>Read-Uncommitted Isolation Mode</h3>
<p>The behaviour described above may be modified slightly by using the
[read_uncommitted] 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 <i>sqlite_master</i>
locks required by the rules enumerated below (see section
"Schema (sqlite_master) 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>Schema (sqlite_master) Level Locking</h2>
<p>The <i>sqlite_master</i> table 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_master</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_master</i>.
</li>
<li>A connection may not compile an SQL statement if any other connection
is holding a write-lock on the <i>sqlite_master</i> table of any attached
database (including the default database, "main").
</li>
</ul>
<h1>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 [sqlite3_open()] to create it.
And a connection could only share cache with another connection in the
same thread.
These restrictions were dropped beginning with SQLite
[version 3.5.0] ([dateof:3.5.0]).
</p>
<h1>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 [version 3.6.17] ([dateof:3.6.17]).
<h1>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 [sqlite3_enable_shared_cache()] affects subsequent database
connections created using [sqlite3_open()], [sqlite3_open16()], or
[sqlite3_open_v2()]. Database connections that already exist are
unaffected. Each call to [sqlite3_enable_shared_cache()] overrides
all previous calls within the same process.
</p>
<p>Individual database connections created using [sqlite3_open_v2()] can
choose to participate or not participate in shared cache mode by using
the [SQLITE_OPEN_SHAREDCACHE] or [SQLITE_OPEN_PRIVATECACHE] flags the
third parameter. The use of either of these flags overrides the
global shared cache mode setting established by [sqlite3_enable_shared_cache()].
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
[sqlite3_open_v2()] then the behavior is undefined.</p>
<p>When [URI filenames] 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 [ATTACH] statements. For example:</p>
<blockquote><pre>
ATTACH 'file:aux.db?cache=shared' AS aux;
</pre></blockquote>
<tcl> hd_fragment inmemsharedcache {in-memory shared-cache} </tcl>
<h1>Shared Cache And In-Memory Databases</h1>
<p>
Beginning with SQLite [version 3.7.13] ([dateof:3.7.13]),
shared cache can be used on
[in-memory databases], provided that the database is created using
a [URI filename]. For backwards compatibility, shared cache is always
disable 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>