Thread safe of sqlite with different connection name

[tham]

If I open two db of sqlite with different connection names, could I treat them as two independent database, or I need to lock the db if I call them in different threads?Thanks

QSqlDatabase alert_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "alert_db");
QSqlDatabase visit_db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), "visit_event_db");

[JonB]

@tham

or I need to lock the db if I call them in different threads

Are you aware that you must not perform database operations in threads other than the one where addDatabase() was called?

[tham]

Are you aware that you must not perform database operations in threads other than the one where addDatabase() was called?

No, thanks for your information, this help a lot.

[JonB]

@tham
I don't know what you were planning to do across threads, but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

[tham]

@JonB

but you could only really do it if you create each of those two databases in separate threads and only access each database in its own thread. Then (so far as I am aware) you could access them from their own threads without risking interference.

This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

1. create different connection in different thread
2. use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

If you want to use it with QtConcurrent::run, is following solution work?

lock_guaryd<mutex>  guard(mutex_);
return QtConccurrent::run([=]()
{
      {
          auto db = QSqlDatabase::addDatabase("QSQLITE", conName);
          db.setDatabaseName("alert");
         //open db, transaction, insert data blah blah blah
     }
    QSqlDatabase::removeDatabase(conName);
});

This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite(only insert when cache is big enough or closing the app), looks a bit clumsy.

[jsulm]

@tham Why do you actually want to access the database from different threads? If you anyway synchronize the access with mutexes you will not gain any performance improvements, but complicate your app. You should access the db from one thread. Other threads can send data (or get data) to/from db using signals.

[JonB]

@tham said in Thread safe of sqlite with different connection name:

This is what I need. From the Qt doc, I think the safe way to access the same sqlite table is

create different connection in different thread
use mutex to lock the non-thread safe operation of sqlite(ex : transaction)

Could you refer me to where in the documentation you find this suggestion?

This solution need to create new connection, open db, remove connection blah blah every time when I need to insert data into the sqlite

It is "unusual" (and costly) to have to add/open remove/close a database each time you want to insert data?

Exactly as @jsulm has just posted: one begins to wonder why in the world you want threads at all, wouldn't you be better/simpler and even possibly quicker to do it all single-threaded and not have to do this mutexing and especially opening/closing each time??

[tham]

@jsulm

If you anyway synchronize the access with mutexes you will not gain any performance improvements, but complicate your app. You should access the db from one thread. Other threads can send data (or get data) to/from db using signals.

Because the main purpose is not increase the throughput, but prevent the main gui freeze, everytime I insert the data(after I call exec), the frame freeze, although this is not frequently happen, customers don't like it. That is why I want to use QtConcurrent to insert the data, since it is easier to use(don't need to create a worker class and move it to QThread etc)

@JonB

Could you refer me to where in the documentation you find this suggestion?

No, that is the solution I come up, not sure if it is correct.

It is "unusual" (and costly) to have to add/open remove/close a database each time you want to insert data?

As I though, but this do not happen frequently, I only insert the data into db after cache is full.

wouldn't you be better/simpler and even possibly quicker to do it all single-threaded and not have to do this mutexing and especially opening/closing each time??

Tried it before, but the frame freeze every time when I insert the data

[JonB]

@tham

Because the main purpose is not increase the throughput, but prevent the main gui freeze,

Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

[tham]

@JonB said in Thread safe of sqlite with different connection name:

Even if this threading is your solution, why do you want two, separate threads for your two databases? If you put both (all) of them in a single thread dedicated for database interaction, you would avoid all this nasty mutexing problem, wouldn't you??

Because I am too lazy to create a worker class for them, so I use QtConcurrent instead of that, the price is the behavior looks unusual. Maybe I would move them into a worker class in the future, but not now.

Anyway, thanks for your suggestions, they help me a lot.

[kshegunov]

You may not be able to access them in separate threads at all, mutexes or no mutexes. The Qt API gives no guarantees that the SQL driver would keep reentrancy! So this question would be answered by actually looking at the SQL driver and/or finding a person that is familiar with the implementation. The answer is implementation (and database) specific.

[tham]

@kshegunov Thanks for your advice. After checking, I am sure in my case, the functions related to the database do not need to be reentrant, I make sure they work sequential with the help of lock(same lock for the same table of sqlite3). Do I miss some important points which may cause the program unstable(undefined behavior etc)?