Does QDataWidgetMapper lock sqlite and prevent write by other applications?

[Al_]

Hi

My application uses QDataWidgetMapper to map (read and write) from a QSqlRelationalTableModel, in turn getting data from sqlite database tables. Issue: whenever this application runs, the sqlite database is continuously write-locked (SQLITE_BUSY, "Error: database is locked"). I suspect this is due to the use of QDataWidgetMapper as I have no QSqlQuery open.

can someone confirm that this is QDataWidgetMapper behaviour?
is there a workaround?


Best
Al_

[anda_skoa]

Well, it is almost certainly not related to QDataWidgetMapper at all. This class can work with any QAbstractItemModel implementation and does not know anything about databases in general or SQLite in partiuclar.

However: SQLite databases are files, so for example on operating systems that lock files on open (only Wndows has such a limitation as far as I know), one process opening the database will lockout any other.
On operating systems that allow multiple processes to open a file, it could still be something that the SQLite library does in order to ensure data integrity. It could also be something that the Qt SQLite driver QSQLite does.

Cheers,
_

[Al_]

Hi anda-skoa

Thanks for your reply. You are correct, it is not QDataWidgetMapper: the issue persists even if I comment out mapper->setModel(...).

From the other options you mentioned: I do not use MS Windows for development (but linux). Sqlite itself is able to handle multiple access (as per documentation only simultaneous write access is not possible, as it is simply a file; but read and write access are possible). Just opening a database (as below) does also not block write access from another application.

QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
db.setDatabaseName(QStringLiteral("verzeichnis.sqlite"));
bool ok = db.open();
Q_ASSERT(ok);

Other hints, how I could locate which code write-blocks the database?

Best

Al_

[ChrisW67]

This is a description of the default locking mechanism: https://www.sqlite.org/lockingv3.html#rollback
You might get this behaviour on write if the temporary files Sqlite uses to ensure consistency cannot be created/written in the location the database is in. This will be the case on Windows if the database is in the Program Files folder.
You might get this behaviour if a separate process (possibly thread) has a long running write to the same database.

If you can reproduce this with only a single process accessing the file please post a complete, minimal program that reproduces it.

[Al_]

Thanks, ChrisW67. Creation (or write access) of the journal file should not be the issue, as I use linux and the database (thus also the journal file) are in the subdirectory of the user's home directory; the user has rwx access to that directory and manual creation of files (e.g., using 'touch') succeeds.

Writing from the same application is not the issue. Rather, the Qt application prevents other applications from writing to the database.

Further testing using the code below confirms that Q*Views and QDataWidgetMapper can be safely used without permanently locking the database for writing. So, I suspect that one of the QSqlQuery is left open somehow and keeps a SHARED lock (i.e., read lock) on the database, preventing other applications from obtaining a write lock.

void MainWindow::on_testButton_clicked(){
QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
db.setDatabaseName(QStringLiteral("/home/user/xxx/xxx.sqlite"));
bool ok = db.open();
Q_ASSERT(ok);
QSqlTableModel* model = new QSqlTableModel(this, db);
model->setTable(QStringLiteral("Farben"));
ok = model->select();
Q_ASSERT(ok);
ui->listView->setModel(model);
ui->listView->setModelColumn(1);
QDataWidgetMapper* mapper = new QDataWidgetMapper(this);
mapper->setModel(model);
mapper->addMapping(ui->lineEdit1, 1);
mapper->toLast();}

[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
(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;}

[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();

[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,
_

[Al_]

Agreed. As QSqlTableModel implements many things that are ok for me, this looks like an overkill. And since the application uses QSqlRelationalTableModel, I would even need to reimplement this class.

Added after 1 9 minutes:

QSqlQueryModel::setQuery(const QSqlQuery &query) is the function that leads to the lock. In turn, this function calls


void fetchMore(const QModelIndex &parent)
void QSqlQueryModelPrivate::prefetch(int limit)
bool QSqlQuery::seek(int index, bool relative)
bool QSqlCachedResult::fetch(int i)
bool QSqlCachedResult::cacheNext() // called repeatedly until requested record has been fetched
bool QSqliteResult::gotoNext()
bool QSQLiteResultPrivate::fetchNext(QSqlCachedResult:: ValueCache &values, int idx, bool initialFetch)
Only when sqlite3_step(sqlite3_stmt*) returns SQLITE_DONE a call to sqlite3_reset(stmt) is made, i.e., only when there is no more data to fetch. Thus, the lock on the database is only released once all data has been fetched; which is the (undesirable) behaviour I observed.

So, the issue is in the QSQLITE driver. And indeed, after realizing this, I find it described in the Qt docs, see qthelp://org.qt-project.qtsql.520/qtsql/sql-driver.html#general-information-about-qsqlite: "The driver is locked for updates while a select is executed. This may cause problems when using QSqlTableModel because Qt's item views fetch data as needed (with QSqlQuery::fetchMore() in the case of QSqlTableModel)."