Documentation Source Text

<title>SQLite Backup API</title>
<tcl>hd_keywords {Using the SQLite Online Backup API} {backup API}</tcl>

<style>
  /* Formatting for the blocks containing the example code */
  pre a:visited, pre a:link { text-decoration: none ; color: #40534b }
  pre { 
    background: #F3F3F3; 
    float: right; 

      <a href="http://www.sqlite.org/src/timeline">
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2010 July 1 (3.7.0)} {
<li> Added support for write-ahead logging
<li> Query planner enhancements
}

chng {2010 March 30 (3.6.23.1)} {
<li> Fix a bug in the offsets() function of [FTS3]
<li> Fix a missing "sync" that when omitted could lead to database
     corruption if a power failure or OS crash occurred just as a
     ROLLBACK operation was finishing.
}

</td>
<td width="20"></td><td bgcolor="#80a796" width="1"></td><td width="20"></td>
<td valign="top">
<h3>Current Status</h3>

<p><ul>
<li><a href="releaselog/3_7_0.html">Version 3.7.0</a>
of SQLite is recommended for all new development.  Upgrading from
version 3.6.12, 3.6.13, 3.6.22, and 3.6.23 is optional.
Upgrading from all other SQLite versions is recommended.</li>
</ul></p>

<h3>Common Links</h3>

    current syntax or functionality as it is likely to change.</p>
</li>

<tcl>Subsection journal_mode</tcl>
<li><p>^(<b>PRAGMA journal_mode;
       <br>PRAGMA </b><i>database</i><b>.journal_mode;
       <br>PRAGMA journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i>
       <br>PRAGMA </b><i>database</i><b>.journal_mode
              = <i>DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF</i></b></p>

    <p>This pragma queries or sets the journal mode for databases
    associated with the current [database connection].</p>)^

    <p>^The first two forms of this pragma query the current journaling
    mode.  ^In the first form, the default journal_mode is returned.
    ^The default journaling mode is the mode used by databases added
    to the connection by subsequent [ATTACH] statements.  ^The second
    form returns the current journaling mode for a specific database.</p>

    <p>^The last two forms change the journaling mode.  ^The 4th form
    changes the journaling mode for a specific database connection.
    ^Use "main" for the main database (the database that was opened by
    the original [sqlite3_open()], [sqlite3_open16()], or
    [sqlite3_open_v2()] interface call) and use "temp" for database
    that holds TEMP tables.  ^The 3rd form changes the journaling mode
    on all databases and if the specified mode is not "WAL"
    it also changes the default journaling mode that
    will be used for new databases added by subsequent [ATTACH]
    commands.  ^The default journaling mode is never set to WAL.
    ^The new journal mode is returned.  ^If the journal mode
    could not be changed, the original journal mode is returned.</p>

    <p>^The DELETE journaling mode is the normal behavior.  ^In the DELETE
    mode, the rollback journal is deleted at the conclusion of each
    transaction.  Indeed, the delete operation is the action that causes
    the transaction to commit.
    (See the documented titled <a href="atomiccommit.html">

    the first block of a file with zeros.</p>

    <p>^The MEMORY journaling mode stores the rollback journal in 
    volatile RAM.  ^This saves disk I/O but at the expense of database
    safety and integrity.  ^If the application using SQLite crashes in
    the middle of a transaction when the MEMORY journaling mode is set,
    then the database file will very likely go corrupt.</p>

    <p>^The WAL journaling mode uses a [write-ahead log] instead of a
    rollback journal to implement transactions.</p>

    <p>^The OFF journaling mode disables the rollback journal completely.
    ^No rollback journal is ever created and hence there is never a rollback
    journal to delete.  ^The OFF journaling mode disables the atomic
    commit and rollback capabilities of SQLite. The [ROLLBACK] command
    no longer works; it behaves in an undefined way.  Applications must
    avoid using the [ROLLBACK] command when the journal mode is OFF.

<title>Write-Ahead Logging</title>
<tcl>hd_keywords {WAL} {write-ahead log}</tcl>

<h1 align="center">Write-Ahead Logging</h1>

<p>The default method in which SQLite implements
[atomic commit | atomic commit and rollback] is through the use of
a [rollback journal].
Beginning with [SQLite version 3.7.0], a new write-ahead log option
(hereafter referred to as "WAL")
for implementing atomic commit and rollback is available as an option
on some platforms.</p>

<p>There are advantages and disadvantages to using WAL.  We begin with
a quick summary of advantages:</p>

<ol>
<li>WAL is significately faster in the common case of many small transactions.
<li>WAL provides more concurrency as readers do not block and 
    are not blocked by a writer.
<li>The sequence of disk I/O operations tends to  be more sequential using WAL.
<li>WAL uses many fewer fsync() operations and is thus less vulnerable to
    problems on systems where the fsync() system call is broken.
</ol>

<p>But there are also disadvantages:</p>

<ol>
<li>WAL is only available when the [sqlite3_vfs | VFS] and the underlying
    operating system support a set of shared-memory primitives.
<li>All processes using a database must be on the same host computer;
    WAL does not work over a network filesystem.
<li>Transactions that involve changes against multiple [ATTACH | ATTACHed]
    databases are atomic for each individual database, but are not
    atomic across all databases as a set.
<li>With WAL, it is not possible to change database page sizes on [VACUUM]
    or on recovery using the [backup API].
<li>WAL can be slower than the traditional rollback-journal approach
    for large transactions (such as when using the [VACUUM] command).
</ol>