Documentation Source Text

Artifact [c89f81aeee]

Artifact c89f81aeeef8bda1a86b80e1052c67b6d7051ec1:

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

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

<p>The default method by which SQLite implements
[atomic commit | atomic commit and rollback] is a [rollback journal].
Beginning with [version 3.7.0], a new "Write-Ahead Log" option
(hereafter referred to as "WAL") is available.</p>

<p>There are advantages and disadvantages to using WAL instead of
a rollback journal.  Advantages include:</p>

<li>WAL is significantly faster in most scenarios.
<li>WAL provides more concurrency as readers do not block writers and 
    a writer does not block readers.  Reading and writing can proceed 
<li>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.

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

<li>WAL normally requires that the [VFS] 
    support shared-memory primitives.
    (Exception: [WAL without shared memory])
    The built-in unix and windows VFSes
    support this but third-party extension VFSes for custom operating
    systems might not.
<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>It is not possible to change the database page size after entering WAL
    mode, either on an empty database or by using [VACUUM] or by restoring
    from a backup using the [backup API].  You must be in a rollback journal
    mode to change the page size.
<li>It is not possible to open [read-only WAL databases].
    The opening process must have write privileges for "<tt>-shm</tt>"
    [wal-index] shared memory file associated with the database, if that
    file exists, or else write access on the directory containing
    the database file if the "<tt>-shm</tt>" file does not exist.
<li>WAL might be very slightly slower (perhaps 1% or 2% slower)
    than the traditional rollback-journal approach
    in applications that do mostly reads and seldom write.
<li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
    "<tt>-shm</tt>" shared memory file associated with each
    database, which can make SQLite less appealing for use as an 
    [application file-format].
<li>There is the extra operation of [checkpointing] which, though automatic
    by default, is still something that application developers need to
    be mindful of.
<li><s>WAL works best with smaller transactions.  WAL does
    not work well for very large transactions.  For transactions larger than
    about 100 megabytes, traditional rollback journal modes will likely
    be faster.  For transactions in excess of a gigabyte, WAL mode may 
    fail with an I/O or disk-full error.
    It is recommended that one of the rollback journal modes be used for
    transactions larger than a few dozen megabytes.</s>
    Beginning with [version 3.11.0], WAL mode works as efficiently with
    large transactions as does rollback mode.

<h2>How WAL Works</h2>

<p>The traditional rollback journal works by writing a copy of the
original unchanged database content into a separate rollback journal file
and then writing changes directly into the database file.  In the
event of a crash or [ROLLBACK], the original content contained in the
rollback journal is played back into the database file to
revert the database file to its original state.  The [COMMIT] occurs
when the rollback journal is deleted.</p>

<p>The WAL approach inverts this.  The original content is preserved
in the database file and the changes are appended into a separate
WAL file.  A [COMMIT] occurs when a special record indicating a commit
is appended to the WAL.  Thus a COMMIT can happen without ever writing
to the original database, which allows readers to continue operating
from the original unaltered database while changes are simultaneously being
committed into the WAL.  Multiple transactions can be appended to the
end of a single WAL file.</p>

<tcl>hd_fragment ckpt checkpoint checkpointed checkpointing</tcl>

<p>Of course, one wants to eventually transfer all the transactions that
are appended in the WAL file back into the original database.  Moving
the WAL file transactions back into the database is called a

<p>Another way to think about the difference between rollback and 
write-ahead log is that in the rollback-journal
approach, there are two primitive operations, reading and writing,
whereas with a write-ahead log
there are now three primitive operations:  reading, writing, and

<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages.  (The
[SQLITE_DEFAULT_WAL_AUTOCHECKPOINT] compile-time option can be used to
specify a different default.) Applications using WAL do
not have to do anything in order to for these checkpoints to occur.  
But if they want to, applications can adjust the automatic checkpoint
threshold.  Or they can turn off the automatic checkpoints and run 
checkpoints during idle moments or in a separate thread or process.</p>

<tcl>hd_fragment concurrency {WAL concurrency}</tcl>

<p>When a read operation begins on a WAL-mode database, it first
remembers the location of the last valid commit record in the WAL.
Call this point the "end mark".  Because the WAL can be growing and
adding new commit records while various readers connect to the database,
each reader can potentially have its own end mark.  But for any
particular reader, the end mark is unchanged for the duration of the
transaction, thus ensuring that a single read transaction only sees
the database content as it existed at a single point in time.</p>

