Sqlite Multithread

[piervalli]

Hi,

With Sqlite and multithread we need to write syncronized.
First i haved improved the multithread with "PRAGMA journal_mode=WAL;".
For synchronization I have declared a QMutex globalMutex witch is used on write.

static auto func = [](int rows,int offset){
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE",QString::number(offset));
        db.setDatabaseName("mydatabase.db"); // Replace with your database name
        db.setConnectOptions(QLatin1String("QSQLITE_ENABLE_REGEXP=1;QSQLITE_BUSY_TIMEOUT=10000"));
        if (!db.open()) {
            qDebug() << "Database connection failed";
        }
        globalMutex.lock();
        db.transaction();
        while(rows>0){
            --rows;
            ++offset;
            QSqlQuery query(db);
            if(!query.prepare("insert into product(id,description) values(:id,:description)")){
                qCritical() << query.lastError();
            }
            query.bindValue(":id",offset);
            query.bindValue(":description",offset);

            if(!query.exec()){
                qCritical() << query.lastError().databaseText();
            }

            if(!query.exec("update product set description ='1' where id =1;")){
                qCritical() << query.lastError();
            }
        }
        db.commit();
        globalMutex.unlock();
    };
    QtConcurrent::run(func,300000,0);
    QtConcurrent::run(func,300000,600001);

The code run without errors. is it correcy use of global QMutex?

Thanks

[ChrisW67]

@piervalli You could use QMutexLocker in place of explicit lock()/unlock() calls.

[Christian Ehrlicher]

I don't see why there is a mutex needed at all - what's the goal for it?

[piervalli]

@Christian-Ehrlicher
With Sqlite we can write only one time to avoid the message error "database locked", we need to synchronize it.

[KenAppleby 0]

@piervalli said in Sqlite Multithread:

is it correcy use of global QMutex?

Well, it's not wrong, but it is effectively serialising your two calls to func, and there is nothing to be gained by using QtConcurrent.

You can let SQLITE do this work for you. See:
https://www.sqlite.org/c3ref/c_config_covering_index_scan.html

As you are using two different database connections (one named "0", the other "600001") they can be accessed safely from separate threads, one thread per connection, without mutexes, if you configure your connection options with "SQLITE_CONFIG_MULTITHREAD" or "SQLITE_CONFIG_SERIALIZED".

Whether this gains you any performance or just pushes the serialisation down the stack into the sqlite driver I don't know.

[piervalli]

@KenAppleby-0

In the documentations of Qt

https://doc.qt.io/qt-5/sql-driver.html#qsqlite

SQLite has some restrictions regarding multiple users and multiple transactions. If you try to read/write on a resource from different transactions, your application might freeze until one transaction commits or rolls back. The Qt SQLite driver will retry to write to a locked resource until it runs into a timeout (see QSQLITE_BUSY_TIMEOUT at QSqlDatabase::setConnectOptions()).

In the Sqlite documentation
https://www.sqlite.org/c3ref/c_config_covering_index_scan.html#sqliteconfigserialized

SQLITE_CONFIG_SERIALIZED
There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1)

Final considerations
Sqlite is builded with flag THREADSAFE=1 we can check with the query "PRAGMA compile_options;", all mutex are enabled and for default configuration thread mode is SQLITE_CONFIG_SERIALIZED. Until the timeout Sqlite retry to write. For my case I changed to timeout to 5 minute i haved limited the transaction to strictly necessary, because the transaction locks all.
In addition PRAGMA journal_mode=WAL; is useful for multithreading.

The global QMutex is not necessary.