Does QDataWidgetMapper lock sqlite and prevent write by other applications?

[Al_]

Hi all

It seems QSqlTableModel in some cases blocks SQLite databases, i.e., prevents write access by another process. When running the application below, the database given in the header of the QMessageBox is locked after the QMessageBox shows up. To try it, open a terminal window and type

• sqlite3 </path/to/database> "UPDATE main SET colorcode=1 WHERE id=5;"

If DBSIZE in the test application source code is sufficiently high (266 in my case, but could be different on other machines or operating systems), then I get "Error: database is locked".

Once the QMessageBox has been closed, the database is open for write-access. And at that point, the database can be opened by several sqlite3 instances in parallel without blocking write-access. Thus, it is not a bug in sqlite but apparently a bug in Qt.

Is anyone aware of a work-around? My application crucially depends on the database being accessible while my application runs.

Best

Al_

PS: I have now reported this as bug, see https://bugreports.qt-project.org/browse/QTBUG-37348

Test application (the .pro project file needs to include "QT += core widgets sql"):

#include <QApplication>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlError>
#include <QTemporaryFile>
#include <QMessageBox>
#include <QDebug>
 
#define DBSIZE 266
 
int main(int argc, char* argv[]){
   QApplication a(argc, argv);
   // create a database file in the temporary directory
   QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
   QTemporaryFile* dbFile = new QTemporaryFile;
   dbFile->setAutoRemove(false);
   bool ok = dbFile->open();
   Q_ASSERT(ok);
   db.setDatabaseName(dbFile->fileName());
   qDebug() << dbFile->fileName();
   dbFile->close();
   delete dbFile;
   ok = db.open();
   Q_ASSERT(ok);
   // create table 'main' in the database
   QSqlQuery query(db);
   ok = query.exec(QStringLiteral("CREATE TABLE main(id INTEGER PRIMARY KEY, colorcode INTEGER)"));
   Q_ASSERT(ok);
   // fill table 'main' with records
   ok = query.prepare(QStringLiteral("INSERT INTO main VALUES(:id, 0)"));
   Q_ASSERT(ok);
   for (int i(0); i < DBSIZE; ++i) {
      qDebug() << i;
      query.bindValue(0, i);
      query.exec();
      Q_ASSERT_X(ok, query.lastQuery().toLatin1(), query.lastError().text().toLatin1());}
   // use table 'main' in a QSqlTableModel; QSqlTableModel::select() will block the database
   QSqlTableModel* model = new QSqlTableModel(0, db);
   model->setTable(QStringLiteral("main"));
   ok = model->select();
   Q_ASSERT(ok);
   // show the filename of the database to the user
   QMessageBox::warning(0, db.databaseName(), QStringLiteral("With sufficiently high DBSIZE, the above database is now erroneously locked. On my Ubuntu machine, DBSIZE 266 is sufficient."));
   return 0;}

#include <QApplication>
#include <QSqlDatabase>
#include <QSqlTableModel>
#include <QSqlQuery>
#include <QSqlError>
#include <QTemporaryFile>
#include <QMessageBox>
#include <QDebug>

#define DBSIZE 266

int main(int argc, char* argv[]){
   QApplication a(argc, argv);
   // create a database file in the temporary directory
   QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
   QTemporaryFile* dbFile = new QTemporaryFile;
   dbFile->setAutoRemove(false);
   bool ok = dbFile->open();
   Q_ASSERT(ok);
   db.setDatabaseName(dbFile->fileName());
   qDebug() << dbFile->fileName();
   dbFile->close();
   delete dbFile;
   ok = db.open();
   Q_ASSERT(ok);
   // create table 'main' in the database
   QSqlQuery query(db);
   ok = query.exec(QStringLiteral("CREATE TABLE main(id INTEGER PRIMARY KEY, colorcode INTEGER)"));
   Q_ASSERT(ok);
   // fill table 'main' with records
   ok = query.prepare(QStringLiteral("INSERT INTO main VALUES(:id, 0)"));
   Q_ASSERT(ok);
   for (int i(0); i < DBSIZE; ++i) {
      qDebug() << i;
      query.bindValue(0, i);
      query.exec();
      Q_ASSERT_X(ok, query.lastQuery().toLatin1(), query.lastError().text().toLatin1());}
   // use table 'main' in a QSqlTableModel; QSqlTableModel::select() will block the database
   QSqlTableModel* model = new QSqlTableModel(0, db);
   model->setTable(QStringLiteral("main"));
   ok = model->select();
   Q_ASSERT(ok);
   // show the filename of the database to the user
   QMessageBox::warning(0, db.databaseName(), QStringLiteral("With sufficiently high DBSIZE, the above database is now erroneously locked. On my Ubuntu machine, DBSIZE 266 is sufficient."));
   return 0;}

To copy to clipboard, switch view to plain text mode 

[Al_]

Hi all

I found a work-around: prefetch the entire table. Obviously, this is only feasible for small database tables (small compared to available memory).

Add the following statement after select()'ing the model (i.e., after line 42 in my example)

while (model->canFetchMore()) model->fetchMore();

while (model->canFetchMore()) model->fetchMore();

To copy to clipboard, switch view to plain text mode 

[anda_skoa]

Another option would be to implement your own QAbstractTableModel subclass and use QSqlQuery internally.

Since you know which database you are using, this model might even use database specific features, e.g. paging.

Cheers,
_