Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.
|Comment:||Add new file doc/wal2.md to this branch.|
|Downloads:||Tarball | ZIP archive | SQL archive|
|Timelines:||family | ancestors | descendants | both | wal2|
|Files:||files | file ages | folders|
|User & Date:||dan 2019-01-11 14:58:03|
|15:39||Add the bgckpt.c extension to Makefile.in and Makefile.msc. check-in: 3712d625 user: drh tags: wal2|
|14:59||Merge latest wal2 changes (documentation only) into this branch. check-in: 820ba1cc user: dan tags: begin-concurrent-wal2|
|14:58||Add new file doc/wal2.md to this branch. check-in: b495dce1 user: dan tags: wal2|
|17:00||Reinstate assert() statements in os_unix.c that were removed to allow wal-mode SHARED locks to be taken over more than one locking slot (this branch no longer does that, so the assert() statements can go back in). check-in: 8445fb6d user: dan tags: wal2|
1 + 2 +Wal2 Mode Notes 3 +=============== 4 + 5 +## Activating/Deactivating Wal2 Mode 6 + 7 +"Wal2" mode is very similar to "wal" mode. To change a database to wal2 mode, 8 +use the command: 9 + 10 +> 11 + PRAGMA journal_mode = wal2; 12 + 13 +It is not possible to change a database directly from "wal" mode to "wal2" 14 +mode. Instead, it must first be changed to rollback mode. So, to change a wal 15 +mode database to wal2 mode, the following two commands may be used: 16 + 17 +> 18 + PRAGMA journal_mode = delete; 19 + PRAGMA journal_mode = wal2; 20 + 21 +A database in wal2 mode may only be accessed by versions of SQLite compiled 22 +from this branch. Attempting to use any other version of SQLite results in an 23 +SQLITE_NOTADB error. A wal2 mode database may be changed back to rollback mode 24 +(making it accessible by all versions of SQLite) using: 25 + 26 +> 27 + PRAGMA journal_mode = delete; 28 + 29 +## The Advantage of Wal2 Mode 30 + 31 +In legacy wal mode, when a writer writes data to the database, it doesn't 32 +modify the database file directly. Instead, it appends new data to the 33 +"<database>-wal" file. Readers read data from both the original database 34 +file and the "<database>-wal" file. At some point, data is copied from the 35 +"<database>-wal" file into the database file, after which the wal file can 36 +be deleted or overwritten. Copying data from the wal file into the database 37 +file is called a "checkpoint", and may be done explictly (either by "PRAGMA 38 +wal_checkpoint" or sqlite3_wal_checkpoint_v2()), or 39 +automatically (by configuring "PRAGMA wal_autocheckpoint" - this is the 40 +default). 41 + 42 +Checkpointers do not block writers, and writers do not block checkpointers. 43 +However, if a writer writes to the database while a checkpoint is ongoing, 44 +then the new data is appended to the end of the wal file. This means that, 45 +even following the checkpoint, the wal file cannot be overwritten or deleted, 46 +and so all subsequent transactions must also be appended to the wal file. The 47 +work of the checkpointer is not wasted - SQLite remembers which parts of the 48 +wal file have already been copied into the db file so that the next checkpoint 49 +does not have to do so again - but it does mean that the wal file may grow 50 +indefinitely if the checkpointer never gets a chance to finish without a 51 +writer appending to the wal file. There are also circumstances in which 52 +long-running readers may prevent a checkpointer from checkpointing the entire 53 +wal file - also causing the wal file to grow indefinitely in a busy system. 54 + 55 +Wal2 mode does not have this problem. In wal2 mode, wal files do not grow 56 +indefinitely even if the checkpointer never has a chance to finish 57 +uninterrupted. 58 + 59 +In wal2 mode, the system uses two wal files instead of one. The files are named 60 +"<database>-wal" and "<database>-wal2", where "<database>" is of 61 +course the name of the database file. When data is written to the database, the 62 +writer begins by appending the new data to the first wal file. Once the first 63 +wal file has grown large enough, writers switch to appending data to the second 64 +wal file. At this point the first wal file can be checkpointed (after which it 65 +can be overwritten). Then, once the second wal file has grown large enough and 66 +the first wal file has been checkpointed, writers switch back to the first wal 67 +file. And so on. 68 + 69 +## Application Programming 70 + 71 +From the point of view of the user, the main differences between wal and 72 +wal2 mode are to do with checkpointing: 73 + 74 + * In wal mode, a checkpoint may be attempted at any time. In wal2 75 + mode, the checkpointer has to wait until writers have switched 76 + to the "other" wal file before a checkpoint can take place. 77 + 78 + * In wal mode, the wal-hook (callback registered using 79 + sqlite3_wal_hook()) is invoked after a transaction is committed 80 + with the total number of pages in the wal file as an argument. In wal2 81 + mode, the argument is either the total number of uncheckpointed pages in 82 + both wal files, or - if the "other" wal file is empty or already 83 + checkpointed - 0. 84 + 85 +Clients are recommended to use the same strategies for checkpointing wal2 mode 86 +databases as for wal databases - by registering a wal-hook using 87 +sqlite3_wal_hook() and attempting a checkpoint when the parameter 88 +exceeds a certain threshold. 89 + 90 +However, it should be noted that although the wal-hook is invoked after each 91 +transaction is committed to disk and database locks released, it is still 92 +invoked from within the sqlite3_step() call used to execute the "COMMIT" 93 +command. In BEGIN CONCURRENT systems, where the "COMMIT" is often protected by 94 +an application mutex, this may reduce concurrency. In such systems, instead of 95 +executing a checkpoint from within the wal-hook, a thread might defer this 96 +action until after the application mutex has been released. 97 + 98 +