3 ms·

Note that WAL mode brings a LOT of limitations. It is not safe to use network file systems (NFS, SMB, or otherwise) in WAL mode, since they cannot access the s

by chasil 2y ago

Note that WAL mode brings a LOT of limitations. It is not safe to use network file systems (NFS, SMB, or otherwise) in WAL mode, since they cannot access the shared memory. There are a few more limitations that I can dig up. https://sqlite.org/lang_attach.html https://sqlite.org/lang_attach.html 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.

chasil 2y ago
Dr. Richard Hipp himself outlined a few more limitations of WAL mode in a recent paper. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”