Documentation Source Text

<title>An Asynchronous I/O Module For SQLite</title>
<tcl>hd_keywords {asynchronous VFS} {asynchronous I/O backend}</tcl>
<h1 align="center">An Asynchronous I/O Module For SQLite</h1>

<p>Normally, when SQLite writes to a database file, it waits until the write
operation is finished before returning control to the calling application.
Since writing to the file-system is usually very slow compared with CPU
bound operations, this can be a performance bottleneck. The asynchronous I/O
backend is an extension that causes SQLite to perform all write requests
using a separate thread running in the background. Although this does not
reduce the overall system resources (CPU, disk bandwidth etc.), it does
allow SQLite to return control to the caller quickly even when writing to
the database.

<h2>1.0 FUNCTIONALITY</h2>

<p>With asynchronous I/O, write requests are handled by a separate thread
running in the background.  This means that the thread that initiates
a database write does not have to wait for (sometimes slow) disk I/O
to occur.  The write seems to happen very quickly, though in reality
it is happening at its usual slow pace in the background.

<p>Asynchronous I/O appears to give better responsiveness, but at a price.
You lose the Durable property.  With the default I/O backend of SQLite,
once a write completes, you know that the information you wrote is
safely on disk.  With the asynchronous I/O, this is not the case.  If
your program crashes or if a power loss occurs after the database
write but before the asynchronous write thread has completed, then the
database change might never make it to disk and the next user of the
database might not see your change.

<p>You lose Durability with asynchronous I/O, but you still retain the
other parts of ACID:  Atomic,  Consistent, and Isolated.  Many
appliations get along fine without the Durablity.

<h3>1.1 How it Works</h3>

<p>Asynchronous I/O works by creating an SQLite [sqlite3_vfs | VFS object]
and registering it with [sqlite3_vfs_register()].
When files opened via 
this VFS are written to (using the vfs xWrite() method), the data is not 
written directly to disk, but is placed in the "write-queue" to be
handled by the background thread.

<p>When files opened with the asynchronous VFS are read from 
(using the vfs xRead() method), the data is read from the file on 
disk and the write-queue, so that from the point of view of
the vfs reader the xWrite() appears to have already completed.

<p>The asynchronous I/O VFS is registered (and unregistered) by calls to the 
API functions sqlite3async_initialize() and sqlite3async_shutdown().
See section "Compilation and Usage" below for details.

<h3>1.2 Limitations</h3>

<p>In order to gain experience with the main ideas surrounding asynchronous 
IO, this implementation is deliberately kept simple. Additional 
capabilities may be added in the future.

<p>For example, as currently implemented, if writes are happening at a 
steady stream that exceeds the I/O capability of the background writer
thread, the queue of pending write operations will grow without bound.
If this goes on for long enough, the host system could run out of memory. 
A more sophisticated module could to keep track of the quantity of 
pending writes and stop accepting new write requests when the queue of 
pending writes grows too large.

<h3>1.3 Locking and Concurrency</h3>

<p>Multiple connections from within a single process that use this
implementation of asynchronous IO may access a single database
file concurrently. From the point of view of the user, if all
connections are from within a single process, there is no difference
between the concurrency offered by "normal" SQLite and SQLite
using the asynchronous backend.

<p>If file-locking is enabled (it is enabled by default), then connections
from multiple processes may also read and write the database file.
However concurrency is reduced as follows:

<ul>
<li><p> When a connection using asynchronous IO begins a database
        transaction, the database is locked immediately. However the
        lock is not released until after all relevant operations
        in the write-queue have been flushed to disk. This means
        (for example) that the database may remain locked for some 
        time after a "[COMMIT]" or "[ROLLBACK]" is issued.

<li><p> If an application using asynchronous IO executes transactions
        in quick succession, other database users may be effectively
        locked out of the database. This is because when a [BEGIN]
        is executed, a database lock is established immediately. But
        when the corresponding COMMIT or ROLLBACK occurs, the lock
        is not released until the relevant part of the write-queue 
        has been flushed through. As a result, if a COMMIT is followed
        by a BEGIN before the write-queue is flushed through, the database 
        is never unlocked,preventing other processes from accessing 
        the database.
</ul>

<p>File-locking may be disabled at runtime using the sqlite3async_control()
API (see below). This may improve performance when an NFS or other 
network file-system, as the synchronous round-trips to the server be 
required to establish file locks are avoided. However, if multiple 
connections attempt to access the same database file when file-locking
is disabled, application crashes and database corruption is a likely
outcome.


