Test SQLite Read Snapshots for iOS on a Cloud Mac

A background sync has committed new records, but refreshing the list still shows old values. Rebuilding the cache makes the problem disappear temporarily, only for it to return on the next batch read. Before investigating further, check whether the reads are still running inside the same SQLite transaction. Reproducing the behavior with two independent connections on a cloud Mac makes it easier to distinguish database semantics from UI caching issues than repeatedly clicking through a remote desktop.

Define the Read Contract First

This article covers only local, file-backed databases in WAL mode. Its conclusions do not extend to in-memory databases or shared-cache configurations. Have Python 3 with the SQLite module available, then run python3 -c 'import sqlite3; print(sqlite3.sqlite_version)' and save the output with your test results.

The acceptance criteria are straightforward: after the reader's first query, the writer can commit; the existing read transaction still sees the old value; and a subsequent query sees the new value only after that read transaction ends. Here, “old” means transactionally consistent, not lost data.

Do not commit arbitrarily during a multi-table read that requires a consistent view just to make the list feel “more real-time.” First decide whether the operation needs a single snapshot or the latest committed data on every query.

The test must use a real temporary file and two connections. Two ordinary :memory: connections each have their own database and cannot verify this interaction.

Build a Minimal Two-Connection Test

Save the following as snapshot_check.py and run python3 snapshot_check.py. The script does not connect to your application database, and its temporary directory is deleted automatically when the context exits. Every cursor is fetched from immediately and closed, so an unfinished statement is not mistaken for an explicit transaction issue.

import sqlite3
import tempfile
from pathlib import Path

def one(db, sql):
    cursor = db.execute(sql)
    try:
        return cursor.fetchone()
    finally:
        cursor.close()

with tempfile.TemporaryDirectory() as root:
    path = Path(root) / "snapshot.sqlite"
    writer = sqlite3.connect(path, isolation_level=None)
    reader = sqlite3.connect(path, isolation_level=None)
    try:
        assert one(writer, "PRAGMA journal_mode=WAL")[0] == "wal"
        writer.execute("PRAGMA wal_autocheckpoint=0")
        writer.execute("CREATE TABLE item(id INTEGER PRIMARY KEY, value TEXT)")
        writer.execute("INSERT INTO item VALUES(1, 'old')")
        one(writer, "PRAGMA wal_checkpoint(TRUNCATE)")

        reader.execute("BEGIN")
        assert one(reader, "SELECT value FROM item WHERE id=1")[0] == "old"

        writer.execute("BEGIN IMMEDIATE")
        writer.execute("UPDATE item SET value='new' WHERE id=1")
        writer.execute("COMMIT")

        assert one(reader, "SELECT value FROM item WHERE id=1")[0] == "old"
        held = one(writer, "PRAGMA wal_checkpoint(PASSIVE)")
        assert held[1] > held[2], held

        reader.execute("COMMIT")
        assert one(reader, "SELECT value FROM item WHERE id=1")[0] == "new"

        released = one(writer, "PRAGMA wal_checkpoint(PASSIVE)")
        assert released[1] == released[2], released
        print({"held": held, "released": released, "result": "passed"})
    finally:
        if reader.in_transaction:
            reader.rollback()
        if writer.in_transaction:
            writer.rollback()
        reader.close()
        writer.close()

The code explicitly disables automatic checkpointing only to make the experiment observable. This is not a production configuration recommendation. The truncation after initialization is likewise intended only for this isolated fixture; do not copy it into a database that is actively handling work.

Distinguish BEGIN from the First Read

A plain BEGIN starts a deferred transaction. The moment that statement executes is not necessarily the snapshot time. In the example above, the first query establishes the read view.

Run a separate variation: move the write and commit before the reader's first query. Both the first query and subsequent queries in that transaction should then return new. Update the assertions accordingly.

This exposes a common diagnostic mistake: a log says “transaction started,” so an engineer assumes that all subsequent reads reflect that instant. At a minimum, log the connection ID, transaction ID, first query, and transaction end—not just the method entry.

Do Multiple Queries Need a Consistent View?

For example, if you query the order count and then the order details, and both queries belong to the same business snapshot, put them in a short read transaction. If every scroll-triggered load must fetch the latest data, do not wrap the entire page lifecycle in one transaction. Acceptance criteria should follow the boundaries of the business operation, not whichever approach appears to refresh faster.

Track Checkpoint Progress, Not Just File Size

wal_checkpoint(PASSIVE) returns three integers: the busy status, the number of log frames, and the number of checkpointed frames, in that order. The existing reader in this fixture still needs its earlier view, so some new frames cannot be checkpointed. Once the read transaction is released, running the checkpoint again allows it to advance.

Do not assert that a particular operation produces an exact number of frames. Page size, database layout, and runtime library version all affect the count. Test the relative relationships instead.

Likewise, the checkpointed frame count catching up with the log frame count does not mean the WAL file immediately shrinks. The file may retain space for reuse. In production, record read transaction duration, checkpoint results, and write activity trends together. A large file in a directory is not, by itself, a reason to delete the log.

If the second assertion fails, first look for read transactions, cursors, or other connections that are still open, then investigate the checkpoint strategy. Do not start by adding a loop that forces truncation and hides the actual long-running reader.

Move the Fixture into iOS Data-Layer Tests

The host-side experiment validates the SQLite library linked to the current Python installation. After moving the fixture to iOS, rerun it through the actual data access layer on the target simulator or device, and record that environment's sqlite3_libversion(). A host-side result is not proof of behavior across all runtime environments.

Keep two genuinely independent underlying connections. Use explicit test barriers to control the three stages: “first read completed, write committed, read again.” Do not rely on sleep delays. If the connection pool schedules both operations on the same connection, the experiment no longer tests the intended interaction.

Finish by checking four things: the old transaction reads the old value, a new transaction reads the new value, transactions end on error paths too, and checkpointing can advance after the long-running reader is released. For asynchronous queries, also verify that cancellation closes out both the cursor and the transaction. Keep these assertions in your regression tests. The next time a list shows stale data, you can first determine whether it is a valid snapshot or a transaction whose lifetime has exceeded its intended scope.

Frequently asked questions