Sqlite and multi thread

rspock

I'm writing a multi thread application that use sqllite as sql database to store, errors, products and other stuf. I've read from "here":http://qt-project.org/doc/qt-4.8/threads-modules.html#threads-and-the-sql-module that sql queries cannot be done from thread different from the connection thread.

I have this class structure:
A base class with some db service
@
class dbService : public QObject
{
Q_OBJECT
public:
explicit dbService(QObject *parent = 0);
~dbService();

public:

static bool init();
static QSqlDatabase getDb();
static bool isDbOpen();
static QString getLastError();
static bool runQuery(QString sql, QSqlQuery & query);
static bool runQuery(QSqlQuery & query);

protected:
static QSqlDatabase db;
};
@

and then some other table specific service class that inherit from dbService, for example:

@
class errorService : public dbService
{
Q_OBJECT
public:
errorService();
static DbError getLastActiveError(Error & entity);

static DbError findByType(alarmType type, alarmState state , QList<Error> & errorList);
static DbError findByType(alarmType type, QList<Error> & errorList);
static DbError findByState(alarmState state, QList<Error> & errorList);
static DbError findByCode(int code, QList<Error> &errorList);
static DbError findByCode(int code, alarmState state, QList<Error> &errorList);

static DbError findLastByType(alarmType type,Error &entity);
static DbError findLastByCode(int code,Error &entity)

};
@

Theese methos tipically have this body:

@
DbError errorService::getLastActiveError(Error & entity){
QString sql = "SELECT errors.* ... ";
QSqlQuery query;
query.prepare(sql);
query.bindValue(":activeState", active);
if(db.isOpen()){
if (query.exec()){
if(query.next()){
...
}else{
return DbError(tr("errorService - dbToClass: empty query"));
}
}else{
return DbError(tr("errorService - dbToClass: query not done"));
}
}else
return DbError(2000, tr("Database not open"));

return DbError();

}
@

which use the inherited db istance to execute the query.

Theese method can be called from differents threads which is the correct approach to avoid problems?

Can I use a global mutex to lock the use of db istance every time i need to do a query?

Resurrection

I don't think you are doing this the right way.

You should create the connection in another thread meaning you have to call QSqlDatabase::addDatabase from the thread where you want to use the connection to a database (and you should name it as well). So not in constructor but in a method/function that runs AFTER you call QObject::moveToThread.

Then you must pass this connection to your QSqlQuery constructor that itself should be created in the same thread as the connection.

And then you use it in a normal way. So...

@
class MyConnectionWrapper : public QObject
{
public:
MyConnectionWrapper(QObject *parent = 0) : QObject(parent) {}

public slots:
void createConnection()
{
m_Db = QSqlDatabase::addDatabase("QSQLITE", "myConnection");
m_Db.setDatabaseName("myDb");
m_Db.open();
}
void executeQuery(const QString &queryString)
{
QSqlQuery query(m_Db);
query.exec(queryString);
}

private:
QSqlDatabase m_Db; //this is just for convenience, you can always call QSqlDatabase::database("myConnection") instead of using the member variable
}

main(int argc, char *argv[])
{
QApplication a(argc, argv);

MyConnectionWrapper *wrapper = new MyConnectionWrapper;
QThread *thread = new QThread;

//clean up after yourself
connect(thread, &QThread::finished, thread, &QObject::deleteLater);
connect(thread, &QThread::finished, wrapper, &QObject::deleteLater);
//create connection once the thread starts
connect(thread, &QThread::started, wrapper, &MyConnectionWrapper::createConnection);

wrapper->moveToThread(thread);
thread->start();

//ToDo: connect to MyConnectionWrapper::executeQuery to post queries/execute SQL queries asynchronously

return a.exec();
}
@

You can of course modify this, for instance have a name of the database a private member of the wrapper or pass it there via signal/slot. Similarly for calling the queries, you may have them pre-prepared there or passed in to it in a different manner.

However, there is no need for a mutex. You cannot use the connection outside of its thread anyway.

So the bottom line:

You must create the connection using QSqlDatabase::addDatabase in a thread that will use it.
You must NOT use that connection outside of that thread.
You must create the QSqlQuery object in the same thread as the connection.
You must pass the connection to your QSqlQuery constructor (it shall be named connection unless you use default one in which case this is not needed)

So there it is. SQLite is thread-safe and allows concurrent queries BUT it does not support concurrent WRITE queries to the same database. These are delayed and executed consecutively.

Also note that by default SQLite has HDD synchronization enabled and it slows down writing immensely (from your CPU speed to your HDD speed), see: http://www.sqlite.org/pragma.html#pragma_synchronous With that turned off the queries are lightning fast and you may find you do not need to execute them in another thread.

rspock

Ok thank you for your help it was very clear!

