Documentation Source Text

Artifact [623c794f9a]

Artifact 623c794f9a01ef12fe0aa47e410bbcd3a5e00bbc:

<title>SQLite Shared-Cache Mode</title>
<tcl>hd_keywords {SQLite Shared-Cache Mode} {shared cache mode}</tcl>

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 "<h$h>$n $title</h$h>"
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

<tcl>HEADING 1 {SQLite Shared-Cache Mode}</tcl>

<p>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.</p>

<p>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.</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>

<tcl>HEADING 1 {Shared-Cache Locking Model}</tcl>

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

<table style="margin:auto">
<img src="images/shared.gif">
<!-- <pre>
            +--------------+      +--------------+
            | Connection 2 |      | Connection 3 |
            +--------------+      +--------------+
                         |          |
                         V          V
+--------------+       +--------------+
| Connection 1 |       | Shared cache |
+--------------+       +--------------+
            |            |
            V            V
          |    Database    |
</pre> -->
<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>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>

<tcl>HEADING 2 {Transaction Level Locking}</tcl>

<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>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 

<tcl>HEADING 2 {Table Level Locking}</tcl>

<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>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>Once a connection obtains a table lock, it is not released until the
current transaction (read or write) is concluded.

<tcl>HEADING 3 {Read-Uncommitted Isolation Mode}</tcl>

<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").

  /* 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;

<tcl>HEADING 2 {Schema (sqlite_master) Level Locking}</tcl>

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

<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 
<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"). 

<tcl>HEADING 1 {Thread Related Issues}</tcl>

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

<tcl>HEADING 1 {Shared Cache And Virtual Tables}</tcl>

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].

<tcl>HEADING 1 {Enabling Shared-Cache Mode}</tcl>

<p>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);

<p>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.

<p>Individual database connections created using [sqlite3_open_v2()] can
choose to participate or not participate in shared cache mode by using
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>