<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>
<ol>
<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
concurrently.
<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.
</ol>
<p>But there are also disadvantages:</p>
<ol>
<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>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.
</ol>
<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>
<h3>Checkpointing</h3>
<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
"<i>checkpoint</i>".<p>
<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
checkpointing.</p>
<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>
<h3>Concurrency</h3>
<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>
<blockquote><pre>
PRAGMA journal_mode=WAL;
</pre></blockquote>
<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()] to for a checkpoint based on whatever
criteria it thinks is appropriate. (The automatic checkpoint mechanism
is implemented as a simple wrapper around [sqlite3_wal_hook()].)</p>
<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.
</p>
<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
operations.</p>
<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="http://en.wikipedia.org/wiki/Mmap">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>
<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="http://en.wikipedia.org/wiki/Chroot">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 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
this:</p>
<blockquote><pre>
PRAGMA journal_mode=DELETE;
</pre></blockquote>
<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>