hd_keywords {SQLite Shared-Cache Mode} {shared cache mode} proc HEADING {level title} { global pnum incr pnum($level) foreach i [array names pnum] { if {$i>$level} {set pnum($i) 0} } set h [expr {$level+1}] if {$h>6} {set h 6} set n $pnum(1).$pnum(2) for {set i 3} {$i<=$level} {incr i} { append n .$pnum($i) } hd_puts "$n $title" } set pnum(1) 0 set pnum(2) 0 set pnum(3) 0 set pnum(4) 0 set pnum(5) 0 set pnum(6) 0 set pnum(7) 0 set pnum(8) 0 HEADING 1 {SQLite Shared-Cache Mode}

Starting with version 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.

In version 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.

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 File Locking And Concurrency In SQLite Version 3 for details) is assumed.

HEADING 1 {Shared-Cache Locking Model}

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.

Figure 1

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.

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.

HEADING 2 {Transaction Level Locking}

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.

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.

HEADING 2 {Table Level Locking}

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.

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.

Once a connection obtains a table lock, it is not released until the current transaction (read or write) is concluded.

HEADING 3 {Read-Uncommitted Isolation Mode}

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.

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.

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 sqlite_master locks required by the rules enumerated below (see section "Schema (sqlite_master) Level Locking").

  /* 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;
HEADING 2 {Schema (sqlite_master) Level Locking}

The sqlite_master 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:

HEADING 1 {Thread Related Issues}

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.

HEADING 1 {Shared Cache And Virtual Tables}

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]. HEADING 1 {Enabling Shared-Cache Mode}

Shared-cache mode is enabled on a per-process basis. Using the C interface, the following API can be used to enable or disable shared-cache mode for the calling thread:

int sqlite3_enable_shared_cache(int);

Each call [sqlite3_enable_shared_cache()] effects 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.

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.