Qt multithreaded transactions in SQLite

Question

How can one get access to transactions using SQLite through QSqlDatabase ?

I open the database as follows:

m_db = QSqlDatabase::addDatabase("QSQLITE", connection_name);
m_db.setDatabaseName(db_name);
m_db.open();

I create two such connections - for my Read and Write threads. The problem is that I have a large data stream to write, therefore I have to use begin and commit transactions at least in the Write thread. Write thread works well, but I cannot make any select request until Write thread closed its connection. Here is how I use transactions:

m_db.transaction(); // BEGIN TRANSACTION

// Many QSqlRequest-s like so:
QString insert = "INSERT INTO mytable (x, y, z) VALUES (:x, :y, :z);"
QSqlQuery query(m_db);
bool ok = query.prepare(insert);
if (ok)
{
    query.addBindValue(x);
    query.addBindValue(y);
    query.addBindValue(z);
    //...
    if (query.exec())
    {
        // ok
    }
    else
    {
        // show error
    }
}
else
{
    // show error
}

m_db.commit(); // COMMIT TRANSACTION

This is a common using. The problem is these standard operations don't support multithreading using. How can I get access to normal multithreading transactions? QSqlDatabase supports threads by default, and I can read/write in both threads without transactions, but transactions are necessary. How can I setup all usual multithreading options using QSqlDatabase and SQLite?

I can invoke q.exec("BEGIN IMMEDIATE TRANSACTION"); , but it returns error while using in both threads: database is locked Unable to fetch row.

Links:
"Database is locked" error in SQLite3 with Qt
Why does SQLite give a "database is locked" for a second query in a transaction when using Perl's DBD::SQLite?

Answer 1

This is a FAQ. SQLite does not have much concurrency support.

To allow one writer and readers at the same time, use WAL mode.