<p>When a reader needs a page of content, it first checks the WAL to
see if that page appears there, and if so it pulls in the last copy
of the page that occurs in the WAL prior to the reader's end mark.
If no copy of the page exists in the WAL prior to the reader's end mark,
then the page is read from the original database file.  Readers can
exist in separate processes, so to avoid forcing every reader to scan
the entire WAL looking for pages (the WAL file can grow to
multiple megabytes, depending on how often checkpoints are run), a
data structure called the "wal-index" is maintained in shared memory
which helps readers locate pages in the WAL quickly and with a minimum
of I/O.  The wal-index greatly improves the performance of readers,
but the use of shared memory means that all readers must exist on the
same machine.  This is why the write-ahead log implementation will not
work on a network filesystem.</p>

<p>Writers merely append new content to the end of the WAL file.
Because writers do nothing that would interfere with the actions of
readers, writers and readers can run at the same time.  However,
since there is only one WAL file, there can only be one writer at
a time.</p>

<p>A checkpoint operation takes content from the WAL file
and transfers it back into the original database file.
A checkpoint can run concurrently with readers, however the checkpoint
must stop when it reaches a page in the WAL that is past the read mark
of any current reader.  The checkpoint has to stop at that point because
otherwise it might overwrite part of the database file that the reader
is actively using.  The checkpoint remembers (in the wal-index) how far
it got and will resume transferring content from the WAL to the database
from where it left off on the next invocation.</p>

<p>Thus a long-running read transaction can prevent a checkpointer from
making progress.  But presumably every read transactions will eventually
end and the checkpointer will be able to continue.</p>

<p>Whenever a write operation occurs, the writer checks how much progress
the checkpointer has made, and if the entire WAL has been transferred into
the database and synced and if no readers are making use of the WAL, then
the writer will rewind the WAL back to the beginning and start putting new
transactions at the beginning of the WAL.  This mechanism prevents a WAL
file from growing without bound.</p>

<tcl>hd_fragment fast</tcl>
<h3>Performance Considerations</h3>

<p>Write transactions are very fast since they only involve writing
the content once (versus twice for rollback-journal transactions)
and because the writes are all sequential.  Further, syncing the
content to the disk is not required, as long as the application is
willing to sacrifice durability following a power loss or hard reboot.
(Writers sync the WAL on every transaction commit if
[PRAGMA synchronous] is set to FULL but omit this sync if
[PRAGMA synchronous] is set to NORMAL.)</p>

<p>On the other hand, read performance deteriorates as the WAL file
grows in size since each reader must check the WAL file for the content
and the time needed to check the WAL file is proportional
to the size of the WAL file.  The wal-index helps find content
in the WAL file much faster, but performance still falls off with
increasing WAL file size.  Hence, to maintain good read performance 
it is important to keep the WAL file size down by
running checkpoints at regular intervals.</p>

<p>Checkpointing does require sync operations in order to avoid
the possibility of database corruption following a power loss
or hard reboot.  The WAL must be synced to persistent storage
prior to moving content from the WAL into the database and the
database file must by synced prior to resetting the WAL.
Checkpoint also requires more seeking.
The checkpointer makes an effort to
do as many sequential page writes to the database as it can (the pages
are transferred from WAL to database in ascending order) but even
then there will typically be many seek operations interspersed among
the page writes.  These factors combine to make checkpoints slower than
write transactions.</p>

<p>The default strategy is to allow successive write transactions to
grow the WAL until the WAL becomes about 1000 pages in size, then to
run a checkpoint operation for each subsequent COMMIT until the WAL
is reset to be smaller than 1000 pages.  By default, the checkpoint will be
run automatically by the same thread that does the COMMIT that pushes
the WAL over its size limit.  This has the effect of causing most
COMMIT operations to be very fast but an occasional COMMIT (those that trigger
a checkpoint) to be much slower.  If that effect is undesirable, then
the application can disable automatic checkpointing and run the
periodic checkpoints in a separate thread, or separate process.
(Links to commands and interfaces to accomplish this are
<a href="#how_to_checkpoint">shown below</a>.)</p>

<p>Note that with [PRAGMA synchronous] set to NORMAL, the checkpoint
is the only operation to issue an I/O barrier or sync operation
(fsync() on unix or FlushFileBuffers() on windows).  If an application
therefore runs checkpoint in a separate thread or process, the main
thread or process that is doing database queries and updates will never
block on a sync operation.  This helps to prevent "latch-up" in applications
running on a busy disk drive.  The downside to
this configuration is that transactions are no longer durable and
might rollback following a power failure or hard reset.</p>

