关于 网站地图 文档 下载 许可 新闻 支持

Write-Ahead Logging

SQLite实现自动提交和回滚的默认方法是通过回滚日志。从 3.7.0版开始，可以使用新的"Write-Ahead Log"（以下称为"WAL"）选项了。
The default method by which SQLite implements 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.

这里是使用WAL替代回滚日志的优点和缺点。优点包括：
There are advantages and disadvantages to using WAL instead of a rollback journal. Advantages include:

1. 多数情况下WAL会有明显的速度提升。
   WAL is significantly faster in most scenarios.
2. WAL提供了更好的并发性，因为读取不再阻塞写入，写入也不再阻塞读取。读取和写入可以并发执行。
   WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
3. 使用WAL时磁盘I/O操作会更趋向连续性。
   Disk I/O operations tends to be more sequential using WAL.
4. WAL使用fsync()操作要少的多，因此数据库更不易在系统出现问题破坏fsync()系统调用时受到损害。
   WAL uses many fewer fsync() operations and is thus less vulnerable to problems on systems where the fsync() system call is broken.

但是也同样有缺点：
But there are also disadvantages:

1. WAL通常需要VFS支持共享内存原语（除了：不使用共享内存的WAL）。内置的unix和windows VFS都支持，但是自定义操作系统上第三方扩展的VFS可能不支持。
   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.
2. 所有使用同一数据库的进行必须在同一个主机上，WAL不支持网络文件系统。
   All processes using a database must be on the same host computer; WAL does not work over a network filesystem.
3. 在多个ATTACH数据库上执行事务只能在每个库上保证原子性，而不能在所有数据库整体上保持原子性。
   Transactions that involve changes against multiple ATTACHed databases are atomic for each individual database, but are not atomic across all databases as a set.
4. 在进入WAL模式后无法修改数据库页大小，即使是空数据库或者使用备份 API从备份恢复也都不行。只能在回滚日志模式下修改页大小。
   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.
5. 无法打开一个只读 WAL 数据库。如果数据库关联的"-shm" wal-index共享内存文件存在，那么开启的进程必须有这个文件的写入权限。否则，如果"-shm"文件不存在，那么则需要有包含数据库文件的目录的写入权限。
   It is not possible to open read-only WAL databases. The opening process must have write privileges for "-shm" 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 "-shm" file does not exist.
6. 当应用程序绝大部分都是读操作，只有很少的写入操作时，WAL可能会导致事务执行速度相对回滚日志方式略微变慢（大约变慢1%或2%）。
   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.
7. 每个数据库都会有两个关联的附加准永久文件，"-wal"文件和"-shm"共享内存文件。这不利于将SQLite当做应用程序文件格式使用。
   There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as an application file-format.
8. 有一个额外的操作checkpointing，虽然默认是自动执行的，但是这依然是需要应用开发者留意的。
   There is the extra operation of checkpointing which, though automatic by default, is still something that application developers need to be mindful of.
9. WAL最适合小事务。而非常大的事务却执行的不是非常好。对于超过100MB的事务，回滚日志模式会更快一些。对于超过1GB的事务，WAL模式可能会产生I/O或磁盘已满的错误。这里推荐在事务大小超过几十MB的使用时候回滚日志模式。
   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.

WAL是如何运行的
How WAL Works

传统的回滚日志的工作方式是将原始未改动的数据库内容复制到一个独立的回滚日志文件中，而将修改直接写入数据库文件中。在遇到崩溃或者ROLLBACK时，会将回滚日志中的原始内容复制回数据库文件中，并将数据库文件恢复到原始状态。当删除回滚日志则触发COMMIT。
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.

WAL基本是翻转过来。原始的内容保存在数据库文件中，而修改的内容追加到单独的WAL文件中。当执行COMMIT时，一个表示提交的特殊记录被追加到WAL中。因此，可以在没有写入原始数据库的情况下完成COMMIT，这样就允许原始未变的数据库上进行读取操作的同时修改被提交到WAL中。多个事务都可以追加到一个WAL文件的结尾。
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.

