.SQLITE File Format – Journal modes

The two modes

The side files take the database’s full file name and add -journal, -wal or -shm: app.db has app.db-journal, app.db-wal and app.db-shm. Of the databases examined for this site, 150 were in rollback mode and 39 in WAL mode.

Copying a database file alone while it is in use can lose recent changes: in WAL mode, committed changes may still be in the -wal file, and in rollback mode a -journal file left by a crash is needed to repair the database. Copy the side files with it, or use SQLite’s backup API or VACUUM INTO.

Rollback journal mode

The original way, used by every SQLite 3 and still the default. Before SQLite changes a page of the database, it copies the page’s original content to name-journal in the same folder. When the transaction commits, the journal is no longer needed, and SQLite gets rid of it in one of three ways, chosen with PRAGMA journal_mode:

Each of these makes the journal invalid, and that is the moment of commit. If the program stops before it, a journal with a valid header is left beside the database: a hot journal. The next program to open the database plays the journal back, restoring the original pages, so the half-finished transaction disappears.

The modes MEMORY and OFF keep the journal in memory or keep none; they leave no file, and a crash can damage the database.

Write-ahead log mode

From SQLite 3.7.0 (2010). Header bytes 18 and 19 are both 2. The database file is left alone during a transaction: the changed pages are appended to name-wal, and a commit is a frame marked as the end of a transaction. Readers look in the write-ahead log first and in the database for pages it does not hold, so readers and a writer can work at the same time.

From time to time, and when the last connection closes, a checkpoint copies the newest version of each page back into the database. Until then the database file alone can be out of date. When the last connection closes, SQLite normally checkpoints and then deletes the -wal and -shm files.

A library older than 3.7.0 sees a read version of 2 and refuses to open the file. Setting PRAGMA journal_mode=DELETE takes a database out of WAL mode and sets the bytes back to 1.

The -journal file

A rollback journal starts with a header beginning with the 8 bytes D9 D5 05 F9 20 A1 63 D7, giving the page count, a random nonce, the database’s size before the transaction, the sector size and the page size, padded to the sector size. Then come the page records: each a 4-byte page number, the page’s original content and a 4-byte checksum. A journal can hold more than one segment, each with its own header. The full layout is a format of its own:

• SQLite rollback journal (-journal) – the header, the page records, the checksum and segments, with a real journal

The -wal file

A write-ahead log starts with a 32-byte header: the magic number 0x377F0682 or 0x377F0683, the format version 3007000, the page size, a checkpoint counter, two salts and a checksum. Then come the frames: each a 24-byte frame header (the page number, the database size for a commit frame, the salts again and a running checksum) and one page. The full layout is a format of its own:

• SQLite write-ahead log (-wal) – the header, the frames and the checksum, with a real log

The -shm file

name-shm is the WAL index: a hash table that tells readers which frame of the write-ahead log holds the newest copy of each page. SQLite maps it into memory and shares it between every connection to the database, which is why WAL mode works only between programs on the same computer. Its numbers are in the byte order of that computer, and it holds nothing that cannot be rebuilt: SQLite rebuilds it from the -wal file whenever it is missing or out of date. It is not a format meant for other programs to read, and this site has no page of its own for it; SQLite describes it in The WAL-index File Format.