Re:How SQLite scales read concurrency
by chasil on Wednesday August 24, 2022 @04:37PM
(#32585414)
There is a significant warning about the use of WAL mode, very plain in the documentation:
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."
https://sqlite.org/lang_attach.html
Systems designed for DML against multiple database files should eschew WAL mode.