Re: [Cppcms-users] Threaded access to SQLite

[Josh H. <jos...@ni...>]

Artyom Beilis wrote:
>> Currently the cppdb_sqlite3 driver attempts to open a new
>> connection to the database via sqlite3_open_v2() for each thread.
>> From the SQLite documentation, it would seem that a given process
>> should only have one shared connection to the database.  If that
>> is the case, then the cppdb_sqlite3 driver is written contrary to
>> the intended usage of sqlite3.  It seems that the right thing to
>> do may be to open a single connection and share it between the
>> threads.
>
> You are wrong, sqlite3 fully supports multi-threading but you need to
> be sure that
> you are using the sqlite3 library that was compiled properly.

I'm sorry, let me attempt to express myself more clearly:  A
given process may share a *single* connection to the database
between multiple threads.  In other words, the call to
sqlite3_open_v2() should only happen once within a given process,
and all threads within that process should then utilize that
single connection, so long as it is open.

The way in which SQLite supports multi-threading is via its own
internal mutexes.  However, creating multiple database
connections to the database (via multiple calls to
sqlite3_open_v2) will cause multiple mutexes to be created,
therefore nullifying their usefulness. The SQLite FAQ says the
following:

>> SQLite is threadsafe because it uses mutexes to serialize
>> access to common data structures.

I am not suggesting that multi-threaded access cannot be achieved
with SQLite.  I am suggesting that the correct way to do
multi-threading with SQLite is to create a single database
connection within a given process, and share that connection
between the threads.  This allows the internal mutexes in SQLite
to work as intended.  I have tested this, and can provide test
code to demonstrate SQLite behavior, if desired.