WAL stands for Write Ahead Logging, it is a lot of database used as a mechanism to achieve atomic transaction, SQLite version 3.7.0 introduced in this feature.
2.WAL How does it work?
Before the introduction of WAL mechanism, SQLite using rollback journal mechanism to achieve atomic transaction.
rollback journal mechanism principle is: modify the database data in the file before you modify the data in the backup where paged in another place, then writes the modifications to the database file; if the transaction fails, the backup copy of the data back, undo changes; If the transaction is successful, delete the backup data, commit your changes.
WAL mechanism principle is: Modify does not write directly to the database file, but is written to another file called WAL; If the transaction fails, WAL in the record will be ignored, and undo changes; if the transaction is successful, It will be some time in the subsequent written back to the database file, and commit your changes.
WAL files and database files synchronized behavior is called checkpoint (checkpoint), which consists of SQLite performed automatically, the default is the WAL files accumulated to 1000, when modified; course, at the appropriate time, you can manually perform checkpoint, SQLite to provide the relevant interface. After execution checkpoint, WAL files will be emptied.
When read, SQLite WAL files in the search to find the final writing point, remember it, and ignore the write after this point (read, write and read these guarantees can be executed in parallel); Then, it determines The page where the data is to be read whether the WAL file, if, then read the data in the WAL files, if not, then directly read the data in the database file.
At the time of writing, SQLite will be written to the WAL files in the can, but must ensure that exclusive write, so can not be executed in parallel between write.
WAL in the implementation process, the use of shared memory technology, so all processes must read and write in the same machine, otherwise, can not guarantee data consistency.
3.WAL the advantages and disadvantages
Advantages:
1 can be completely executed concurrently read and write, do not block each other (but still can not write between concurrent).
2.WAL in most cases, have better performance (because you do not have to write each write two files).
3 disk I / O behavior is more easily predicted.
Disadvantages:
1 All programs must access the database on the same host, and supports shared memory technology.
(2) Each database corresponds to three files now:
3 When writing data to GB level, the database performance will drop.
4.3.7.0 Unrecognized prior SQLite WAL mechanism enabled database file.
4.WAL introduce compatibility issues
WAL is enabled, the database file format version number consists of an upgrade to a 2, so before 3.7.0 does not recognize SQLite WAL mechanism enabled database file.
Disabling WAL database file format will revert to a version number, which can be SQLite 3.7.0 versions prior identification.
5.WAL introduce performance problems
In general, WAL SQLite transaction performance will increase; but in some extreme cases, it will lead to a decline in SQLite transaction performance.
1 In the transaction execution time is longer, or the amount of data to be modified when the GB level, WAL files will be occupied, it will temporarily prevent the execution checkpoint (checkpoint WAL files will be empty), which will cause WAL files become very large , increasing the address period, resulting in a decline in read and write performance.
(2) When the checkpoint is executed, will reduce the time to read and write performance, so, WAL may cause periodic performance degradation.
6 associated with WAL PRAGMA and interface
PRAGMA journal_mode
PRAGMA wal_checkpoint
PRAGMA wal_autocheckpoint
sqlite3_wal_checkpoint
sqlite3_wal_autocheckpoint
sqlite3_wal_hook
No comments:
Post a Comment