INSERT OR IGNORE idempotency shift with WAL mode on Raspberry Pi SQLite

With a UNIQUE constraint, both INSERT OR IGNORE and ON CONFLICT DO UPDATE prevent duplicate rows; the observable duplicate frequency is zero for either statement, and it does not change when you adjust the WAL checkpoint interval. Without a UNIQUE constraint, INSERT OR IGNORE degrades to a plain INSERT and will create duplicates on retry, while ON CONFLICT DO UPDATE is not available because there is no conflict target. A schema without a UNIQUE column cannot be made retry-safe with INSERT OR IGNORE alone.

Likely explanation versus confirmed behavior

The likely source of perceived visibility gaps is not checkpoint timing but snapshot isolation. In WAL mode, committed transactions are visible to new read transactions immediately, whether or not a checkpoint has run. A reader that started its transaction before the writer committed will not see the new row until it restarts its read transaction. Checkpointing moves frames from the WAL back into the main database file; it controls disk footprint and recovery time, not logical visibility of committed data.

Confirmed behavior: conflict resolution for INSERT OR IGNORE and ON CONFLICT DO UPDATE happens at statement-execution time inside the writing transaction, before commit. Journal mode does not alter that decision. Therefore, toggling wal_autocheckpoint between 0 and a small page count leaves duplicate counts unchanged.

Scoped verification on ARM

Run these checks against a scratch database, not production data. SQLite 3.35+ is assumed for UPSERT syntax; older ARM builds should be tested explicitly.

PRAGMA journal_mode=WAL;
CREATE TABLE t(id INTEGER PRIMARY KEY, k TEXT UNIQUE, v TEXT);

-- Simulate 100 retries
-- INSERT OR IGNORE INTO t(k,v) VALUES('a','1');
SELECT count(*) FROM t;  -- expect 1

-- INSERT INTO t(k,v) VALUES('a','2')
--   ON CONFLICT(k) DO UPDATE SET v=excluded.v;
SELECT count(*) FROM t;  -- expect 1

CREATE TABLE t2(id INTEGER PRIMARY KEY, k TEXT, v TEXT);
-- INSERT INTO t2(k,v) VALUES('a','1');  -- repeat 100 times
SELECT count(*) FROM t2; -- expect 100

PRAGMA wal_autocheckpoint=0;
-- rerun the UNIQUE loop; count remains 1
PRAGMA wal_autocheckpoint=100;
-- rerun again; count remains 1

When checkpoint timing actually matters

Checkpoint frequency affects WAL file size, read amplification when the WAL grows large, and crash-recovery duration. It does not create partially committed rows, because SQLite transactions are atomic. If your application observes missing rows after a retry, verify whether the reader is using a stale snapshot rather than assuming a checkpoint delay.

One missing diagnostic

Do your retry attempts read back on the same connection, or do separate reader connections hold long-running transactions? If reads occur on the same connection, prior writes are visible immediately. If separate connections are involved, they must begin a fresh read transaction after the writer commits; otherwise snapshot isolation will hide the row regardless of checkpoint state.