SQLite Threading Mode about direct-sqlite HOT 5 CLOSED

When I checked sqlite3 on Mac OS X (10.11.3), I was surprised it was compiled without multi-thread support, but I assume this is independent of the binding used in direct-sqlite, correct?

> sqlite3 --version
3.12.0 2016-03-29 10:14:15 e9bb4cf40f4971974a74468ef922bdee481c988b

> echo 'PRAGMA compile_options;' | sqlite3 
compile_option
--------------
ENABLE_FTS3   
ENABLE_FTS5   
ENABLE_JSON1  
ENABLE_RTREE  
SYSTEM_MALLOC 
THREADSAFE=0  

from direct-sqlite.

I'm no longer a maintainer of direct-sqlite; this bug just happened to catch my eye. I'll wait for the people who are doing the work to weigh in about what changes might be appropriate, but some advice off the top of my head -

Indeed, unless you built with the systemlib flag, direct-sqlite uses its own copy of sqlite, not the OS X one.

You can see how sqlite is built in the Cabal file; it doesn't specify threading-related options, so, per your link, it defaults to serialized mode. There's not yet any sqlite3_open_v2 support, so it stays at the default. You can also check that by evaluating "PRAGMA compile_options;" as SQL from within Haskell.

From the information you've provided, it is not obvious why you're getting timeouts. As with any resource contention issue, you should be sure you know where the concurrent access is coming from in the first place. Is it from multiple threads in the same process, or from multiple processes? The latter case has to rely on filesystem locking primitives, which is one reason that things can be slow. You should also familiarize yourself with sqlite's concurrency mechanisms in general and with how they relate to what you're doing.

from direct-sqlite.

@IreneKnapp Thank you for your thoughtful answer. Ha, on my way to work I was also thinking of just running PRAGMA compile_options; using sqlite-simple itself — brilliant!

I got the following (slightly formatted for readability):

λ> :set -XOverloadedStrings
λ> :m +Database.SQLite.Simple
λ> conn <- open "./test.sqlite3" 
λ> query_ conn "PRAGMA compile_options;" :: IO [(Only String)]
[ Only {fromOnly = "ENABLE_FTS3"}
, Only {fromOnly = "ENABLE_FTS3_PARENTHESIS"}
, Only {fromOnly = "ENABLE_FTS4"}
, Only {fromOnly = "SYSTEM_MALLOC"}
, Only {fromOnly = "THREADSAFE=1"}]

…so indeed, it’s using the serialized mode (THREADSAFE=1) which means it should work fine in a multi-threaded environment according to the documentation.

Disclaimer: I am new to multi-threaded programming, coming mostly from a Node.js background.

Once I looked a bit more closely at my errors, I noticed that they were ErrorBusy (SQLite3 returned ErrorBusy while attempting to perform step: database is locked) which, based on my understanding, is an expected resource contention error that is safely retriable, which is what I have done for now: https://github.com/gasi/zoomhub/blob/ad17a1118d82e1cd07eef31318aa9e880ebbe52e/src/ZoomHub/Storage/SQLite.hs#L455-L488

As with any resource contention issue, you should be sure you know where the concurrent access is coming from in the first place. Is it from multiple threads in the same process, or from multiple processes? The latter case has to rely on filesystem locking primitives, which is one reason that things can be slow.

In my case, I am running a single process with multiple threads stack build --ghc-options='-threaded' and stack exec … -- +RTS -N -RTS, based on my understanding. Do you have any recommendations for good introductory literature to multi-threaded programming, maybe even using Haskell? I just started taking a course that’s using Parallel and Concurrent Programming in Haskell but my pet project got me a bit ahead of myself 😉

You should also familiarize yourself with sqlite's concurrency mechanisms in general and with how they relate to what you're doing.

I will do that. Do you have any good suggestions besides the official docs?

Thanks again and have a nice day 😄

from direct-sqlite.

Hi @gasi, I have also come across this problem and found it to be extraordinarily frustrating to deal with.

The internet knows that you can just "re-try" your queries until they succeed. But who programs like that in the year 2016?!

When I implemented the Snap sqlite bindings snaplet-sqlite-simple, I tried hard to fix this but eventually just added an MVar lock around db access and serialized database queries by only letting one thread inside sqlite at any time. This worked great and I got much better performance and latency out of sqlite this way.

You can read my notes here: nurpax/snaplet-sqlite-simple#5

Nevertheless, I think this is not really a direct-sqlite bug. I think we can close this. Let me know if you think otherwise.

from direct-sqlite.

@nurpax Thanks for sharing your own experiences. Especially reading through your nurpax/snaplet-sqlite-simple#5 issue and code examples was very enlightening.

Of course, as it stands, this is not a direct-sqlite bug so I am in favor of closing. I thought I searched existing issues but apologies for not finding the previous discussion in nurpax/sqlite-simple#28.

I’ll keep on playing around with SQLite and direct-sqlite but I’ll have to see if I can put together a lock based system.

from direct-sqlite.