/ Check-in [b495dce1]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
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
SHA3-256: b495dce153f7f886f3dba09593f29ce2375718bf6508f2cfffd1af8071a995ae
User & Date: dan 2019-01-11 14:58:03
Wiki:wal2
Context
2019-03-08
15:39
Add the bgckpt.c extension to Makefile.in and Makefile.msc. check-in: 3712d625 user: drh tags: wal2
2019-01-11
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
2019-01-02
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added doc/wal2.md.

            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  +