WAL & deleted chats

1 How the write-ahead log works

Every transaction appends one frame per changed page to chat.db-wal. A frame is a 24-byte header plus a complete copy of the page. The last frame of a transaction is marked as a commit frame. The main file is not touched.

To read page P, SQLite looks for the newest valid frame that holds page P (the -shm file is an index that makes this fast) and uses the main file only if there is none. A checkpoint copies the newest version of each page back into the main file. After that the WAL is either truncated, or restarted: new frames are written from the beginning again, over the old ones. Frames that are not overwritten stay in the file and are recognised as old by their salts.

2 The WAL header: salts and checkpoints

The WAL header holds the page size and two salts. At every restart salt-1 is incremented and salt-2 gets a new random value. Every frame copies both salts into its own header, so a frame from an earlier generation of the log can be told apart. The checkpoint sequence counts the restarts: in the sample it is 2.

The two checksums are a running sum over 32-bit words. The header's checksum seeds a chain that continues through every frame header (first 8 bytes) and page image, so each frame's checksum also vouches for all frames before it. The magic number says which byte order the words are read in: 0x377F0682 means little-endian, which is what ARM and x86 phones and PCs write.

3 Frames and which ones count

SQLite accepts a frame only if three things hold:

The table below applies these rules to the sample WAL. Click a frame number to jump to its hex view.

Frame 1 is a commit frame for page 2 (the messages table): its commit size says the database has 4 pages after this transaction.

Frame 2 is another version of page 2, written by a later transaction. It is the newest valid frame for page 2, so this is the page a normal query sees.

Frame 3 holds page 3 (contacts), but its salt-1 is one lower than the header's: it was written before the last restart and survived because the new generation has only two frames so far. SQLite ignores it. Its content still matters to you: a stale frame can hold an older version of a page than anything else in the file. Here it happens to match the main file, because that version was checkpointed.

4 Recovering the deleted message

Put the three versions of page 2 side by side:

Sam sent two messages a minute apart. The second was deleted before any checkpoint, so the main file never saw it, and the current page only has its remains. Frame 1 still holds the complete row: contact, direction, timestamp and text. A normal query returns only message 8:

Try this: in frame 1's page image, find cell 7 and read its sent_at value. Then find the same text in frame 2: which field of the page header tells you that those bytes are no longer part of any cell? And why does the deleted cell sit in the unallocated space rather than in a freeblock?

5 Pitfalls

• Checkpoints erase history. After a checkpoint the newest pages are in the main file and the WAL may be truncated or restarted. Opening the original database in a normal tool can trigger exactly that.
• Restarts overwrite from the front. The oldest surviving frames are at the end of the file, with old salts. Tools that stop at the first invalid frame never show them.
• Same page, many versions. A busy chat page may appear in dozens of frames. Compare versions by frame number, not by file offset, and say which one a value came from.
• The -shm file is a cache. SQLite can rebuild it from the WAL, but acquire and hash it anyway: it records which frames were in use at the moment of acquisition.

6 Try it yourself

Answer from the hex views and the frame table above. Numbers can be typed in decimal or hex (0x…).