[database-devel] Multi-threaded access to an SQLite database

Moving the discussion over to database-devel - others may find this interesting / may have run into this. FWIW - I also tried setting up a test case which would have multiple concurrent writers, but couldn't get this to fail. I suspect though that my test case wasn't good enough to hit this problem. Background: This originated from [1], also collected some notes on [2]. [1]: https://github.com/nurpax/snaplet-sqlite-simple/issues/5 [2]: here: http://nurpax.github.com/posts/2013-01-04-note-to-self.html Janne On Sat, Jan 5, 2013 at 12:38 AM, Joey Adams wrote:

On Fri, Jan 4, 2013 at 4:43 PM, Janne Hellsten wrote:

...

I was just wondering if you have any past experience on multi-threaded use of SQLite? I hit some issues last week, started seeing SQLITE_BUSY on inserts and wasn't quite sure what to think about it. I wrote some notes about it here http://nurpax.github.com/posts/2013-01-04-note-to-self.html. Have you by any chance had to deal with such problems before?

According to the SQLite FAQ [1], only one writer can access the database at a time. I haven't run into this myself, though.

Be careful with transactions. If you allow multiple threads to access a database handle, and to issue BEGIN/COMMIT on their own terms, then one thread might issue BEGIN and interfere with other transactions. Thus, make sure the MVar lock is held for the duration of the transaction.

PS: Mind CCing this to database-devel@haskell.org ?

[1]: http://www.sqlite.org/faq.html#q5