[odb-users] SQLite Concurrency

[Boris Kolpackov]

Hi Mike,

Mike Ruffing <mike.ruffing at ipconfigure.com> writes:

> In a separate test I turned off shared cache via SQLITE_OPEN_PRIVATECACHE.
> With caching turned off, all the short running queries executed and
> finished in parallel to the long running query.
> 
> For our use case (favor concurrency over memory footprint), I think
> disabling shared cache is the right approach.  Are there any known
> problems/gotchas with disabling shared cache?

Yes, this will only work for read-only connections. As soon as you
have a read and write connections trying to do work at the same time,
SQLite will return an error (which ODB will throw; though there is a
way to specify a timeout at the SQLite level[1] during which it will
retry the operation before giving up).

One can probably come up with a custom ODB connection pool that
implements a single writer connection/multiple reader connections
using an RW mutex, though this will be a pretty narrowly applicable
solution.

[1] https://www.sqlite.org/c3ref/busy_timeout.html

Boris