1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>An Asynchronous I/O Module For SQLite</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
122
<h1 align="center">An Asynchronous I/O Module For SQLite</h1>
124
<p>Normally, when SQLite writes to a database file, it waits until the write
125
operation is finished before returning control to the calling application.
126
Since writing to the file-system is usually very slow compared with CPU
127
bound operations, this can be a performance bottleneck. The asynchronous I/O
128
backend is an extension that causes SQLite to perform all write requests
129
using a separate thread running in the background. Although this does not
130
reduce the overall system resources (CPU, disk bandwidth etc.), it does
131
allow SQLite to return control to the caller quickly even when writing to
134
<h2>1.0 FUNCTIONALITY</h2>
136
<p>With asynchronous I/O, write requests are handled by a separate thread
137
running in the background. This means that the thread that initiates
138
a database write does not have to wait for (sometimes slow) disk I/O
139
to occur. The write seems to happen very quickly, though in reality
140
it is happening at its usual slow pace in the background.
142
<p>Asynchronous I/O appears to give better responsiveness, but at a price.
143
You lose the Durable property. With the default I/O backend of SQLite,
144
once a write completes, you know that the information you wrote is
145
safely on disk. With the asynchronous I/O, this is not the case. If
146
your program crashes or if a power loss occurs after the database
147
write but before the asynchronous write thread has completed, then the
148
database change might never make it to disk and the next user of the
149
database might not see your change.
151
<p>You lose Durability with asynchronous I/O, but you still retain the
152
other parts of ACID: Atomic, Consistent, and Isolated. Many
153
applications get along fine without the Durability.
155
<h3>1.1 How it Works</h3>
157
<p>Asynchronous I/O works by creating an SQLite <a href="c3ref/vfs.html">VFS object</a>
158
and registering it with <a href="c3ref/vfs_find.html">sqlite3_vfs_register()</a>.
159
When files opened via
160
this VFS are written to (using the vfs xWrite() method), the data is not
161
written directly to disk, but is placed in the "write-queue" to be
162
handled by the background thread.
164
<p>When files opened with the asynchronous VFS are read from
165
(using the vfs xRead() method), the data is read from the file on
166
disk and the write-queue, so that from the point of view of
167
the vfs reader the xWrite() appears to have already completed.
169
<p>The asynchronous I/O VFS is registered (and unregistered) by calls to the
170
API functions sqlite3async_initialize() and sqlite3async_shutdown().
171
See section "Compilation and Usage" below for details.
173
<h3>1.2 Limitations</h3>
175
<p>In order to gain experience with the main ideas surrounding asynchronous
176
IO, this implementation is deliberately kept simple. Additional
177
capabilities may be added in the future.
179
<p>For example, as currently implemented, if writes are happening at a
180
steady stream that exceeds the I/O capability of the background writer
181
thread, the queue of pending write operations will grow without bound.
182
If this goes on for long enough, the host system could run out of memory.
183
A more sophisticated module could to keep track of the quantity of
184
pending writes and stop accepting new write requests when the queue of
185
pending writes grows too large.
187
<h3>1.3 Locking and Concurrency</h3>
189
<p>Multiple connections from within a single process that use this
190
implementation of asynchronous IO may access a single database
191
file concurrently. From the point of view of the user, if all
192
connections are from within a single process, there is no difference
193
between the concurrency offered by "normal" SQLite and SQLite
194
using the asynchronous backend.
196
<p>If file-locking is enabled (it is enabled by default), then connections
197
from multiple processes may also read and write the database file.
198
However concurrency is reduced as follows:
201
<li><p> When a connection using asynchronous IO begins a database
202
transaction, the database is locked immediately. However the
203
lock is not released until after all relevant operations
204
in the write-queue have been flushed to disk. This means
205
(for example) that the database may remain locked for some
206
time after a "<a href="lang_transaction.html">COMMIT</a>" or "<a href="lang_transaction.html">ROLLBACK</a>" is issued.
208
<li><p> If an application using asynchronous IO executes transactions
209
in quick succession, other database users may be effectively
210
locked out of the database. This is because when a <a href="lang_transaction.html">BEGIN</a>
211
is executed, a database lock is established immediately. But
212
when the corresponding COMMIT or ROLLBACK occurs, the lock
213
is not released until the relevant part of the write-queue
214
has been flushed through. As a result, if a COMMIT is followed
215
by a BEGIN before the write-queue is flushed through, the database
216
is never unlocked,preventing other processes from accessing
220
<p>File-locking may be disabled at runtime using the sqlite3async_control()
221
API (see below). This may improve performance when an NFS or other
222
network file-system, as the synchronous round-trips to the server be
223
required to establish file locks are avoided. However, if multiple
224
connections attempt to access the same database file when file-locking
225
is disabled, application crashes and database corruption is a likely
229
<h2>2.0 COMPILATION AND USAGE</h2>
232
The asynchronous IO extension consists of a single file of C code
233
(sqlite3async.c), and a header file (sqlite3async.h), located in the
234
<a href="http://www.sqlite.org/src/dir?name=ext/async">
235
<tt>ext/async/</tt> subfolder</a> of the SQLite source tree, that defines the
236
C API used by applications to activate and control the modules
240
To use the asynchronous IO extension, compile sqlite3async.c as
241
part of the application that uses SQLite. Then use the APIs defined
242
in sqlite3async.h to initialize and configure the module.
245
The asynchronous IO VFS API is described in detail in comments in
246
sqlite3async.h. Using the API usually consists of the following steps:
249
<li><p>Register the asynchronous IO VFS with SQLite by calling the
250
sqlite3async_initialize() function.
252
<li><p>Create a background thread to perform write operations and call
255
<li><p>Use the normal SQLite API to read and write to databases via
256
the asynchronous IO VFS.
259
<p>Refer to comments in the
260
<a href="http://www.sqlite.org/src/finfo?name=ext/async/sqlite3async.h">
261
sqlite3async.h header file</a> for details.
266
<p>Currently the asynchronous IO extension is compatible with win32 systems
267
and systems that support the pthreads interface, including Mac OS X, Linux,
268
and other varieties of Unix.
270
<p>To port the asynchronous IO extension to another platform, the user must
271
implement mutex and condition variable primitives for the new platform.
272
Currently there is no externally available interface to allow this, but
273
modifying the code within sqlite3async.c to include the new platforms
274
concurrency primitives is relatively easy. Search within sqlite3async.c
275
for the comment string "PORTING FUNCTIONS" for details. Then implement
276
new versions of each of the following:
279
static void async_mutex_enter(int eMutex);
280
static void async_mutex_leave(int eMutex);
281
static void async_cond_wait(int eCond, int eMutex);
282
static void async_cond_signal(int eCond);
283
static void async_sched_yield(void);
286
<p>The functionality required of each of the above functions is described
287
in comments in sqlite3async.c.
added
removed
asyncvfs.html
