Prompt and context
A desktop sync service opens one SQLite file from multiple processes in WAL mode. The team learns that historical versions may contain the WAL-reset bug and worries about misreading safety, concurrent writes during an upgrade, or syncing a damaged copy downstream. Design version inventory, risk assessment, upgrade, integrity verification, backup recovery, and rollback, including multi-process access controls.
What the interviewer tests
- Separating an affected version range from the conditions that can trigger the bug.
- Building an upgrade matrix from official fixed and backported versions.
- Ordering consistent backup, verification, isolation, and recovery steps.
- Explaining the limits of
PRAGMA integrity_checkinstead of treating it as business correctness. - Handling multi-process writes, checkpoints, file copies, and sync side effects.
Questions to clarify
- What exact SQLite versions, build options, operating systems, and journal modes are deployed?
- Are two or more processes or threads writing to or checkpointing the same file?
- What verified backups, replicas, and last-successful-check timestamps exist?
- Can the upgrade freeze writes briefly and force old processes to exit?
- Is the recovery target recent-transaction loss, server reconstruction, or exact restoration?
30-second answer
I would inventory versions and runtime mode first; an affected version range does not prove corruption. SQLite documents the WAL-reset bug in some WAL scenarios from 3.7.0 through 3.51.2, fixed in 3.51.3 and later, with a few backported releases. Before upgrading, freeze writes, copy the database and WAL/SHM evidence, and record a verification baseline. Upgrade an isolated copy, then run structural integrity checks, business-level sampling, and sync consistency checks. Switch the production file only after the gate passes, keeping a rollback copy. Long term, limit multi-process writes, monitor checkpoints, lock conflicts, and verification failures, and make recovery drills part of release gates.
Step-by-step deep dive
1. Build an impact matrix
SQLite’s official material places the possible bug range at 3.7.0 through 3.51.2, with the fix in 3.51.3 and later and backports in 3.44.6 and 3.50.7. The risk also requires WAL mode, multiple connections to one file, and writes or checkpoints interleaving in a tight window. Record each client’s SQLite version, WAL state, connection count, process model, and file location, then classify “affected version plus trigger conditions present.”
version/mode -> WAL enabled -> multi-connection/process -> concurrent write/checkpoint
| | | |
+-- upgrade gate ------------------+----------> isolate, verify, rehearse recovery2. Freeze and preserve evidence
Stop new writes and let the application close connections gracefully. If old processes may still be alive, do not replace or copy the database. Preserve the original database, same-directory WAL/SHM files, version data, latest backups, and verification logs. Copy only from a stable state; a changing WAL is not a static snapshot.
3. Upgrade and verify in isolation
Open a copy with a fixed version. Run SQLite-level integrity checks first, then application checks for row counts, key indexes, foreign keys, sync cursors, and recent transactions. integrity_check can find structural problems but cannot prove domain semantics, remote replica equality, or completeness of uncommitted work. Bind results to the copy hash and tool version.
4. Switch, roll back, and recover
After the gate passes, atomically switch a file path or versioned directory and keep the old copy read-only. If startup reveals a verification failure, sync conflict, or business gap, stop new writes, switch back or rebuild from a trusted backup, and then resynchronize incrementally. Do not run VACUUM, bulk repair, or overwrite copies on a suspected damaged file before preserving evidence.
5. Govern multi-process writes and checkpoints
Prefer one writer process for a file and serialize write transactions through a queue; other processes use controlled read connections. Define who triggers checkpoints, their timeout, and failure alerts so multiple components do not checkpoint concurrently. If multi-process access is unavoidable, record connection lifecycles, lock waits, checkpoint outcomes, and process versions, and stress-test interleaved writes and checkpoints.
6. Monitor and rehearse recovery
After release, monitor SQLite version coverage, WAL growth, checkpoint latency, lock conflicts, integrity failures, sync retries, and recovery time. Rehearse “freeze—backup—verify—switch—rollback” on sanitized copies and verify that old clients cannot reopen the old-version database. Express recovery objectives as business RPO and RTO rather than merely saying “backups exist.”
Model answer
I would start with an impact matrix: whether SQLite is in 3.7.0–3.51.2, whether WAL is enabled, whether multiple connections share the file, and whether writes and checkpoints can overlap. Being in the range means upgrade and verify; it does not prove corruption. Freeze writes, confirm old processes have exited, preserve the database, WAL/SHM, backups, version, and hashes, and upgrade an isolated copy to 3.51.3 or an acceptable backport.
Verification has three layers: PRAGMA integrity_check for structure, application sampling for key data and indexes, and sync checks for local versus remote cursors. Switch atomically after the gate and keep the old copy read-only. If checks or business invariants fail, stop writes, restore a trusted copy, and rebuild incremental sync. Long term, centralize writes in one process, constrain active checkpoints, monitor lock conflicts and verification failures, and rehearse recovery.
Common mistakes
- Declaring the database damaged solely because an old version is deployed.
- Upgrading binaries without freezing writes or preserving WAL/SHM and rollback copies.
- Treating one passing
integrity_checkas proof of complete business correctness. - Running VACUUM or overwriting a suspected damaged file before preserving evidence.
- Letting many processes checkpoint independently without conflict and timeout metrics.
- Saying “we back up regularly” without backup consistency, RPO, RTO, or recovery drills.
Follow-up questions and responses
If the version is affected but no anomaly is visible, can we skip the upgrade?
No. Use trigger conditions to assess short-term exposure and schedule the fixed version; absence of an observed failure does not disprove a narrow race.
Why perform business checks after PRAGMA integrity_check passes?
It mainly checks database structure and selected constraints. It does not validate sync cursors, domain invariants, remote equality, or recent transaction completeness, so application sampling and replica comparison remain necessary.
What is a temporary measure if multi-process access cannot be redesigned immediately?
Unify SQLite versions and startup settings, prohibit uncontrolled active checkpoints, serialize writes, and improve lock-conflict telemetry. Shorten canary batches and keep a read-only copy that can be switched back quickly until a single-writer design is available.