[SOLVED] SQLite transactions in multiple threads

[marcus.fr]

Hi

Currently I'm using QSqlDatabase::transaction() and QSqlDatabase::commit() to optimize the write operations to a database. Still on large amount of queries the GUI is blocked. So I want to make some of the transactions in a secondary thread.

According to "http://qt-project.org/doc/qt-4.8/threads-modules.html#threads-and-the-sql-module":http://qt-project.org/doc/qt-4.8/threads-modules.html#threads-and-the-sql-module that will be thread-safe if I create and use another database connection in the secondary thread. I have two questions on this:

1. Does it mean that I can use another connection in a secondary thread to the work on the same database file? I suppose that SQLite internally will take care of a situation when 2 threads try to modify the same database file/table, but I'm not sure.

2. Can I use QSqlDatabase::transaction() and QSqlDatabase::commit() concurrently in different threads, using different database connections but to the same database file/table? What confuses me is the note in http://qt-project.org/doc/qt-4.8/qsqldatabase.html#commit.

I'm asking this because I want to have two database connections, one in the GUI thread (for the QSqlTableModel) and one in a secondary thread (for writing large queries).

Thanks

[MuhamedAuda]

bq. Currently I’m using QSqlDatabase::transaction() and QSqlDatabase::commit() to optimize the write operations to a database. Still on large amount of queries the GUI is blocked. So I want to make some of the transactions in a secondary thread.

Note: For some databases, the commit will fail and return false if there is an active query using the database for a SELECT. Make the query "inactive":http://qt-project.org/doc/qt-4.8/qsqlquery.html#isActive before doing the commit.

[vidar]

I have not done much with Sqlite yet, but I assume that (when using Sqlite) using two threads for committing data will not really speed up the transactions ...
"Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however."

[vidar]

P.S.
1+2) yes & yes. but it will probably not speed up your app as much as you might have expected.

[Zarko Markovic]

Transactions are not really safe in multi-threading environment. You will need to make them safe manually.

See below how QSqlLiteDriver implements the transactions:
@bool QSQLiteDriver::beginTransaction()
{
if (!isOpen() || isOpenError())
return false;

QSqlQuery q(createResult());
if (!q.exec(QLatin1String("BEGIN"))) {
    setLastError(QSqlError(tr("Unable to begin transaction"),
                           q.lastError().databaseText(), QSqlError::TransactionError));
    return false;
}
return true;

}@

Qt starts the transaction in the default mode which is DEFERRED. To use transactions by multiple threads one should use "BEGIN IMMEDIATE TRANSACTION". Read more in: http://www.sqlite.org/lang_transaction.html

This means that you need to make your own functions for transactions which will use the IMMEDIATE type, then use only this type whenever you make transactions in your app. Unfortunately you can not longer use QSqlTableModel safely because it will use the default transaction mode, unless you subclass and implement SQL operations manually.