<h2>2.0 COMPILATION AND USAGE</h2>

<p>
The asynchronous IO extension consists of a single file of C code
(sqlite3async.c), and a header file (sqlite3async.h), located in the
<a href="http://www.sqlite.org/cvstrac/dir?d=sqlite/ext/async">
<tt>ext/async/</tt> subfolder</a> of the SQLite source tree, that defines the 
C API used by applications to activate and control the modules 
functionality.

<p>
To use the asynchronous IO extension, compile sqlite3async.c as
part of the application that uses SQLite. Then use the APIs defined
in sqlite3async.h to initialize and configure the module.

<p>
The asynchronous IO VFS API is described in detail in comments in 
sqlite3async.h. Using the API usually consists of the following steps:

<ol>
<li><p>Register the asynchronous IO VFS with SQLite by calling the
       sqlite3async_initialize() function.

<li><p>Create a background thread to perform write operations and call
       sqlite3async_run().

<li><p>Use the normal SQLite API to read and write to databases via 
       the asynchronous IO VFS.
</ol>

<p>Refer to comments in the
<a href="http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/async/sqlite3async.h">
sqlite3async.h header file</a> for details.


<h2>3.0 PORTING</h2>

<p>Currently the asynchronous IO extension is compatible with win32 systems
and systems that support the pthreads interface, including Mac OSX, Linux, 
and other varieties of Unix. 

<p>To port the asynchronous IO extension to another platform, the user must
implement mutex and condition variable primitives for the new platform.
Currently there is no externally available interface to allow this, but
modifying the code within sqlite3async.c to include the new platforms
concurrency primitives is relatively easy. Search within sqlite3async.c
for the comment string "PORTING FUNCTIONS" for details. Then implement
new versions of each of the following:

<blockquote><pre>
static void async_mutex_enter(int eMutex);
static void async_mutex_leave(int eMutex);
static void async_cond_wait(int eCond, int eMutex);
static void async_cond_signal(int eCond);
static void async_sched_yield(void);
</pre></blockquote>

<p>The functionality required of each of the above functions is described
in comments in sqlite3async.c.

      http://www.sqlite.org/cvstrac/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2009 May 7 (3.6.14)} {
<li>Added the optional [asynchronous VFS] module.</li>
<li>Enhanced the query optimizer so that [virtual tables] are able to
    make use of OR and IN operators in the WHERE clause.</li>
<li>Speed improvements in the btree and pager layers.</li>
<li>Added the [SQLITE_HAVE_ISNAN] compile-time option which will cause
    the isnan() function from the standard math library to be used instead
    of SQLite's own home-brew NaN checker.</li>
<li>Countless minor bug fixes, documentation improvements, new and

  regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt
  regsub -all {[Tt]icket #(\d+)} $txt \
      {<a href="http://www.sqlite.org/cvstrac/tktview?tn=\1">\0</a>} txt
  hd_resolve "<p>$txt</p>"
  hd_puts "<hr width=\"50%\">"
}

newsitem {2009-May-07} {Version 3.6.14} {
  SQLite [version 3.6.14] provides new performance enhancements in
  the btree and pager layers and in the query optimizer.  Certain
  workloads can be as much as twice as fast as the previous release,
  though 10% faster is a more typical result.

  Queries against [virtual tables] that contain OR and IN operators
  in the WHERE clause are no able to use indexing.

  A new optional [asynchronous I/O backend] is available for
  unix and windows.  The asynchronous backend gives the illusion of faster
  response time by pushing slow write operations into a background thread.
  The tradeoff for faster response time is that more memory is required
  (to hold the content of the pending writes) and if a power failure or
  program crash occurs, some transactions that appeared to have committed
  might end up being rolled back upon restart.

  This release also contains many minor bug fixes, documentation enhancements,
  new test cases, and cleanups and simplifications to the source code.

  There is no compelling reason to upgrade from versions 3.6.12 or
  3.6.13 if those prior versions are working.  Though many users may
  benefit from the improved performance.
}

newsitem {2009-Apr-14} {Version 3.6.13} {
  SQLite [version 3.6.13] fixes several minor issues that appeared
  in previous version, including Ticket #3774, ticket #3791, and ticket #3777.
  This is a bug-fix release only.   There are no new features or
  enhancements.  
}

