.DB-WAL File Format

Introduction

A -wal file is the write-ahead log of an SQLite 3 database in WAL mode. It sits in the same folder as the database and takes its full name with -wal added: app.db has app.db-wal, notes.sqlite has notes.sqlite-wal. SQLite appends the pages each transaction changes to this file instead of writing them into the database, and later copies them back in a checkpoint. Until then, the newest data is in the log, not in the database.

Write-ahead logging came with SQLite 3.7.0 in July 2010, and the file’s layout has not changed since: its format version field still holds 3007000. SQLite documents the format in full. A write-ahead log is useful only with its database; on its own it is a set of pages, and only SQLite writes it.

A -wal file dropped on any page of this site opens in the explorer, which shows the header and every frame, checks each checksum and says which frames are committed, which are valid but not committed, and which are left over.

Structure

A 32-byte header, then any number of frames, each a 24-byte frame header and one database page. Every number is big-endian.

WAL header

Unlike the database header, the page size here is a plain 32-bit number, so 65,536 is written as it is.

Frames

Each frame holds one database page as a transaction wrote it.

Frame n starts at 32 + (n − 1) × (24 + page size). A frame with a commit size of not zero is a commit frame: it ends a transaction, and the frames from the previous commit frame up to it are that transaction’s pages. The same page can appear in many frames; the latest valid one is the current version.

Checksum

The checksum is a pair of 32-bit numbers, s0 and s1, computed over 32-bit words. The words are read big-endian if the magic number is 0x377F0683 and little-endian if it is 0x377F0682; the result is always stored big-endian. SQLite writes the order of the machine that made the file.

1. Start with the previous checksum: s0 = s1 = 0 for the header, or the checksum in the frame before for a frame (for frame 1, the header’s checksum).
2. Take the input two words at a time, x(i) and x(i + 1):
   • s0 = s0 + x(i) + s1
   • s1 = s1 + x(i + 1) + s0
   with all sums in 32 bits, the carry dropped.
3. Store s0 as checksum-1 and s1 as checksum-2.

The header’s checksum covers bytes 0 to 23. A frame’s covers, continuing from the frame before, the first 8 bytes of its frame header and then its page.

A frame is valid only while its salts equal the header’s and its checksum matches the running checksum. A reader uses the valid frames, from the first, up to the last commit frame. Frames after that are an unfinished transaction, or left from before the last reset, and are ignored.

Checkpoints and resets

A checkpoint copies the newest committed version of each page back into the database. When every frame has been copied and no reader still needs the log, the next writer starts again at frame 1, overwriting the old frames, and writes a new header: salt-1 incremented, a new random salt-2, the checkpoint sequence counted on. The old frames still in the file then have the wrong salts and are no longer valid, so the log never has to be emptied or truncated to be reused.

The -shm file beside the log, the WAL index, tells readers which frame holds each page. It is a shared-memory file in the byte order of the computer, rebuilt from the -wal file whenever it is needed, and has no page of its own here; see Journal modes.

A real log

A write-ahead log made with the sqlite3 shell for this page, copied while the database was open: a database with 1,024-byte pages in WAL mode, then CREATE TABLE t(a,b) and an INSERT of one row. The file is 3,176 bytes: the header and three frames of 24 + 1,024 bytes.

00000000  37 7F 06 82 00 2D E2 18 00 00 04 00 00 00 00 00
00000010  EE DA B6 03 26 FC 89 53 D8 85 DE 01 E3 EB B1 CC
00000020  00 00 00 01 00 00 00 00 EE DA B6 03 26 FC 89 53
00000030  CA F2 FC FE D3 C3 C8 61 53 51 4C 69 74 65 20 66

• 37 7F 06 82: little-endian checksum words, as written on a little-endian computer. 00 2D E2 18 = 3,007,000. 00 00 04 00: pages of 1,024 bytes. Checkpoint sequence 0.
• Salt-1 EE DA B6 03, salt-2 26 FC 89 53, then the header checksum D8 85 DE 01 E3 EB B1 CC.
• Frame 1, at offset 32: page 1, commit size 0, the same two salts, its checksum. Its page is page 1 of the database, with the header bytes 18 and 19 set to 02 02.

Page 2 appears twice. A reader takes frame 3’s copy, the latest committed one.

Telling it apart

The file starts with 37 7F 06 82 or 37 7F 06 83, followed by 00 2D E2 18, the format version 3007000.

The explorer’s parser checks the magic number, the format version and a page size that is a power of two from 512 to 65,536. A file too short for the 32-byte header is turned down. It then recomputes the header checksum and the running checksum of every frame, and reports a mismatch as a problem rather than turning the file down: a log with left-over frames after the valid ones is normal. The four -wal files examined for this site, from one program, all had 4,096-byte pages, little-endian checksums and checkpoint sequence 0, and every one of their 1 to 12 frames was valid and committed.

Related formats

• SQLite 3 database – the database the log belongs to
• SQLite rollback journal (-journal) – the other way SQLite protects a transaction: the old pages instead of the new

Links