<p>Notice too that there is a tradeoff between average read performance
and average write performance.  To maximize the read performance,
one wants to keep the WAL as small as possible and hence run checkpoints
frequently, perhaps as often as every COMMIT.  To maximize
write performance, one wants to amortize the cost of each checkpoint
over as many writes as possible, meaning that one wants to run checkpoints
infrequently and let the WAL grow as large as possible before each 
checkpoint.  The decision of how often to run checkpoints may therefore
vary from one application to another depending on the relative read
and write performance requirements of the application.
The default strategy is to run a checkpoint once the WAL
reaches 1000 pages and this strategy seems to work well in test applications on 
workstations, but other strategies might work better on different 
platforms or for different workloads.</p>

<h2>Activating And Configuring WAL Mode</h2>

<p>An SQLite database connection defaults to 
[journal_mode | journal_mode=DELETE].  To convert to WAL mode, use the
following pragma:</p>

PRAGMA journal_mode=WAL;

<p>The journal_mode pragma returns a string which is the new journal mode.
On success, the pragma will return the string "<tt>wal</tt>".  If 
the conversion to WAL could not be completed (for example, if the [VFS]
does not support the necessary shared-memory primitives) then the
journaling mode will be unchanged and the string returned from the
primitive will be the prior journaling mode (for example "<tt>delete</tt>").

<a name="how_to_checkpoint"></a>
<h3>Automatic Checkpoint</h3>

<p>By default, SQLite will automatically checkpoint whenever a [COMMIT]
occurs that causes the WAL file to be 1000 pages or more in size, or when the 
last database connection on a database file closes.  The default 
configuration is intended to work well for most applications.
But programs that want more control can force a checkpoint
using the [wal_checkpoint pragma] or by calling the
[sqlite3_wal_checkpoint()] C interface.  The automatic checkpoint
threshold can be changed or automatic checkpointing can be completely
disabled using the [wal_autocheckpoint pragma] or by calling the
[sqlite3_wal_autocheckpoint()] C interface.  A program can also 
use [sqlite3_wal_hook()] to register a callback to be invoked whenever
any transaction commits to the WAL.  This callback can then invoke
[sqlite3_wal_checkpoint()] or [sqlite3_wal_checkpoint_v2()] based on whatever
criteria it thinks is appropriate.  (The automatic checkpoint mechanism
is implemented as a simple wrapper around [sqlite3_wal_hook()].)</p>

<h3>Application-Initiated Checkpoints</h3>

<p>An application can initiate a checkpoint using any writable database
connection on the database simply by invoking
[sqlite3_wal_checkpoint()] or [sqlite3_wal_checkpoint_v2()].
There are three subtypes of checkpoints that vary in their aggressiveness:
PASSIVE, FULL, and RESTART.  The default checkpoint style is PASSIVE, which
does as much work as it can without interfering with other database
connections, and which might not run to completion if there are
concurrent readers or writers.
All checkpoints initiated by [sqlite3_wal_checkpoint()] and
by the automatic checkpoint mechanism are PASSIVE.  FULL and RESTART
checkpoints try harder to run the checkpoint to completion and can only
be initiated by a call to [sqlite3_wal_checkpoint_v2()].  See the
[sqlite3_wal_checkpoint_v2()] documentation for additional information
on FULL and RESET checkpoints.

<h3>Persistence of WAL mode</h3>

<p>Unlike the other journaling modes, 
[journal_mode | PRAGMA journal_mode=WAL] is
persistent.  If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode.  In contrast, if
a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and
reopens the database will come back up in the default rollback mode of
DELETE rather than the previous TRUNCATE setting.</p>

<p>The persistence of WAL mode means that applications can be converted
to using SQLite in WAL mode without making any changes to the application
itself.  One has merely to run "<tt>PRAGMA journal_mode=WAL;</tt>" on the
database file(s) using the [command-line shell] or other utility, then
restart the application.</p>

<p>The WAL journal mode will be set on all
connections to the same database file if it is set on any one connection.

<tcl>hd_fragment {readonly} {read-only WAL databases}</tcl>
<h2>Read-Only Databases</h2>

<p>No SQLite database (regardless of whether or not it is WAL mode) is
readable if it is located on read-only media and it requires recovery.
So, for example, if an application crashes and leaves an SQLite database
with a [hot journal], that database cannot be opened unless the opening
process has write privilege on the database file, the directory
containing the database file, and the hot journal.  This is because the 
incomplete transaction left over from the crash must be rolled back prior 
to reading the database and that rollback cannot occur without write 
permission on all files and the directory containing them.</p>