Checkpointing

当然，如果想最终将追加到WAL文件中的所有事务移回到原始数据库中。将WAL文件事务移回数据库中叫做"checkpoint"。
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 "checkpoint".

从另一个方式去思考回滚日志和WAL的区别，在回滚日志方式中，有两个原始操作，读取和写入，然而在WAL中，一共有三个原始操作：读取、写入和checkpointing。
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.

默认情况下，SQLite会在WAL文件达到1000页的临界点的时候自动执行chekpoint（可以使用SQLITE_DEFAULT_WAL_AUTOCHECKPOINT编译期选项来修改这个默认值）。应用程序不需要为这些checkpoint的发生做任何事情。但是如果需要，应用程序可以调整自动checkpoint的阀值。或者也可以关闭自动checkpoint然后在空闲期或者单独的线程或进程中运行checkpoint。
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.

并发
Concurrency

当在一个WAL模式数据库上开始读取操作时，会首先记住WAL中上次有效提交记录的位置。这个点叫做"end mark"。由于当若干个读取者正连接在数据库上时，WAL依然会增长并加入新提交的记录，所以，每个读取者可能拥有自己的end mark。但是，对于任何指定的读取者，end mark在事务期间是不会发生变化的，这样确保每一个独立的读取事务面对数据库内容就像其只存在于某一个时间点一样。
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.

当一个读取者需要一页内容时，其首先检查这个页是否存在WAL中，如果有，会读取WAL中读取者的end mark之前的这一页的最后一份拷贝。如果WAL中读取者的end mark之前没有这个页的拷贝，那么直接从原始数据库文件读取这一页。读取者可以再不同的进程中，所以为了避免强制每个读取者扫描这个WAL来寻找页（WAL文件可以增长到许多MB，这依赖于checkpoint的运行频率），会在共享内存中维护一个叫做"wal-index" 的数据结构，用来帮助读取者在WAL中使用最少的I/O快速定位页。wal-index极大的提高了读取者的性能，但是使用共享内存也就意味着所有读取者必须在同一台机器上。这也就是为什么WAL实现无法在网络文件系统上运行。
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.

写入者仅仅是在WAL文件的结尾追加新内容。由于写入者不需要面对读取者的操作做任何事儿，所以写入者和读取者可以同时运行。不过由于只有一个WAL文件，所以同一时刻只能有一个写入者。
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.

checkpoint操作提取WAL文件的内容，并将其传回到原始数据库文件中。checkpoint可以和读取者并发运行，但是当在WAL文件中遇到超过任何当前的读取者的读取标志的页时，checkpoint必须停止。checkpoint必须在这个点停止是因为否则这可能会覆盖数据库文件中读取者正在使用的页。checkpoint会记录（在wal-index）已经执行了多少，并且会从中断点开始恢复继续从WAL传输内容到数据库中。
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.

因此，一个长时间的读取事务会妨碍运行checkpoint的进程。但是可以假设每个读取事务最终都会结束，然后checkpoint可以继续运行。
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.

每当发生写操作时，写入者会检查checkpoint运行到什么进度，如果整个WAL已经被写入数据库中并完成了sync，而且没有读取者在使用WAL，那么写入者会将WAL回退到开始处，然后在WAL的开始处开始写入新的事务。这个机制阻止了WAL文件无休止的增长。
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.

性能相关的注意事项
Performance Considerations

写入事务会运行的非常快速，这是因为只需要执行一次写入操作（相对而言，回滚日志事务需要两次）并且写入全都是连续的。进一步说，甚至可以不需要将内容同步到磁盘上，只要应用程序可以牺牲断电或硬件重启后的完整性。（如果PRAGMA synchronous设置为FULL，那么每个事务提交时写入者都会同步WAL。如果PRAGMA synchronous设置为NORMAL，那么会忽略同步。）
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.)

