Detailed introduction of Wal mechanism in SQLite _sqlite

One, what is Wal?

The full name of Wal is the write ahead Logging, a mechanism used in many databases to implement atomic transactions, which SQLite introduced in version 3.7.0.

Second, how Wal work?

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

The principle of the rollback journal mechanism is to back up the data in the page of the modification in a different place before modifying the data in the database file, and then write the changes back to the database file, and if the transaction fails, the backup data is returned and the modification is revoked; If the transaction succeeds, The backup data is deleted and the modification is committed.

The principle of Wal mechanism is: The modification is not written directly to the database file, but is written to another file called Wal, and if the transaction fails, the record in the Wal is ignored, the modification is undone, and if the transaction succeeds, it will be written back to the database file and submitted for modification at a later time.

The behavior of synchronizing Wal files and database files is called Checkpoint (checkpoint), which is automatically performed by SQLite, which defaults to the time the Wal file accumulates to 1000 pages of modification, and, of course, the checkpoint can be executed manually at the appropriate time, The SQLite provides the associated interface. After the checkpoint is executed, the Wal file is emptied.

At the time of 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 both read and write and read-read can be executed in parallel); then it determines whether the page to be read is in the Wal file, and if so, read the data in the Wal file. If not, the data in the database file is read directly.

At the time of writing, SQLite writes it to the Wal file, but it must be guaranteed to be exclusive, so write-write cannot be executed in parallel.

Wal in the process of implementation, the use of shared memory technology, therefore, all the reading and writing process must be on the same machine, otherwise, data consistency can not be guaranteed.

Iii. advantages and disadvantages of Wal

Advantages:

1. Read and write can be executed completely concurrently, without blocking each other (but still not concurrent between writes).
2.WAL has better performance in most cases (because you don't need to write two files each time you write).
3. Disk I/O behavior is more easily predicted.

Disadvantages:

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 writing data reaches GB, database performance will decrease.
The WAL mechanism-enabled database file was not recognized by SQLite before 4.3.7.0.

Iv. compatibility issues introduced by Wal

After Wal is enabled, the version number of the database file format is upgraded from 1 to 2, so the Wal-enabled database files are not recognized by the SQLite before 3.7.0.

Disabling Wal restores the version number of the database file format to 1, which can be identified by the version prior to SQLite 3.7.0.

V. Performance problems introduced by Wal

In general, Wal can improve the transaction performance of SQLite, but in some extreme cases, it can lead to sqlite transaction performance degradation.

1. When the transaction takes longer to execute or the amount of data to be modified is GB, the Wal file is occupied and it temporarily blocks checkpoint execution (checkpoint clears Wal files), which causes the Wal file to become large and increase addressing time. Eventually leads to a drop in read and write performance.
2. When checkpoint executes, it reduces the read and write performance at that time, so Wal can cause periodic performance degradation.

Vi. pragma and interfaces related to Wal

Copy Code code as follows:

PRAGMA Journal_mode
PRAGMA Wal_checkpoint
PRAGMA Wal_autocheckpoint
Sqlite3_wal_checkpoint
Sqlite3_wal_autocheckpoint
Sqlite3_wal_hook