> Le 18 avr. 2016 ? 15:10, Detlef Golze <Detlef.Golze at point.de> a ?crit : > > thank you for the hints. I think I do it correctly, I compiled with > SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option > cache=shared.
Do you really *need* to use shared cache in favor of private cache? There are more contentions (on the cache) implied by locks on table b-trees, when you use shared-cache. We quickly had to rule it out, for better concurrency (of readers). Also when using shared-cache, taking into account the large number of SQLITE_LOCKED_SHAREDCACHE extended error code you will get, it is probably better to use sqlite3_unlock_notify() properly in those cases, so that each thread is awaken properly as soon as it can retry and proceed. But that implies some more programming and is probably more fitted for an intermediate layer of code between you application code and sqlite library code (that's how we tested it). Anyway, unless you have severe memory constraints, I would insist you try the private cache for each of your connections, you should see direct benefits. > Also, each connection is used by one thread at a time. But another thread may > reuse a connection - I hope that is allowed. I think so, because sqlite does not keep per-thread data but per connection data. So if you're absolutely sure this can't lead to situations of two threads sharing the connection, it should be okay, though I'd refrain to do that, it probably is useless. Here our experience has been that "re-using" connections is useless, at least for us. Opening a connection when needed is very cheap with sqlite. So each worker thread in our app server, opens a new connection when the thread is scheduled fo working for a client and closes it when work done (client disconnect). > Are you also opening/closing database connections and prepare statements > while another SELECT is running? We can have any number of connections opened (generally less than 30), and any of them can run simultaneous select statements without issue, as far as our short experience shows. You can even have one writer do a short-lived transaction while you have a number of readers right in the middle of select(s), that won't be a problem, wal is your friend for this. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