另一方面，读取的性能会随着WAL文件尺寸的增长而恶化，这是因为每次读取必须检查WAL文件内容，并且检查WAL文件所需的时间是与WAL文件的大小成比例的。wal-index可以帮助在WAL文件中快速寻找内容，但是性能依然会随着WAL文件尺寸的增长而下降。因此，为了保持良好的读取性能，定期运行checkpoint以保持WAL文件较小的尺寸是非常重要的。
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.

为了避免在断电或硬件重启后数据库损坏，checkpoint需要做同步操作。WAL文件需要在移动数据到数据库中之前将数据库同步持久化，而数据库文件必须在WAL被重置之前进行同步。checkpoint还需要很多次的查找。check能够尽可能多的将页连续写入到数据库中（页按照升序从WAL移动到数据库中），但是即使这样，还是需要在页写入过程中穿插许多查找操作。这些因素结合到一起导致checkpoint执行的比写入事务慢。
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.

默认策略是将事务连续写入到WAL中，直到WAL达到1000页大小。这时，后续的每个COMMIT都会运行checkpoint操作，直到WAL重置为小于1000页。默认情况下，checkpoint会自动由执行COMMIT的线程执行，这会将WAL缩小到尺寸限制之下。这个影响会导致大多数的COMMIT操作都执行的非常快，而偶尔的COMMIT（触发checkpoint的）会非常慢。如果这个影响是无法接受的，那么应用程序可以禁用自动checkpoint，然后在单独的线程或进程上定期执行checkpoint。（完成这个的命令和接口在下方）
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 shown below.)

注意，当PRAGMA synchronous设置为NORMAL时，checkpoint是唯一会执行I/O barrier或sync（unix上的fsync()或windows上的FlushFileBuffers()）的操作。如果一个应用在单独的线程或进程中运行checkpoint，主线程或进程在做查询或更新的时候永远不会在sync操作室被阻塞。这有助于防止应用在磁盘驱动器繁忙的时候出现"latch-up"。这个配置的负面效果就是事务不是持久的，并且可能在断电或硬件复位之后无法恢复。
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.

还需要注意的是，在读平均性能和写平均性能之间需要进行权衡，想要最大化读性能就要尽可能的维持较小的WAL文件，因此更频繁的运行checkpoint，甚至是每次COMMIT都执行。想要最大化写性能则需要将每次checkpoint的开销分摊到尽可能多的写操作上，这就意味着要尽可能少的运行checkpoint并在每次执行checkpoint之前让WAL增长的尽量大。最总确定的checkpoint运行频率可能在不同的应用程序之间变化很大，这依赖于应用对读写性能需求的比例。默认策略是一旦WAL达到1000页则执行checkpoint，这个策略开起来在工作站上的测试应用中运行良好，但是在其他不同的平台上或不同的工作负载上其它的策略可能更好。
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.

启动和配置WAL模式
Activating And Configuring WAL Mode

一个SQLite数据库连接的默认是journal_mode=DELETE。想要转换为WAL模式需要使用下面的指令：
An SQLite database connection defaults to journal_mode=DELETE. To convert to WAL mode, use the following pragma:

PRAGMA journal_mode=WAL;

journal_mode PRAGMA返回一个表示新日志模式的字符串。如果成功，PRAGMA会返回字符串"wal"。如果到WAL的转换无法完成（例如，如果VFS不能支持必须的共享内存）那么，日志模式将不会改变，然会的字符串将是之前的日志模式（例如"delete"）。
The journal_mode pragma returns a string which is the new journal mode. On success, the pragma will return the string "wal". 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 "delete").

自动 checkpoint
Automatic Checkpoint

默认情况下，一旦一次COMMIT导致WAL文件尺寸达到或超过1000页，或者数据库上的最后一条数据库连接关闭，SQLite就会自动执行checkpoint。默认配置趋向于满足大多数的应用程序。但是想要进一步控制的程序可以使用wal_checkpoint pragma或者调用sqlite3_wal_checkpoint() C接口来强制执行一次checkpoint。如果想调整自动checkpoint的阀值或者彻底禁用自动checkpoint可以使用wal_autocheckpoint PRAGMA或者调用sqlite3_wal_autocheckpoint() C接口。 程序中还可以使用sqlite3_wal_hook()注册一个会在每次事务提交到WAL中时调用的回调函数。这个毁掉函数可以在认为合适的条件下执行sqlite3_wal_checkpoint()（自动checkpoint机制就是通过对sqlite3_wal_hook()做简单封装来实现的）。
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().)

