4 ms·

How are people using sqlite within a multi-threaded, asynchronous runtime: are you using a synchronization lock? SQLite seems to "kinda, sorta" support multi-t

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?

wanttocomment 3y ago
Each thread opens its own connection. Set a non-zero timeout. I think that's all you need.
masklinn 3y ago
Create a connection per task. WAL is probably a good idea. 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-592624109 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.
matharmin 3y ago
1. Use WAL mode - this allows reading while practically never being blocked by writes. 2. Use application-level locks (in addition to the SQLite locking system). The queuing behavior from application-level locks can work better than the retry mechanism provided by SQLite. 3. Use connection pooling with a separate connection per thread - one write connection and multiple read connections. 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-high-performance https://www.powersync.co/blog/sqlite-optimizations-for-ultra...
- chasil 3y ago
  Be careful of WAL mode. There are specific limitations. 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.” https://www.sqlite.org/lang_attach.html 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.”
  - notTooFarGone 3y ago
    Yeah, wal can also grow very large, making read operations increasingly slow on wrong checkpoint configuration, which also makes checkpointing slower and it grows out of proportion. Inefficient queries, no reader gaps and a bad manual checkpointing system can bring the system down.
    - tored 3y ago
      Does WAL2 mitigate the performance reduction? https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md
      - notTooFarGone 3y ago
        That's neat, I haven't worked with SQLLite for a few years but this changes it. Of course you can still fall into the trap of reaching your checkpoints faster than the first wal file needs to write back to the database, but I think that's a less critical problem in pratice.
emadda 3y ago
Two methods: 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
- MuffinFlavored 3y ago
  https://stackoverflow.com/questions/25319031/what-is-the-default-value-of-sqlite3-busy-timeout https://stackoverflow.com/questions/25319031/what-is-the-def... Just to clarify, busy_timeout = 0 by default

wanttocomment 3y ago

Each thread opens its own connection. Set a non-zero timeout. I think that's all you need.

masklinn 3y ago

Create a connection per task. WAL is probably a good idea. 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-592624109 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.

matharmin 3y ago

1. Use WAL mode - this allows reading while practically never being blocked by writes. 2. Use application-level locks (in addition to the SQLite locking system). The queuing behavior from application-level locks can work better than the retry mechanism provided by SQLite. 3. Use connection pooling with a separate connection per thread - one write connection and multiple read connections. 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-high-performance https://www.powersync.co/blog/sqlite-optimizations-for-ultra...

chasil 3y ago
Be careful of WAL mode. There are specific limitations. 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.” https://www.sqlite.org/lang_attach.html 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.”
- notTooFarGone 3y ago
  Yeah, wal can also grow very large, making read operations increasingly slow on wrong checkpoint configuration, which also makes checkpointing slower and it grows out of proportion. Inefficient queries, no reader gaps and a bad manual checkpointing system can bring the system down.
  - tored 3y ago
    Does WAL2 mitigate the performance reduction? https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md https://sqlite.org/cgi/src/doc/wal2/doc/wal2.md
    - notTooFarGone 3y ago
      That's neat, I haven't worked with SQLLite for a few years but this changes it. Of course you can still fall into the trap of reaching your checkpoints faster than the first wal file needs to write back to the database, but I think that's a less critical problem in pratice.

emadda 3y ago

Two methods: 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

MuffinFlavored 3y ago
https://stackoverflow.com/questions/25319031/what-is-the-default-value-of-sqlite3-busy-timeout https://stackoverflow.com/questions/25319031/what-is-the-def... Just to clarify, busy_timeout = 0 by default