<p>A database in WAL mode cannot generally be opened from read-only 
media because even ordinary reads in WAL mode require recovery-like

<p>An efficient implementation of the [WAL read algorithm] requires that
there exist a hash table in shared memory over the content of the WAL file.
This hash table is called the [wal-index].
The wal-index is in shared memory, and so technically it does not have 
to have a name in the host computer filesystem.  Custom
[VFS] implementations are free to implement shared 
memory in any way they see fit, but the default unix and windows 
drivers that come built-in with SQLite implement shared memory
using <a href="">mmapped files</a>
named using the suffix "<tt>-shm</tt>" and
located in the same directory as the database file.  The wal-index must
be rebuilt upon first access, even by readers, and so in order to open
the WAL database, write access is required on the "<tt>-shm</tt>" shared
memory file if the file exists, or else write access is required on the
directory containing the database so that the wal-index can be created if
it does not already exist.
This does not preclude custom VFS implementations that implement shared 
memory differently from being able to access read-only WAL databases, but
it does prevent the default unix and windows backends from accessing
WAL databases on read-only media.</p>

<p>Hence, SQLite databases should always be converted to 
[journal_mode | PRAGMA journal_mode=DELETE] prior to being transferred
to read-only media.</p>

<p>Also, if multiple processes are to access a WAL mode database, then
all processes should run under user or group IDs that give them write
access to the database files, the WAL file, the shared memory 
<tt>-shm</tt> file, and the containing directory.</p>

<tcl>hd_fragment bigwal {large WAL files} {avoiding large WAL files}</tcl>
<h2>Avoiding Excessively Large WAL Files</h2>

<p>In normal cases, new content is appended to the WAL file until the
WAL file accumulates about 1000 pages (and is thus about 4MB 
in size) at which point a checkpoint is automatically run and the WAL file
is recycled.  The checkpoint does not normally truncate the WAL file
(unless the [journal_size_limit pragma] is set).  Instead, it merely
causes SQLite to start overwriting the WAL file from the beginning.
This is done because it is normally faster to overwrite an existing file
than to append.  When the last connection to a database closes, that
connection does one last checkpoint and then deletes the WAL and its
associated shared-memory file, to clean up the disk.

<p>So in the vast majority of cases, applications need not worry about
the WAL file at all.  SQLite will automatically take care of it.  But
it is possible to get SQLite into a state where the WAL file will grow
without bound, causing excess disk space usage and slow queries speeds.
The following bullets enumerate some of the ways that this can happen
and how to avoid them.

<b>Disabling the automatic checkpoint mechanism.</b>
In its default configuration, SQLite will checkpoint the WAL file at the
conclusion of any transaction when the WAL file is more than 1000 pages
long.  However, compile-time and run-time options exist that can disable
or defer this automatic checkpoint.  If an application disables the
automatic checkpoint, then there is nothing to prevent the WAL file
from growing excessively.

<b>Checkpoint starvation.</b>
A checkpoint is only able to run to completion, and reset the WAL file,
if there are no other database connections using the WAL file.  If another
connection has a read transaction open,
then the checkpoint cannot reset the WAL file because
doing so might delete content out from under the reader.
The checkpoint will do as much work as it can without upsetting the
reader, but it cannot run to completion.
The checkpoint will start up again where it left off after the next
write transaction.  This repeats until some checkpoint is able to complete.

<p>However, if a database has many concurrent overlapping readers
and there is always at least one active reader, then
no checkpoints will be able to complete
and hence the WAL file will grow without bound.

<p>This scenario can be avoided by ensuring that there are "reader gaps":
times when no processes are reading from the 
database and that checkpoints are attempted during those times.
In applications with many concurrent readers, one might also consider 
running manual checkpoints with the [SQLITE_CHECKPOINT_RESTART] or
[SQLITE_CHECKPOINT_TRUNCATE] option which will ensure that the checkpoint
runs to completion before returning.  The disadvantage of using
readers might block while the checkpoint is running.

<b>Very large write transactions.</b>
A checkpoint can only complete when no other transactions are running, 
which means the WAL file cannot be reset in the middle of a write
transaction.  So a large change to a large database
might result in a large WAL file.  The WAL file will be checkpointed
once the write transaction completes (assuming there are no other readers
blocking it) but in the meantime, the file can grow very big.

