1
2Normally, when SQLite writes to a database file, it waits until the write
3operation is finished before returning control to the calling application.
4Since writing to the file-system is usually very slow compared with CPU
5bound operations, this can be a performance bottleneck. This directory
6contains an extension that causes SQLite to perform all write requests
7using a separate thread running in the background. Although this does not
8reduce the overall system resources (CPU, disk bandwidth etc.) at all, it
9allows SQLite to return control to the caller quickly even when writing to
10the database, eliminating the bottleneck.
11
12 1. Functionality
13
14 1.1 How it Works
15 1.2 Limitations
16 1.3 Locking and Concurrency
17
18 2. Compilation and Usage
19
20 3. Porting
21
22
23
241. FUNCTIONALITY
25
26 With asynchronous I/O, write requests are handled by a separate thread
27 running in the background. This means that the thread that initiates
28 a database write does not have to wait for (sometimes slow) disk I/O
29 to occur. The write seems to happen very quickly, though in reality
30 it is happening at its usual slow pace in the background.
31
32 Asynchronous I/O appears to give better responsiveness, but at a price.
33 You lose the Durable property. With the default I/O backend of SQLite,
34 once a write completes, you know that the information you wrote is
35 safely on disk. With the asynchronous I/O, this is not the case. If
36 your program crashes or if a power loss occurs after the database
37 write but before the asynchronous write thread has completed, then the
38 database change might never make it to disk and the next user of the
39 database might not see your change.
40
41 You lose Durability with asynchronous I/O, but you still retain the
42 other parts of ACID: Atomic, Consistent, and Isolated. Many
43 appliations get along fine without the Durablity.
44
45 1.1 How it Works
46
47 Asynchronous I/O works by creating a special SQLite "vfs" structure
48 and registering it with sqlite3_vfs_register(). When files opened via
49 this vfs are written to (using the vfs xWrite() method), the data is not
50 written directly to disk, but is placed in the "write-queue" to be
51 handled by the background thread.
52
53 When files opened with the asynchronous vfs are read from
54 (using the vfs xRead() method), the data is read from the file on
55 disk and the write-queue, so that from the point of view of
56 the vfs reader the xWrite() appears to have already completed.
57
58 The special vfs is registered (and unregistered) by calls to the
59 API functions sqlite3async_initialize() and sqlite3async_shutdown().
60 See section "Compilation and Usage" below for details.
61
62 1.2 Limitations
63
64 In order to gain experience with the main ideas surrounding asynchronous
65 IO, this implementation is deliberately kept simple. Additional
66 capabilities may be added in the future.
67
68 For example, as currently implemented, if writes are happening at a
69 steady stream that exceeds the I/O capability of the background writer
70 thread, the queue of pending write operations will grow without bound.
71 If this goes on for long enough, the host system could run out of memory.
72 A more sophisticated module could to keep track of the quantity of
73 pending writes and stop accepting new write requests when the queue of
74 pending writes grows too large.
75
76 1.3 Locking and Concurrency
77
78 Multiple connections from within a single process that use this
79 implementation of asynchronous IO may access a single database
80 file concurrently. From the point of view of the user, if all
81 connections are from within a single process, there is no difference
82 between the concurrency offered by "normal" SQLite and SQLite
83 using the asynchronous backend.
84
85 If file-locking is enabled (it is enabled by default), then connections
86 from multiple processes may also read and write the database file.
87 However concurrency is reduced as follows:
88
89 * When a connection using asynchronous IO begins a database
90 transaction, the database is locked immediately. However the
91 lock is not released until after all relevant operations
92 in the write-queue have been flushed to disk. This means
93 (for example) that the database may remain locked for some
94 time after a "COMMIT" or "ROLLBACK" is issued.
95
96 * If an application using asynchronous IO executes transactions
97 in quick succession, other database users may be effectively
98 locked out of the database. This is because when a BEGIN
99 is executed, a database lock is established immediately. But
100 when the corresponding COMMIT or ROLLBACK occurs, the lock
101 is not released until the relevant part of the write-queue
102 has been flushed through. As a result, if a COMMIT is followed
103 by a BEGIN before the write-queue is flushed through, the database
104 is never unlocked,preventing other processes from accessing
105 the database.
106
107 File-locking may be disabled at runtime using the sqlite3async_control()
108 API (see below). This may improve performance when an NFS or other
109 network file-system, as the synchronous round-trips to the server be
110 required to establish file locks are avoided. However, if multiple
111 connections attempt to access the same database file when file-locking
112 is disabled, application crashes and database corruption is a likely
113 outcome.
114
115
1162. COMPILATION AND USAGE
117
118 The asynchronous IO extension consists of a single file of C code
119 (sqlite3async.c), and a header file (sqlite3async.h) that defines the
120 C API used by applications to activate and control the modules
121 functionality.
122
123 To use the asynchronous IO extension, compile sqlite3async.c as
124 part of the application that uses SQLite. Then use the API defined
125 in sqlite3async.h to initialize and configure the module.
126
127 The asynchronous IO VFS API is described in detail in comments in
128 sqlite3async.h. Using the API usually consists of the following steps:
129
130 1. Register the asynchronous IO VFS with SQLite by calling the
131 sqlite3async_initialize() function.
132
133 2. Create a background thread to perform write operations and call
134 sqlite3async_run().
135
136 3. Use the normal SQLite API to read and write to databases via
137 the asynchronous IO VFS.
138
139 Refer to sqlite3async.h for details.
140
141
1423. PORTING
143
144 Currently the asynchronous IO extension is compatible with win32 systems
145 and systems that support the pthreads interface, including Mac OSX, Linux,
146 and other varieties of Unix.
147
148 To port the asynchronous IO extension to another platform, the user must
149 implement mutex and condition variable primitives for the new platform.
150 Currently there is no externally available interface to allow this, but
151 modifying the code within sqlite3async.c to include the new platforms
152 concurrency primitives is relatively easy. Search within sqlite3async.c
153 for the comment string "PORTING FUNCTIONS" for details. Then implement
154 new versions of each of the following:
155
156 static void async_mutex_enter(int eMutex);
157 static void async_mutex_leave(int eMutex);
158 static void async_cond_wait(int eCond, int eMutex);
159 static void async_cond_signal(int eCond);
160 static void async_sched_yield(void);
161
162 The functionality required of each of the above functions is described
163 in comments in sqlite3async.c.
164
165