cppcms-users

[Cppcms-users] Threaded access to SQLite

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

Is connection pooling expected to work for the SQLite backend?  When I
spawn three threads and try to connect to the database from each, one
of the connections will succeed and the other two will fail.  On the
two that fail, I get an error saying "database is locked."  This
happens whether I am using the connection pool directly (via
cppdb::pool) or indirectly (via @pool_size).

Re: [Cppcms-users] Threaded access to SQLite

[Artyom B. <art...@gm...>]

In general sqlite3 behaves poorly with multiple threads and you should
make yourself ready to deal with the issue - simultanious updates and
even query + update may fail.

Artyom

On Fri, Jan 6, 2017 at 8:54 PM, Josh Hunsaker <jos...@ni...> wrote:
> Is connection pooling expected to work for the SQLite backend?  When I
> spawn three threads and try to connect to the database from each, one
> of the connections will succeed and the other two will fail.  On the
> two that fail, I get an error saying "database is locked."  This
> happens whether I am using the connection pool directly (via
> cppdb::pool) or indirectly (via @pool_size).
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, SlashDot.org! http://sdm.link/slashdot
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users

Re: [Cppcms-users] Threaded access to SQLite

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

On Sun, Jan 8, 2017 at 12:17 AM, Artyom Beilis wrote:
> In general sqlite3 behaves poorly with multiple threads and you should
> make yourself ready to deal with the issue - simultanious updates and
> even query + update may fail.
>
> Artyom

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.

Re: [Cppcms-users] Threaded access to SQLite

[Artyom B. <art...@gm...>]

> On Sun, Jan 8, 2017 at 12:17 AM, Artyom Beilis wrote:
>> In general sqlite3 behaves poorly with multiple threads and you should
>> make yourself ready to deal with the issue - simultanious updates and
>> even query + update may fail.
>>
>> Artyom
>
> 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.

https://www.sqlite.org/threadsafe.html

Artyom

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.

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

Re: [Cppcms-users] Threaded access to SQLite

[Artyom B. <art...@gm...>]

>
> 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
>


Actually it shows exact behaviour I expected

This is my run (note on PC with 4 cores):


SAME CONNECTION
Thread 1: library routine called out of sequence
Thread 2: library routine called out of sequence
Thread 3: library routine called out of sequence
FAILED run(insert_random, "/var/tmp/testdb1.db")
NEW CONNECTION
Thread 2: database is locked
Thread 3: database is locked
FAILED run(new_connection_insert_random, "/var/tmp/testdb2.db")


Now I explain:

In fact new connection worked as expected to work, some threads
succeed and some failed due to locked database.
Using single connection created out of sequence error!

IN fact the use of new connection works properly as sqlite3 does not
support writing the same database from different threads
simultaneously and would return the error the locked database and user
need to handle such a error - i.e. try to run the statement
same time - similarly that in "bigger" DB you receive deadlock errors.

In fact you try the test with sqlite3 in exact situation where the
sqlite3 is suboptimal - high concurrency updates.

https://sqlite.org/whentouse.html

If you for example run select queries in all threads it would succeed
and if you turn on WAL you would likely succeed doing simultaneous
single write multiple reads.


Bottom line - sqlite3 behaves exactly as expected. You probably
misunderstand the use case. If you want to run high amount of inserts
to
DB the optimal way is to do it from a single thread in a single
transaction (on Rollback journal mode) or from single thread in big
chunks
each in a single transaction of WAL mode.

In any case you have to be ready to handle errors - similar to the way
you need to handle deadlocks in "big" DBs.

Best Regards,
    Artyom



> ------------------------------------------------------------------------------
> Developer Access Program for Intel Xeon Phi Processors
> Access to Intel Xeon Phi processor-based developer platforms.
> With one year of Intel Parallel Studio XE.
> Training and support from Colfax.
> Order your platform today. http://sdm.link/xeonphi
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users

Re: [Cppcms-users] Threaded access to SQLite

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

Artyom Beilis wrote:
>> 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
>>
>
>
> Actually it shows exact behaviour I expected
>
> This is my run (note on PC with 4 cores):
>
>
> SAME CONNECTION
> Thread 1: library routine called out of sequence
> Thread 2: library routine called out of sequence
> Thread 3: library routine called out of sequence
> FAILED run(insert_random, "/var/tmp/testdb1.db")
> NEW CONNECTION
> Thread 2: database is locked
> Thread 3: database is locked
> FAILED run(new_connection_insert_random, "/var/tmp/testdb2.db")
>

I'm sorry.  I made a mistake when posting to Gist.  I made a slight tweak
to the code when posting, and forgot to check if it worked.  (Rookie
mistake, I know).  The error that you are seeing in the first case is
not related to threading at all; it is just a bad pointer.  Will you please
humor me and try one more time with the updated code?

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

Re: [Cppcms-users] Threaded access to SQLite

[Artyom B. <art...@gm...>]

On Thu, Jan 12, 2017 at 6:41 PM, Josh Hunsaker <jo...@ni...> wrote:
> Artyom Beilis wrote:
>>> 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
>>>
>>
>>
>> Actually it shows exact behaviour I expected
>>
>> This is my run (note on PC with 4 cores):
>>
>>
>> SAME CONNECTION
>> Thread 1: library routine called out of sequence
>> Thread 2: library routine called out of sequence
>> Thread 3: library routine called out of sequence
>> FAILED run(insert_random, "/var/tmp/testdb1.db")
>> NEW CONNECTION
>> Thread 2: database is locked
>> Thread 3: database is locked
>> FAILED run(new_connection_insert_random, "/var/tmp/testdb2.db")
>>
>
> I'm sorry.  I made a mistake when posting to Gist.  I made a slight tweak
> to the code when posting, and forgot to check if it worked.  (Rookie
> mistake, I know).  The error that you are seeing in the first case is
> not related to threading at all; it is just a bad pointer.  Will you please
> humor me and try one more time with the updated code?
>
> https://gist.github.com/nispio/fe2955c9a0978532cf7b864a8b1b54db
>


It does not change the fact that the code is wrong.

There is meaning of the session/connection - you can't use it simultaneously
from multiple threads even if it is allowed by API,

For example how do you run concurrent transactions from different
threads on same connection?

You can't safely (in terms of logical sessions) use same connection from
multiple threads. And you must deal with DB locked error and be able to recover
from it.

Regards,
    Artyom