Threadsafety?
singpolyma opened this issue · 4 comments
Are the sqlite3 connections created by sqlite-simple threadsafe? That is, can I share a connection across threads safely?
It should be as thread-safe as the underlying sqlite library is. My experience has been that it's pretty hard to use an sqlite database from multiple threads (regardless of single shared connection or multiple threads with their own connections). I did a bunch of experiments in the context of the Snap web framework and at least for my uses, I ended up going for a single connection with accesses to it being serialized with an MVar. Please see details from nurpax/snaplet-sqlite-simple#5.
Sqlite has three different thread-safety settings: http://www.sqlite.org/threadsafe.html
So, based on what you're saying, I would guess the connections are opened in single thread mode?
It's using the default threading options (ie. we don't specify any special options) - this should be serialized.
The code that implements the low-level sqlite3 bindings is in direct-sqlite, you can see the source on https://github.com/IreneKnapp/direct-sqlite/tree/master/Database/SQLite3.
I'd say this library is thread-safe in the sense that access to sqlite won't cause corruption or segfaults, or anything like that in the lower levels. However, at least I couldn't quite figure out how to do concurrent sqlite accesses without getting spurious SQLITE_BUSY errors. This probably works for concurrent cases where all the threads are read-only. The sqlite docs will tell you that in the case of SQLITE_BUSY, the user of the sqlite library "should just re-try his query" which for an API like sqlite-simple or snaplet-sqlite-simple is pretty much a non-starter.
AFAICT, the SQLITE_BUSY semantics have little to do with sqlite-simple or direct-sqlite, it's just how sqlite3 works.
For future reference: Excellent overview of attempting to use SQLite with multiple threads by @nurpax: nurpax/snaplet-sqlite-simple#5