>"...and then we discovered an unexpected
clue.
We wanted a way to restore service that didn’t involve rolling back to the last known-good backup (which would lose a lot of data) or repairing the known-corrupted database (which was potentially risky). To do this, we built a
transaction logging pipeline.
We streamed every SQL statement that modified the database to a separate log file. Because SQLite is a single-writer database with serializable transactions, our transaction history was completely linear and
deterministic.
(This wouldn’t be true in a multi-writer database like Postgres or MySQL.) Replaying those transactions against the latest known-good backup should restore the database to its most recent state, safely bypassing the corruption.
[...] This pipeline worked, but then it did something even better: it gave us a clue.
[...] To understand what was happening during these faulty checkpoints, the SQLite developers created a new debugging tool for the virtual filesystem layer.
[...] To help diagnose our problem, the SQLite developers created a wrapper around the virtual filesystem that
writes additional tracing information and logs
about changes to the database.
[...] After our next corruption incident, the additional logs from the new tmstmpvfs shim allowed the SQLite developers to find and fix the bug:
a rare data race
in the SQLite source code between a checkpoint and a write transaction."
Great article!
Software Engineering lessons (that repeat in this article!): So called "Heisenbugs" (bugs that make it past developer test harnesses and a company's Quality Assurance (QA) team) that show up post-deployment intermittently and can't be reproduced locally, occur because one or more of the following factors:
1) The lack of Determinism in a software process or processes.
2) The lack of appropriate logging.
3) The lack of the ability to replay a software process, step by exact step, state by exact state, as it has occurred in the field (occurs as an effect of #1 and/or #2).
4) Multi-threaded code; i.e., multiple threads giving rise to race conditions or other very specific intermittent combinatorial/permutational conditions caused by multiple threads and specific sections of code, which due to very large numbers of permutational timing possibilities, were not or could not be exactly tested for in development...
Anyway, great article! A must-read for any Sr. Software Engineer, or any developer that wrestles with hard-to-find-and-fix bugs in the field...