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>