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