📄 sharedcache.html
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"><html><head><title>SQLite Shared-Cache Mode</title><style type="text/css">body { margin: auto; font-family: "Verdana" "sans-serif"; padding: 8px 1%;}a { color: #45735f }a:visited { color: #734559 }.logo { position:absolute; margin:3px; }.tagline { float:right; text-align:right; font-style:italic; width:240px; margin:12px; margin-top:58px;}.toolbar { font-variant: small-caps; text-align: center; line-height: 1.6em; margin: 0; padding:1px 8px;}.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }.toolbar a:visited { color: white; }.toolbar a:hover { color: #80a796; background: white; }.content { margin: 5%; }.content dt { font-weight:bold; }.content dd { margin-bottom: 25px; margin-left:20%; }.content ul { padding:0px; padding-left: 15px; margin:0px; }/* rounded corners */.se { background: url(images/se.png) 100% 100% no-repeat #80a796}.sw { background: url(images/sw.png) 0% 100% no-repeat }.ne { background: url(images/ne.png) 100% 0% no-repeat }.nw { background: url(images/nw.png) 0% 0% no-repeat }</style><meta http-equiv="content-type" content="text/html; charset=UTF-8"> </head><body><div><!-- container div to satisfy validator --><a href="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td> <div class="se"><div class="sw"><div class="ne"><div class="nw"> <div class="toolbar"> <a href="about.html">About</a> <a href="sitemap.html">Sitemap</a> <a href="docs.html">Documentation</a> <a href="download.html">Download</a> <a href="copyright.html">License</a> <a href="news.html">News</a> <a href="http://www.sqlite.org/cvstrac/index">Developers</a> <a href="support.html">Support</a> </div></div></div></div></div></td></tr></table> <h2>1.0 SQLite Shared-Cache Mode</h2><p>Starting with version 3.3.0, SQLite includes a special "shared-cache"mode (disabled by default) intended for use in embedded servers. Ifshared-cache mode is enabled and a thread establishes multiple connectionsto the same database, the connections share a single data and schema cache.This can significantly reduce the quantity of memory and IO required bythe system.</p><p>In version 3.5.0, shared-cache mode was modified so that the samecache can be shared across an entire process rather than just withina single thread. Prior to this change, there were restrictions onpassing database connections between threads. Those restrictions weredropped in 3.5.0 update. This document describes shared-cache modeas of version 3.5.0.</p><p>Shared-cache mode changes the semanticsof the locking model in some cases. The details are described bythis 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><h2>2.0 Shared-Cache Locking Model</h2><p>Externally, from the point of view of another process or thread, twoor more <a href="c3ref/sqlite3.html">database connections</a> 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 normalSQLite database connection. Connections 2 and 3 share a cache The normal lockingprotocol is used to serialize database access between connection 1 andthe shared cache. The internal protocol used to serialize (or not, see"Read-Uncommitted Isolation Mode" below) access to the shared-cache byconnections 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><h3>2.1 Transaction Level Locking</h3><p>SQLite connections can open two kinds of transactions, read and writetransactions. This is not done explicitly, a transaction is implicitly aread-transaction until it first writes to a database table, at which pointit 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><h3>2.2 Table Level Locking</h3><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 toconnections - at any one time, each database connection has either aread-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-locksor 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 thecurrent transaction (read or write) is concluded.</p><h4>2.2.1 Read-Uncommitted Isolation Mode</h4><p>The behaviour described above may be modified slightly by using the <a href="pragma.html#pragma_read_uncommitted">read_uncommitted</a> 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 databaseconnection modifies a table while it is being read, but it also means thata read-transaction opened by a connection in read-uncommitted mode canneither block nor be blocked by any other connection.</p><p>Read-uncommitted mode has no effect on the locks required to write todatabase 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 = <boolean>; /* Retrieve the current value of the read-uncommitted flag */ PRAGMA read_uncommitted;</pre></blockquote><h3>2.3 Schema (sqlite_master) Level Locking</h3><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 connectionis holding a write-lock on the <i>sqlite_master</i> table of any attacheddatabase (including the default database, "main"). </li></ul><h2>3.0 Thread Related Issues</h2><p>In SQLite versions 3.3.0 through 3.4.2 when shared-cache mode is enabled, a database connection may only beused by the thread that called <a href="c3ref/open.html">sqlite3_open()</a> to create it.And a connection could only share cache with another connection in thesame thread.These restrictions were dropped beginning with SQLite version</p><h2>4.0 Shared Cache And Virtual Tables</h2><p>Shared cache mode cannot be used together with virtual tables.The reason for this is that virtual tables often make a copy ofthe <a href="c3ref/sqlite3.html">"sqlite3*"</a> database handle that was used to originally openthe virtual table. The virtual table might use this handle to prepare statements for recursive access to the database. Buta prepared statement only works for the database connection onwhich it was originally created. If a virtual table is part ofa shared cache, it might be invoked by multiple database connectionsbut its prepared statements will only work on one of those connections. To avoid problems sorting all of this out, and tosimplify the implementation of virtual tables, SQLite includeschecks that prohibit shared cache and virtual tables from beingused at the same time.</p><h2>5.0 Enabling Shared-Cache Mode</h2><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-cachemode for the calling thread:</p><blockquote><pre>int sqlite3_enable_shared_cache(int);</pre></blockquote><p>Each call <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> effects subsequent databaseconnections created using <a href="c3ref/open.html">sqlite3_open()</a>, <a href="c3ref/open.html">sqlite3_open16()</a>, or<a href="c3ref/open.html">sqlite3_open_v2()</a>. Database connections that already exist areuneffected. Each call to <a href="c3ref/enable_shared_cache.html">sqlite3_enable_shared_cache()</a> overridesall previous calls within the same process.</p><hr><small><i>This page last modified 2008/03/17 12:14:42 UTC</i></small></div></body></html>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -