Detailed introduction to the WAL mechanism in SQLite

1. What is WAL?

The full name of WAL is Write Ahead Logging, which is a mechanism used in many databases to implement atomic transactions. SQLite introduced this feature in version 3.7.0.

2. How does WAL work?

Before introducing the WAL mechanism, SQLite used the rollback journal mechanism to implement atomic transactions.

The principle of the rollback journal mechanism is: before modifying the data in the database file, the data in the page where the modification is located is backed up in another place, and then the modification is written to the database file; if the transaction fails, the backup data is copied back and the modification is cancelled; if the transaction is successful, the backup data is deleted and the modification is submitted.

The principle of the WAL mechanism is that modifications are not written directly to the database file, but to another file called WAL; if the transaction fails, the records in the WAL will be ignored and the modification will be cancelled; if the transaction is successful, it will be written back to the database file at some subsequent time and the modification will be submitted.

The behavior of synchronizing WAL files and database files is called checkpoint. It is automatically executed by SQLite. By default, when the WAL files accumulate to 1,000 pages of modification; of course, checkpoint can also be manually executed at appropriate times, and SQLite provides related interfaces. After checkpoint is executed, the WAL file will be cleared.

When reading, SQLite will search in the WAL file, find the last write point, remember it, and ignore the write point after this (this ensures that read and write and read can be executed in parallel); then it determines whether the page where the data to be read is in the WAL file, if it is, read the data in the WAL file, and if it is not, read the data in the database file directly.

When writing, SQLite can write it to the WAL file, but it must ensure exclusive writing, so it cannot be executed in parallel between writing.

During the implementation of WAL, shared memory technology is used, so all read and write processes must be on the same machine, otherwise, data consistency cannot be guaranteed.

3. Advantages and disadvantages of WAL

advantage:

1. Reading and writing can be executed concurrently completely without blocking each other (but it still cannot be concurrent between writes).
In most cases, better performance (because there is no need to write two files every time you write).
3. Disk I/O behavior is easier to predict.

shortcoming:

1. All programs that access the database must be on the same host and support shared memory technology.
2. Each database now corresponds to 3 files: <yourdb>.db, <yourdb>-wal, <yourdb>-shm.
3. When the written data reaches GB level, the database performance will decline.
SQLite before 4.3.7.0 cannot recognize database files with WAL mechanism enabled.

4. Compatibility issues introduced by WAL

After WAL is enabled, the version number of the database file format is upgraded from 1 to 2. Therefore, SQLite before 3.7.0 cannot recognize database files with WAL mechanism enabled.

Disabling WAL will restore the version number of the database file format to 1, so it can be recognized by versions prior to SQLite 3.7.0.

5. Performance issues introduced by WAL

In general, WAL will improve SQLite's transaction performance; but in some extreme cases, it will lead to a degradation in SQLite's transaction performance.

1. When the transaction execution time is long or the amount of data to be modified reaches GB level, the WAL file will be occupied, which will temporarily prevent the execution of checkpoint (checkpoint will clear the WAL file), which will cause the WAL file to become large, increase the addressing time, and ultimately lead to a degradation of read and write performance.
2. When checkpoint is executed, the read and write performance at that time will be reduced, so WAL may cause periodic performance degradation.

VI. PRAGMA and interfaces related to WAL