<p>As of SQLite version 3.11.0, the WAL file for a single transaction
should be proportional in size to the transaction itself.  Pages that
are changed by the transaction should only be written into the WAL file
once.  However, with older versions of SQLite, the same page might be
written into the WAL file multiple times if the transaction grows larger
than the page cache.

<h2>Implementation Of Shared-Memory For The WAL-Index</h2>

<p>The [wal-index] is implemented using an ordinary file that is
mmapped for robustness.  Early (pre-release) implementations of WAL mode
stored the wal-index in volatile shared-memory, such as files created in
/dev/shm on Linux or /tmp on other unix systems.  The problem
with that approach is that processes with a different root directory
(changed via <a href="">chroot</a>)
will see different files and hence use different shared memory areas,
leading to database corruption.  Other methods for creating nameless
shared memory blocks are not portable across the various flavors of
unix.  And we could not find any method to create nameless shared
memory blocks on windows.  The only way we have found to guarantee
that all processes accessing the same database file use the same shared
memory is to create the shared memory by mmapping a file in the same
directory as the database itself.</p>

<p>Using an ordinary disk file to provide shared memory has the 
disadvantage that it might actually do unnecessary disk I/O by
writing the shared memory to disk.  However, the developers do not
think this is a major concern since the wal-index rarely exceeds
32 KiB in size and is never synced.  Furthermore, the wal-index 
backing file is deleted when the last database connection disconnects,
which often prevents any real disk I/O from ever happening.</p>

<p>Specialized applications for which the default implementation of
shared memory is unacceptable can devise alternative methods via a
custom [VFS].  
For example, if it is known that a particular database
will only be accessed by threads within a single process, the wal-index
can be implemented using heap memory instead of true shared memory.</p>

<tcl>hd_fragment noshm {WAL without shared memory}</tcl>
<h2>Use of WAL Without Shared-Memory</h2>

<p>Beginning in SQLite version 3.7.4, ^WAL databases can be created, read, and
written even if shared memory is unavailable as long as the
[locking_mode] is set to EXCLUSIVE before the first attempted access.
In other words, a process can interact with
a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.
^This feature allows WAL databases to be created, read, and written
by legacy [VFSes] that lack the "version 2" shared-memory
methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
[sqlite3_io_methods] object.</p>

<p>^(If [locking_mode | EXCLUSIVE locking mode]
is set prior to the first WAL-mode 
database access, then SQLite never attempts to call any of the
shared-memory methods and hence no shared-memory
wal-index is ever created.)^
^(In that case, the database connection remains in EXCLUSIVE mode
as long as the journal mode is WAL; attempts to change the locking
mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.)^
^The only way to change out of EXCLUSIVE locking mode is to first
change out of WAL journal mode.</p>

<p>^If NORMAL locking mode is in effect for the first WAL-mode database
access, then the shared-memory wal-index is created.  ^This means that the
underlying VFS must support the "version 2" shared-memory.
^If the VFS does not support shared-memory methods, then the attempt to
open a database that is already in WAL mode, or the attempt convert a
database into WAL mode, will fail.
^As long as exactly one connection is using a shared-memory wal-index, 
the locking mode can be changed freely between NORMAL and EXCLUSIVE.  
^It is only when the shared-memory wal-index is omitted, when the locking 
mode is EXCLUSIVE prior to the first WAL-mode database access, that the 
locking mode is stuck in EXCLUSIVE.</p>

<tcl>hd_fragment bkwrds {WAL backwards compatibility}</tcl>
<h2>Backwards Compatibility</h2>

<p>The database file format is unchanged for WAL mode.  However, the
WAL file and the [wal-index] are new concepts and so older versions of 
SQLite will not know
how to recover a crashed SQLite database that was operating in WAL mode
when the crash occurred.
^(To prevent older versions of SQLite (prior to version 3.7.0, 2010-07-22)
from trying to recover
a WAL-mode database (and making matters worse) the database file format
version numbers (bytes 18 and 19 in the [database header])
are increased from 1 to 2 in WAL mode.)^
Thus, if an older version of SQLite attempts to connect to an SQLite
database that is operating in WAL mode, it will report an error along
the lines of "file is encrypted or is not a database".</p>

<p>^(One can explicitly change out of WAL mode using a pragma such as

PRAGMA journal_mode=DELETE;

<p>^(Deliberately changing out of WAL mode changes the database file format
version numbers back to 1 so that older versions of SQLite can once again 
access the database file.)^</p>