Detailed introduction of WAL mechanism in SQLite, sqlitewal Mechanism

Detailed introduction of WAL mechanism in SQLite, sqlitewal Mechanism

1. What is WAL?

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

Ii. How does 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: before modifying data in the database file, back up the data in the modification page in another place before writing the modification into the database file; if the transaction fails, the backup data is copied back to cancel the modification. If the transaction succeeds, the backup data is deleted and the modification is submitted.

The principle of the WAL mechanism is that the modification is not directly written 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 revoked; if the transaction succeeds, it will be written back to the database file at a later time and submitted for modification.

The behavior of synchronizing WAL files and database files is called checkpoint. It is automatically executed by SQLite. By default, WAL files are accumulated to 1000 pages for modification. Of course, when appropriate, you can also manually execute checkpoint. SQLite provides related interfaces. After the checkpoint is executed, the WAL file is cleared.

During 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/write and read can be executed in parallel ); then, it determines whether the page of the data to be read is in the WAL file. If it is in, it reads the data in the WAL file. If not, it reads the data in the database file directly.

During writing, SQLite can write the data to the WAL file, but it must be exclusive. Therefore, writing and writing cannot be executed in parallel.

WAL uses the shared memory technology during implementation. Therefore, all read/write processes must be on the same machine. Otherwise, Data Consistency cannot be guaranteed.

Iii. Advantages and Disadvantages of WAL

Advantages:

1. The read and write operations can be fully executed concurrently without blocking each other (but the write operations cannot be concurrently executed ).
2. WAL has better performance in most cases (because you do not need to write two files each time ).
3. Disk I/O behavior is easier to predict.

Disadvantages:

1. All Programs accessing the database must be on the same host and support shared memory technology.
2. Each database now corresponds to three files: <yourdb>. db, <yourdb>-wal, <yourdb>-shm.
3. When the write data reaches the GB level, the database performance will decrease.
SQLite earlier than 4.3.7.0 cannot identify the database files that have the WAL mechanism enabled.

Iv. 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 identify the database file with WAL mechanism enabled.

Disabling WAL restores the database file format version to 1, which can be recognized by Versions earlier than SQLite 3.7.0.

V. performance problems introduced by WAL

In general, WAL will improve the transaction performance of SQLite, but in some extreme cases, it will lead to a decline in the transaction performance of SQLite.

1. when the transaction execution time is long or the data volume to be modified reaches the GB level, the WAL file will be occupied and it will temporarily stop the checkpoint execution (the checkpoint will clear the WAL file ), this will cause the WAL file to become very large, increase the addressing time, and eventually lead to a decline in read/write performance.
2. When the checkpoint is executed, the read/write performance at that time is reduced. Therefore, WAL may cause periodic performance degradation.

Vi. WAL-related PRAGMA and interfaces
Copy codeThe Code is as follows:
PRAGMA journal_mode
PRAGMA wal_checkpoint
PRAGMA wal_autocheckpoint
Sqlite3_wal_checkpoint
Sqlite3_wal_autocheckpoint
Sqlite3_wal_hook