How are people using sqlite within a multi-threaded, asynchronous runtime: are you using a synchronization lock? SQLite seems to "kinda, sorta" support multi-threading, requiring special configuration and caveats. Is there a good reference?
Even using SERIALIZED mode, sqlite has multiple APIs which are completely broken if two clients touch the same connection (https://github.com/rusqlite/rusqlite/issues/342#issuecomment...).
Don't bother, just don't share connections between threads and use the regular multi-thread mode (do use that though).
You can use a connection pool and move connections from task to task (and thread to thread), just not use connections concurrently.
Ideally, all of the above would be covered by a library, and not up to the app developer.
I wrote a blog post covering some of this recently: https://www.powersync.co/blog/sqlite-optimizations-for-ultra...
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.”
https://www.sqlite.org/lang_attach.html
SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode.
“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.”
Inefficient queries, no reader gaps and a bad manual checkpointing system can bring the system down.
1. Queue up writes to a single connection in a given thread.
2. Retry the write after a short sleep when you get a SQLITE_BUSY error. SQLite will do this for you, see busy_timeout docs.
WAL is single write, multiple reader, so if you are doing SELECT queries they should not return SQLITE_BUSY
Just to clarify, busy_timeout = 0 by default