Re: [Cppcms-users] Threaded access to SQLite

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

Josh Hunsaker wrote:
> 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.

I have created a stand-alone test that shows the difference between
sharing a single SQLite database connection between threads, and
creating a new connection for each thread.  It can be viewed here:

https://gist.github.com/nispio/fe2955c9a0978532cf7b864a8b1b54db