[odb-users] SQLITE unable to open db multiple threads

[Boris Kolpackov]

Hi Eric,

Sum, Eric B <eric.b.sum at lmco.com> writes:

> I am persisting objects into a sqlite database from multiple threads. 
> However, I am getting odd results caught with odb exceptions. I get
> sqlite error 14: unable to open database multiple times throughout
> the execution of my program, and only a fraction of the objects
> persisted are present in the database.

Can you take a look at the common/threads test in the odb-tests
package and see if there is anything different between that code
and your application. You can either try to model your code after
the test, or you can try to change the test to model your
application and thus reproduce the problem. If you can reproduce
it with the test, then I would be happy to take a look.


> I am aware that you are unable to do concurrent writes to the sqlite
> database due to its exclusive locking mechanism, but I thought that
> there would be some scheduling/blocking scheme used inherently by odb
> to schedule the order of the persists from the different threads.

That's correct. And the common/threads test is the proof that it
works without doing anything special/extra. Besides other things,
this test persists multiple objects from multiple threads into the
same database, simultaneously.


> The odb manual states that by default, a sqlite connection_pool_factory
> is used, which behind the scenes uses the shared_cache/unlock_notify
> mechanism.  From reading about the shared_cache/unlock_notify mechanism
> it seems that "The sqlite3_unlock_notify() interface is designed for
> use in systems that have a separate thread assigned to each database
> connection"  and the shared_cache mechanism allows the same cache to
> be shared across the entire process that contains all of its threads.
> How do I create a database connection for each individual separate
> thread as it seems the intended use is?

This is all done automatically by ODB/connection_pool_factory.


> 1. I tried explicitly calling connection_ptr c(db.connection()) in each
>    thread on the existing db object created in the main function that was
>    passed to each thread.

No, you don't need to do that.


> 2. I tried passing the existing db object to each thread, which I suppose
>    does not create a new connection and uses the existing one across
>    multiple threads.

That's exactly how it should be done: you pass a single sqlite::database
instance to all your threads and this instance will "issue" an individual
connection (via the pool_factory) to each thread. Again, see the 'threads'
test for an example.


> 3. I tried called the sqlite db constructor in each thread to open the
>    existing database created in the main function in READWRITE mode,
>    which I assume creates a connection in each thread to use.

Nop, you shouldn't do that. Just create a single sqlite::database in
main() and pass it to all your threads.

Boris