I don't need a separated thread for the connection as you shown but I have the connection created by the main thread and then the service method get called by other threads.
Anyway the solution is the same, I can use a public method in the base class which get the sql string and a map of parameters, then I build the query in the base method wich run in the main thread and return back the result.

Should work!

rspock

Just a question about Resurrection replay. That method sound good to execute insert or update or delete. In case of select I need to get the query back and I can't use asynchronous aproach like signal/slot.

Resurrection

[quote author="rspock" date="1414622948"]Just a question about Resurrection replay. That method sound good to execute insert or update or delete. In case of select I need to get the query back and I can't use asynchronous aproach like signal/slot.[/quote]

There are number of options to handle that.

You can send the query via signal once executed (you can iterate over its result anywhere).

You can access it with mutex from another thread if the query is stored as a member in your class that executes queries.

You can also send only QSqlResult via the signal and retrieve data from that.

You can retrieve the data where you executed the query and send them in any form you need...

And there might be more ways. :-)

However doing select asynchronously does not make much sense (whereas doing database updates does in many cases). Therefore I would suggest you perform the selects wherever you need them. Reading the database is very fast operation anyway. Just remember you need to use connection created in the same thread as your query. If the read-write operations should be synchronized in your application you may send signal of database update finished (from the thread that performs it) and connect it with your select.

rspock

All of that approach seems to be asynchronous, maybe I wasn't so clear about my needs.

I have a class (dbService) which handle db connection and sql query, that class is instantiated in the main application thread.

On top of that class I have a DAO layer which is an object per table that have static method to do queries. All of those methods use the dbService class to run the queries.

Error table has static method such as "getLastError()", "getErrorsByType()" and so on. Product table has "getQuantityByType()" "getProducts()". All of those methods are reentrand and need to be run synchronous.

At the end, the application has a couple of thread that use the DAO layer methods and needs to be synchronous call.

In that way the calls to DAO layer methods are run in the calling thread which is not always the thread who instantiated the class which run the query.

Resurrection

EDIT: Looking at your OP. You should go with it and simply make QSqlDatabase not shared but exclusive for each instance by naming it and opening it in its own thread. That is all there is to it actually. It will work and you will avoid all that nasty stuff we talked about. :-) Connection must be encapsulated in its thread and cannot be shared between threads.

rspock

You are in right.

Mixing db stuff in non db related object is not a good pattern but as you said I have no choise!

At least I guess, can I have multiple db connection to the same db in separated thread?

Resurrection

[quote author="rspock" date="1414663342"]Mixing db stuff in non db related object is not a good pattern but as you said I have no choise![/quote]

It is not but you can always use composition over inheritance. In this case it would actually make more sense imho (= have dbService member rather than base). EDIT: Or have it separate, move it to each thread separately and conncet your service with it using signals and slots...

[quote author="rspock" date="1414663342"]At least I guess, can I have multiple db connection to the same db in separated thread? [/quote]

You can have as many connections as you want from any number of threads (or the same thread). Just make sure you name them differently because otherwise they will overwrite each other (or main connection if no name is specified).

Whether and how they will work and perform does not depend on Qt but on SQLite. Luckily SQLite supports this just fine. BUT by default it does not support concurrent writing/reading from the same database and therefore you should switch to WAL journaling mode that supports it (PRAGMA journal_mode="WAL") however only from SQLite 3.7.0 onwards.

rspock

What about to open a new connection on each query request, inside the
@
static bool runQuery(QString sql, QSqlQuery & query)
@
in the base class with a lock and then close the conenction and unlock.

In this way I don't have to relax the static methods but would it be to heavy for the db?

Resurrection

No need to close and open the connection nor recreate it. Simply lock this method and unlock it when its done so no two threads can execute it. But that is unnecessary restriction since SQLite can handle accessing from multiple threads and concurrent writing/reading with journal_mode="WAL".

But you still seem not to understand the limitation of QSql and threads. Connection must be USED in the thread that created it. In your current scenario your connection is created in main thread but your static query execution is being run in different thread = will not work.

You must have a separate object of dbService in main thread and access that object from your services objects in different threads. For example keep a pointer to the dbService object in your services objects or make it a singleton... In any case your method:

@
static bool runQuery(QString sql, QSqlQuery & query)
@

must be part of the dbService object (NOT services objects) and must be executed by it. That is why it needs a mutex lock.

You will also need to take care about what QSqlQuery object you pass to it. That object must be constructed around a connection created in main thread even though it will mean it could not be run in a thread that actually created the query object. And to be honest I am not even sure that would work as maybe even the query must be created in the same thread that will execute it. But that is something you will have to try.

Still I think having it decentralised in every thread with its own connection and query execution would be better solution. At the very least it would be actually making use of concurrency and threading... in your current solution that lock in above mentioned function makes it virtually the same as if you had everything in main thread to begin with - only slower due to mutex and threading overheads.