newsitem {2009-Mar-31} {Version 3.6.12} {
  SQLite [version 3.6.12] fixes a database corruption bug.  If an
  [incremental_vacuum] is rolled back in an in-memory database, the

<title>How SQLite Is Tested</title>

<tcl>
# This document contains many size statistics about SQLite, statistics
# that change frequently.  We want the document to be up-to-date.  To
# facilitate that, all the size values are defined by variables here
# which are then used as needed through the document.
#
# NOTE:  Also update the version number in the text!!!
#

# sloc sqlite3.c
set stat(coreSLOC)    63239  ;# Non-comment lines of amalgamation code 
# sloc test*.c
set stat(tclcSLOC)    15070  ;# Non-comment lines of test C code
# ls test*.c tclsqlite.c | wc
set stat(tclcNfile)      31  ;# Number of files of TCL C testcode + tclsqlite.c
# ls -l test*.c tclsqlite.c | awk '{sum+=$5}END{print sum}'
set stat(tclcNByte)  711774  ;# Number of bytes of TCL C testcode + tclsqlite.c
# sloc *.test *.tcl
set stat(tclsSLOC)   216369  ;# Non-comment lines of TCL test script
# ls *.test *.tcl | wc
set stat(tclsNFile)     488  ;# Number of files of TCL test script
# ls -l *.test *.tcl | awk '{sum+=$5}END{print sum}'
set stat(tclsNByte) 8076312  ;# Number of bytes of TCL test script
# grep do_test *.test | wc
set stat(tclNTest)    24149  ;# Number of test cases in the TCL test suite
set stat(tclNEval)  1752856  ;# Number of test case evaluations
set stat(nSqlFuzz)   108656  ;# Number of SQL fuzz tests
set stat(vqNEval)     41657  ;# Number of test evaluations for veryquick.test
set stat(vqStmtCov)   97.07  ;# veryquick statement coverage
set stat(vqBrCov)     90.43  ;# veryquick branch coverage
set stat(allStmtCov)  99.41  ;# all.test statement coverage
set stat(allBrCov)    95.56  ;# all.test condition/decision coverage
# tclsh mkth3.tcl cfg/*.cfg */*.test >th3.c; sloc th3.c
set stat(th3SLOC)    169736  ;# Non-comment lines in full th3.c
# ls -l th3.c
set stat(th3NByte) 10745765  ;# Number of bytes in full th3.c
# grep th3testBegin */*.test
# grep th3oomBegin */*.test
# grep th3ioerrBegin */*.test
# grep '^--testcase' */*.test
set stat(th3NTest)     8345  ;# Number of test cases
# from output of a full test run.
set stat(th3NEval)  3934553  ;# Number of test case evaluations
set stat(th3StmtCov)  94.70  ;# TH3 statement coverage
set stat(th3BrCov)    87.86  ;# TH3 branch coverage
# wc `fossil ls | awk '/\.test$/{print $2}'`
set stat(sltsSLOC)  44858977 ;# Non-comment lines of SLT test script
# ls -l `fossil ls | awk '/\.test$/{print $2}'` | awk '{sum+=$5}END{print sum}'
set stat(sltsNByte) 1116748159 ;# Bytes of SLT test script
# fossil ls | awk '/.test$/{print $2}' | wc
set stat(sltsNFile)        610 ;# Files of SLT test script
# sloc md5.c slt_*.c sqllogictest.c
set stat(sltcSLOC)        1307 ;# Non-comment lines of SLT C code
# grep '^query' `fossil ls | awk '/\.test$/{print $2}'` | wc
set stat(sltNTest)     7195024 ;# Number of test cases in SLT
# grep 'assert(' sqlite3.c | wc
set stat(nAssert)         2567 ;# Number of assert statements


set stat(totalSLOC) [expr {$stat(tclcSLOC)+$stat(tclsSLOC)+
                           $stat(th3SLOC)+$stat(sltcSLOC)+$stat(sltsSLOC)}]

proc GB {expr} {
  set n [uplevel #0 expr $expr]
  hd_puts [format %.2f [expr {$n/(1000.0*1000.0*1000.0)}]]

</tcl>

<h1 align="center">How SQLite Is Tested</h1>

<h2>1.0 Introduction</h2>

<p>The reliability and robustness of SQLite is achieved in part
by thorough and careful testing.</p>

<p>As of [version 3.6.14] (all statistics in the report are against that
release of SQLite),
the SQLite library consists of approximately
<tcl>KB {$stat(coreSLOC)}</tcl> KSLOC of C code.
(KSLOC means thousands of "Source Lines Of Code" or, in other words,
lines of code excluding blank lines and comments.)
By comparison, the project has
<tcl>