WAL模式的持久化
Persistence of WAL mode

与其他日志模式不同，PRAGMA journal_mode=WAL是持久的。如果一个进程设置了WAL模式，那么关闭再重新打开数据库，数据库依然是WAL模式。相比而言，如果一个进程设置（举例）PRAGMA journal_mode=TRUNCATE，然后关闭再重新打开数据库，数据库会重新回到默认的DELETE回滚模式，而不是之前设置的TRUNCATE模式。
Unlike the other journaling modes, 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.

WAL模式的持久化意味着应用程序可以在不对程序做任何改动的情况下转为使用WAL模式。所要做的仅仅是通过命令行程序或者其它工具在数据库上运行"PRAGMA journal_mode=WAL;" ，然后重启应用程序即可。
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 "PRAGMA journal_mode=WAL;" on the database file(s) using the command-line shell or other utility, then restart the application.

WAL日志模式只要在数据库的任何一条连接上设置后，其它所有的连接都会被设置。
The WAL journal mode will be set on all connections to the same database file if it is set on any one connection.

只读数据库
Read-Only Databases

一旦一个数据库位于只读媒质中且需要恢复时，那么这个数据库将是不可读的（不管是否是WAL模式）。所以，例如，如果一个应用程序崩溃并退出了一个热日志模式的SQLite数据库，那么除非当前进程拥有数据库文件、包含数据库文件的目录和热日志的写权限，否则将无法打开这个数据库。这是因为崩溃中留下的未完成的事务需要在读取数据库之前回滚，而如果没有所有文件和包含他们的目录的写权限的话是无法完成回滚的。
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.

WAL模式的数据库通常无法再只读媒介上打开，因为WAL模式下即使是普通的读取也需要类似恢复的操作。
A database in WAL mode cannot generally be opened from read-only media because even ordinary reads in WAL mode require recovery-like operations.

一个高效的WAL 读取算法实现需要在共享内存中保留一个覆盖WAL文件内容的哈希表。这个哈希表叫做wal-index。wal-index在共享内存中，因此技术上讲它不需要在主机文件系统中有个名字。自定义的VFS实现中可以自由的使用任何合适的方法来实现共享内存，但是SQLite内置的默认unix和windows实现中使用映射文件实现的共享内存，这个映射文件命名使用"-shm"后缀，并且位于数据库文件相同的目录中。wal-index 在第一次访问的时候需要重建，即使是读取者也需要。因此，为了打开WAL数据库，就需要"-shm"共享内存文件（文件存在时）或者创建wal-index的包含数据库文件的目录（文件不存在）的写权限。这并不妨碍自定义的VFS实现中使用不同的共享内存实现来访问只读的WAL数据库，但是却阻止了默认的unix和windows后端访问只读的WAL数据库。
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 mmapped files named using the suffix "-shm" 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 "-shm" 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.

因此，SQLite数据库需要在转换为只读之前转换为PRAGMA journal_mode=DELETE。
Hence, SQLite databases should always be converted to PRAGMA journal_mode=DELETE prior to being transferred to read-only media.

并且，如果多个进程访问WAL模式数据库，那么运行所有进程的用户和组ID都需要拥有数据库文件、WAL文件、共享内存-shm文件以及包含其的目录的写权限。
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 -shm file, and the containing directory.

WAL-Index的共享内存实现
Implementation Of Shared-Memory For The WAL-Index

