Multithreading and sqllite

schouwen

I am switching from qt 5.7 to 5.10. In my code I have in memory database (sqlite) with a shared connection between multiple threads. Maybe not the best idea but it worked fine (in 5.7). In 5.10 though it crashed in the threads so, understanding that shared connection is not the way to go I tried the recommended QSqlDatabase::addDatabase per thread of course with a unique name for each connection. Something like this

auto p = QSqlDatabase::addDatabase("QSQLITE", Identity::newAnonymousName("ilwisobjects_databaseconnection").toLatin1());
p.open();

Before the threads are started the main thread fills the database with 20 tables but the connection(in a thread) says there are 0 tables. Probably I am misunderstanding something but from reading what other people wrote this seems correct.

jsulm

@schouwen You're using different databases in different threads. Filling tables in one thread will not affect databases in other threads. As far as I know SQLite is NOT thread safe, if this is true it is in general a bad idea to access same database from more than one thread without serialising the queries.

mrjj

Hi
Just as a note.
It seems it can be thread safe but i never tried it.
https://sqlite.org/threadsafe.html
So the big questions is if compiled with this flag or
if possible to insert SQLITE_CONFIG_MULTITHREAD
at runtime.
Also, this is "raw" Sqlite, and im not sure how its related to using it via Qt and its classes.

SGaist

Hi,

The key is "in memory database". By default it uses a private cache that is only visible to the thread that opened the connection.

See the SQLite documentation about that here.

schouwen

Ah, thank you. The inmemory database is good hint. That would explain what I am seeing.