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