wal-index使用一个普通文件的内存映射来实现的以保证其鲁棒性。WAL模式的早期实现中将wal-index存储在易失共享内存中，例如linux下创建在/dev/shm下的文件或者unix系统中创建在/tmp下的文件。这种方法的问题是，不同根目录的进程（使用chroot修改）会使用不同的文件，因此而使用不同的共享内存空间，从而导致数据库损坏。其他创建无名共享内存块的方法无法兼容各种各样的unix。并且在windows上没有办法创建无名共享内存块。我们发现唯一能保证所有进程访问同一个数据库使用同样的共享内存的方法就是在数据库所在的目录下创建一个文件，然后使用内存映射的方式创建共享内存。
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 chroot) 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.

使用普通磁盘文件来提供共享内存也有缺点，那就是其实际上会做一些无用的磁盘I/O来将共享内存写入到磁盘上。不管怎样，开发者不用太关心这个事情，因为，wal-index极少会超过32KB大小并且永远不会同步。此外，wal-index文件在最有一个数据库连接断开时会被删除，这往往可以阻止所有实际的磁盘I/O发生。
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.

个别无法接受默认实现的共享内存的应用程序可以通过自定义VFS的方式来设计自己的实现。例如，移植一个数据库只会被同一个进程中的线程访问，那么wal-index可以使用堆内存来实现而不是真正的共享内存。
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.

使用无需共享内存的WAL
Use of WAL Without Shared-Memory

从SQLite3.7.4版开始，只要在第一次访问之前将locking_mode设置为EXCLUSIVE就可以在共享内存不可用的情况下创建、读取、写入WAL数据库。 也就是说，一个进程只要保证其是唯一一个访问数据库的进程，就可以不使用共享内存来处理WAL数据库。通过这个特性，即使是sqlite3_io_methods对象中缺少xShmMap、xShmLock、xShmBarrier和xShmUnmap这几个“第二版”共享内存方法的历史遗留VFS，也可以用来创建、读取和写入WAL数据库。
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.

如果在第一次WAL模式数据库访问之前设置了EXCLUSIVE 锁模式，那么SQLite就不会去调用任何共享内存函数，因此，也就不会创建共享内存wal-index文件了。 这种情况下，只要日志模式是WAL，数据库连接就会保持在EXCLUSIVE模式。执行"PRAGMA locking_mode=NORMAL;" 来修改锁模式是无效的。如果想改变EXCLUSIVE锁模式就只能先改变WAL日志模式。
If 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 "PRAGMA locking_mode=NORMAL;" are no-ops. The only way to change out of EXCLUSIVE locking mode is to first change out of WAL journal mode.

如果在第一次访问WAL模式数据库时NORMAL锁模式已经生效了，那么共享内存wal-index就已经创建了。这就意味着底层的VFS必须支持“第二版”共享内存。 如果VFS不支持共享内存方法，那么打开一个已有的WAL模式数据库或者将一个数据库转换为WAL模式都会失败。 只要有一个连接使用了共享内存wal-index，那么锁模式就可以自用的在NORMAL和EXCLUSIVE之间切换。只有当忽略了共享内存wal-index且在第一次访问WAL模式数据库之前锁模式是EXCLUSIVE时，锁模式才会卡在EXCLUSIVE模式。
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.

向后兼容性
Backwards Compatibility

WAL模式没有改动数据库库文件结构。而WAL文件和 wal-index文件都是新概念，因此老版本的SQLite无法知道如何恢复一个在WAL模式下发生崩溃的SQLite数据库。为了防止老版本的SQLite去尝试恢复一个WAL模式的数据库（且把事情变得更糟），数据库文件格式版本（数据库头的第18和19字节）在WAL模式中从1升级到了2。 因此，如果一个老版本的SQLite师徒连接一个WAL模式的SQLite数据库，会抛出一个错误和一行"file is encrypted or is not a database"提示。
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".

想明确切换出WAL模式可以使用一个PRAGMA，例如：
One can explicitly change out of WAL mode using a pragma such as this:

PRAGMA journal_mode=DELETE;

明确切换出WAL模式会将数据库文件格式版本修改会1。所以老版本的SQLite再一次可以访问数据库文件。
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.