Capture Inventory and Cursor at the Same Instant

In one line

A snapshot is not a pretty JSON file; it is a promise that binds a state and the last event that state includes at the same point in time.

Why this was needed

The warehouse stock is 7 and the last number is 0 when the display board requests a full recovery. Suppose the server reads last=0 first, and in the meantime a receipt of 5 is committed. When it then reads the stock, it gets total=12. Each value is one that really existed, but the combination of last=0 and total=12 is a state that never existed. The display board that installs this snapshot receives the +5 of event 1 again and shows 17.

This mistake is not caught by JSON syntax checks, type checks, or file hash comparison. The JSON was produced by the server itself, so the origin is right, the numbers are within range, and no bytes changed in transit. The fault is that the two queries read different points in time. You must distinguish what an integrity check confirms from what it does not. Both validating the format strictly and building a semantically consistent snapshot are necessary.

How it works

In the lab, export_snapshot reads the checkpoint's epoch and last first inside a read transaction and then reads stock.total. The between hook in the middle is a test point that lets another connection commit a new event. The return value has the four keys version, epoch, last, and total, and version is the integer 1. The two queries in the same transaction form a single state, and after the transaction ends you separately check that a fresh query shows the new state.

It uses the read-point isolation of SQLite WAL mode. Even if another connection commits while you read, that read transaction can keep its earlier point in time. Conversely, if you start the export with BEGIN IMMEDIATE, it claims the write slot and blocks the test's other connection. Writing to the source needs serialization, but an export that only reads does not have to block every receipt. The intent must differ between when you use a read transaction and when you use a write transaction.

In the test, after the reader connection's first query, the writer connection commits +5, and then the reader makes its second query. The result must be the earlier cursor and the earlier stock, and when you look at the writer again, the new stock must be there. We do not wait for a race to happen by luck with a fake sleep. Because the hook directly specifies the point between the queries, the same mistake produces the same failure every time. This verifies the logical overlap of concurrent reads and writes; it is not a load test that measures the throughput of many threads.

The process of putting the exported snapshot into a replica must also be atomic. Deleting the old events, putting in the new stock, and then changing the cursor are all one transaction. If the process goes down midway, either the old state must remain intact or the new state must remain intact. If old total and new last get mixed, the next replay misses events. A response vanishing after the commit finished does not mean there was no install, so you design reinstalling the same snapshot to leave the state unchanged and return False.

What it looks like in the field

Copying a single DB file to build a snapshot is different from exporting the logical state. In WAL mode, a separate file may hold finalized content that has not been reflected yet. This lab does not copy the source .db file; it fetches only the business state it needs with queries. A real backup must be verified separately with the backup API of the DB you use and a recovery procedure. You do not say that you also have a backup to restore from a server failure just because you made JSON for the display board.

Another operational problem is extending the read time without limit. If you hold a transaction while sending the snapshot over the network, a slow receiver can hold the DB's old state. Here we read the small fixed metadata and the integer stock into memory and then close the transaction. A real large state needs design for splitting, checksums, completion markers, access permissions, and resuming transfers. An example with one integer does not remove that cost.

The version is also something to observe. The SQLite version reported by the Linux lab image we checked is 3.45.1. The official WAL documentation explains that the WAL-reset defect was fixed in 3.51.3 and later and in some backports. This lab is a finite single write flow that turns off each connection's automatic checkpoint and creates no concurrent checkpoint work. It also closes the connections in order after writing is done. This is not a proposal of safe default settings for a multi-writer production server. In a real deployment, you must check whether your distribution includes the patch and the fix release, and you do not declare the patch state from a version string alone.

What you will do in the next check

In the quiz that follows, you judge the difference between a mismatched snapshot and a write lock. In the comprehensive lab after it, you implement export_snapshot and install_snapshot. If you insert a commit between the two SELECTs wrongly, the actual value total=12 and the expected value total=7 show up together. At the install process's after-clear and after-install points, you put in an exception and a real child termination respectively. You observe separately whether the rollback of exception handling is correct and whether the DB recovers even when the cleanup code does not run at all.

Reference: SQLite isolation at a read point, WAL files, concurrency, and known defects. This lab's stock JSON is not a backup of the whole database.