Documentation Source Text

Check-in [67d9d15136]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Corrections and updates to the sharedcache.html document.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 67d9d1513659d56920b7eaff89c5cbbadbaa973a
User & Date: drh 2008-03-17 12:16:07.000
Context
2008-03-17
15:18
Update webpage index, change-log, and news for the 3.5.7 release. (check-in: 9497b87a69 user: drh tags: trunk)
12:16
Corrections and updates to the sharedcache.html document. (check-in: 67d9d15136 user: drh tags: trunk)
2008-03-08
12:38
Fix typo in the GROUP BY documentation. (check-in: 9b7284faf3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/sharedcache.in.
30
31
32
33
34
35
36






37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<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>Using shared-cache mode imposes some extra restrictions on 
passing database handles between threads and changes the semantics
of the locking model in some cases. These details are described in full 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 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">







>
>
>
>
>
>
|
|
|







|







30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<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.
</p>

<table style="margin:auto">
<tr><td>
<img src="images/shared.gif">
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
          +----------------+
</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 (and so must
have been established by the same process thread). 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, 







|
|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
          +----------------+
</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, 
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
<p>Once a connection obtains a table lock, it is not released until the
current transaction (read or write) is concluded.
</p>

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

<p>The behaviour described above may be modified slightly by using the 
<i>read_uncommitted</i> 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>

<pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connectino to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre>

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







|

















|









|







121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
<p>Once a connection obtains a table lock, it is not released until the
current transaction (read or write) is concluded.
</p>

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

<blockquote><pre>
  /* Set the value of the read-uncommitted flag:
  **
  **   True  -> Set the connection to read-uncommitted mode.
  **   False -> Set the connectino to serialized (the default) mode.
  */
  PRAGMA read_uncommitted = &lt;boolean&gt;;

  /* Retrieve the current value of the read-uncommitted flag */
  PRAGMA read_uncommitted;
</pre></blockquote>

<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:
</p>
167
168
169
170
171
172
173

174
175
176
177
178
179

180
















181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198


199
is holding a write-lock on the <i>sqlite_master</i> table of any attached
database (including the default database, "main"). 
</li>
</ul>

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


<p>When shared-cache mode is enabled, a database connection may only be
used by the thread that called sqlite3_open() to create it. If another 
thread attempts to use the database connection, in most cases an 
SQLITE_MISUSE error is returned. However this is not guaranteed and 
programs should not depend on this behaviour, in some cases a segfault 
may result.

</p>

















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

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

<pre>
int sqlite3_enable_shared_cache(int);
</pre>

<p>It is illegal to call sqlite3_enable_shared_cache() if one or more 
open database connections were opened by the calling thread. If the argument
is non-zero, shared-cache mode is enabled. If the argument is zero,
shared-cache mode is disabled. The return value is either SQLITE_OK (if the
operation was successful), SQLITE_NOMEM (if a malloc() failed), or
SQLITE_MISUSE (if the thread has open database connections).


</p>







>
|
|
|
<
<
|
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



|




|

|

|
|
<
<
<
|
>
>

173
174
175
176
177
178
179
180
181
182
183


184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216



217
218
219
220
is holding a write-lock on the <i>sqlite_master</i> table of any attached
database (including the default database, "main"). 
</li>
</ul>

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

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

<p>Shared cache mode cannot be used together with virtual tables.
The reason for this is that virtual tables often make a copy of
the ["sqlite3*"] database handle that was used to originally open
the virtual table.  The virtual table might use this handle to 
prepare statements for recursive access to the database.  But
a prepared statement only works for the database connection on
which it was originally created.  If a virtual table is part of
a shared cache, it might be invoked by multiple database connections
but its prepared statements will only work on one of those 
connections.  To avoid problems sorting all of this out, and to
simplify the implementation of virtual tables, SQLite includes
checks that prohibit shared cache and virtual tables from being
used at the same time.</p>

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

<blockquote><pre>
int sqlite3_enable_shared_cache(int);
</pre></blockquote>

<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
uneffected.  Each call to [sqlite3_enable_shared_cache()] overrides
all previous calls within the same process.
</p>