Results 1 to 9 of 9

Thread: Does QDataWidgetMapper lock sqlite and prevent write by other applications?

  1. #1
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Does QDataWidgetMapper lock sqlite and prevent write by other applications?

    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_

  2. #2
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: Does QDataWidgetMapper lock sqlite and prevent write by other applications?

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

  3. The following user says thank you to anda_skoa for this useful post:

    Al_ (3rd March 2014)

  4. #3
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: Does QDataWidgetMapper lock sqlite and prevent write by other applications?

    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.
    Qt Code:
    1. QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
    2. db.setDatabaseName(QStringLiteral("verzeichnis.sqlite"));
    3. bool ok = db.open();
    4. Q_ASSERT(ok);
    To copy to clipboard, switch view to plain text mode 
    Other hints, how I could locate which code write-blocks the database?

    Best

    Al_

  5. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Does QDataWidgetMapper lock sqlite and prevent write by other applications?

    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.
    Last edited by ChrisW67; 3rd March 2014 at 23:47.

  6. #5
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: Does QDataWidgetMapper lock sqlite and prevent write by other applications?

    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.
    Qt Code:
    1. void MainWindow::on_testButton_clicked(){
    2. QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
    3. db.setDatabaseName(QStringLiteral("/home/user/xxx/xxx.sqlite"));
    4. bool ok = db.open();
    5. Q_ASSERT(ok);
    6. QSqlTableModel* model = new QSqlTableModel(this, db);
    7. model->setTable(QStringLiteral("Farben"));
    8. ok = model->select();
    9. Q_ASSERT(ok);
    10. ui->listView->setModel(model);
    11. ui->listView->setModelColumn(1);
    12. QDataWidgetMapper* mapper = new QDataWidgetMapper(this);
    13. mapper->setModel(model);
    14. mapper->addMapping(ui->lineEdit1, 1);
    15. mapper->toLast();}
    To copy to clipboard, switch view to plain text mode 

  7. #6
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default QSqlTableModel locks sqlite and prevents write by other applications

    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"):
    Qt Code:
    1. #include <QApplication>
    2. #include <QSqlDatabase>
    3. #include <QSqlTableModel>
    4. #include <QSqlQuery>
    5. #include <QSqlError>
    6. #include <QTemporaryFile>
    7. #include <QMessageBox>
    8. #include <QDebug>
    9.  
    10. #define DBSIZE 266
    11.  
    12. int main(int argc, char* argv[]){
    13. QApplication a(argc, argv);
    14. // create a database file in the temporary directory
    15. QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
    16. dbFile->setAutoRemove(false);
    17. bool ok = dbFile->open();
    18. Q_ASSERT(ok);
    19. db.setDatabaseName(dbFile->fileName());
    20. qDebug() << dbFile->fileName();
    21. dbFile->close();
    22. delete dbFile;
    23. ok = db.open();
    24. Q_ASSERT(ok);
    25. // create table 'main' in the database
    26. QSqlQuery query(db);
    27. ok = query.exec(QStringLiteral("CREATE TABLE main(id INTEGER PRIMARY KEY, colorcode INTEGER)"));
    28. Q_ASSERT(ok);
    29. // fill table 'main' with records
    30. ok = query.prepare(QStringLiteral("INSERT INTO main VALUES(:id, 0)"));
    31. Q_ASSERT(ok);
    32. for (int i(0); i < DBSIZE; ++i) {
    33. qDebug() << i;
    34. query.bindValue(0, i);
    35. query.exec();
    36. Q_ASSERT_X(ok, query.lastQuery().toLatin1(), query.lastError().text().toLatin1());}
    37. // use table 'main' in a QSqlTableModel; QSqlTableModel::select() will block the database
    38. QSqlTableModel* model = new QSqlTableModel(0, db);
    39. model->setTable(QStringLiteral("main"));
    40. ok = model->select();
    41. Q_ASSERT(ok);
    42. // show the filename of the database to the user
    43. 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."));
    44. return 0;}
    To copy to clipboard, switch view to plain text mode 
    Last edited by Al_; 8th March 2014 at 15:44. Reason: updated contents

  8. #7
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: QSqlTableModel locks sqlite and prevents write by other applications

    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)
    Qt Code:
    1. while (model->canFetchMore()) model->fetchMore();
    To copy to clipboard, switch view to plain text mode 

  9. #8
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: QSqlTableModel locks sqlite and prevents write by other applications

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

  10. #9
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    11
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android

    Default Re: QSqlTableModel locks sqlite and prevents write by other applications

    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
    Qt Code:
    1. void fetchMore(const QModelIndex &parent)
    2. void QSqlQueryModelPrivate::prefetch(int limit)
    3. bool QSqlQuery::seek(int index, bool relative)
    4. bool QSqlCachedResult::fetch(int i)
    5. bool QSqlCachedResult::cacheNext() // called repeatedly until requested record has been fetched
    6. bool QSqliteResult::gotoNext()
    7. bool QSQLiteResultPrivate::fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch)
    To copy to clipboard, switch view to plain text mode 
    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)."
    Last edited by Al_; 9th March 2014 at 16:10.

Similar Threads

  1. Replies: 1
    Last Post: 25th April 2013, 15:55
  2. Replies: 2
    Last Post: 14th November 2011, 12:24
  3. sqlite read lock.
    By gilgm in forum Qt Programming
    Replies: 6
    Last Post: 18th June 2010, 06:58
  4. sqlite write security
    By lesat in forum Qt Programming
    Replies: 0
    Last Post: 28th April 2010, 06:05
  5. SQLite + journal + lock
    By NoRulez in forum Qt Programming
    Replies: 4
    Last Post: 14th December 2009, 09:25

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.