Results 1 to 3 of 3

Thread: Problems accessing same SQLite database across multiple processes

  1. #1
    Join Date
    Mar 2014
    Posts
    6
    Qt products
    Qt4
    Platforms
    Windows

    Default Problems accessing same SQLite database across multiple processes

    Hello,

    I am having problems accessing a SQLite db file across multiple processes. I created an extremely simple app in PySide (a Python binding for Qt) to demonstrate. Given the app code below, my two questions are:

    (1.) I would think that, in running this app across multiple processes, db.isOpen() would, in instances of the app opened after the first, evaluate to True. I reviewed the QT source code and it isn't clear to me that this shouldn't be the case. I did notice, though, that the SQLite driver's open() function immediately closes the DB connection if it's already open. (https://qt.gitorious.org/qt/qt/sourc...qlite.cpp#L545)

    (2.) If I open more than one instance of this app, then all instances (including the first opened) are prevented at all times from writing to the database file. Calling the model's insertRow() method hangs the app instance for 5 seconds (which I believe is SQLite's default busy signal time) and prints to the console the error "database is locked." To the question "Can multiple applications or multiple instances of the same application access a single database file at the same time?", the SQLite FAQ reads:

    Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
    So my second question is: why is the database locked? After all, I am only attempting to perform a single database write operation from a single process.

    Below is the test app I created in PySide. For reference, I am using Python 2.7.3, PySide v1.2.1, Qt 4.8, and SQLite 3, and running on Windows 7.

    Qt Code:
    1. from PySide.QtCore import *
    2. from PySide.QtGui import *
    3. from PySide.QtSql import *
    4. import sys
    5.  
    6. class MyDlg(QDialog):
    7.  
    8. def __init__(self):
    9. super(MyDlg, self).__init__()
    10. db = QSqlDatabase.addDatabase("QSQLITE")
    11. db.setDatabaseName("C:/temp/testfile.db")
    12. if db.isOpen():
    13. print "Database is already open by another process!"
    14. db.open()
    15.  
    16. self.model = ListModel()
    17. view = QListView()
    18. view.setModel(self.model)
    19. add_button = QPushButton("Insert row")
    20. layout = QVBoxLayout()
    21. layout.addWidget(view)
    22. layout.addWidget(add_button)
    23. self.setLayout(layout)
    24.  
    25. add_button.clicked.connect(self.addRow)
    26.  
    27. def addRow(self):
    28. text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
    29. self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
    30.  
    31. class ListModel(QAbstractListModel):
    32.  
    33. def __init__(self):
    34. super(ListModel, self).__init__()
    35. self.data_query = QSqlQuery()
    36. self.data_query.prepare("SELECT name FROM names WHERE rowid=?")
    37. self.count_query = QSqlQuery()
    38. self.count_query.prepare("SELECT COUNT(1) FROM names")
    39. self.insert_query = QSqlQuery()
    40. self.insert_query.prepare("INSERT INTO names VALUES (?)")
    41.  
    42. def rowCount(self, parent=QModelIndex()):
    43. self.count_query.exec_()
    44. self.count_query.next()
    45. return self.count_query.value(0)
    46.  
    47. def data(self, index, role=Qt.DisplayRole):
    48. if not index.isValid():
    49. return None
    50. if role == Qt.DisplayRole:
    51. self.data_query.addBindValue(index.row() + 1)
    52. self.data_query.exec_()
    53. self.data_query.next()
    54. return self.data_query.value(0)
    55.  
    56. def insertRow(self, row, parent=QModelIndex(), text="Default"):
    57. self.insert_query.addBindValue(text)
    58. if self.insert_query.exec_():
    59. self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
    60. return True
    61. else:
    62. print self.insert_query.lastError().databaseText()
    63. return False
    64.  
    65. def main():
    66. app = QApplication(sys.argv)
    67. dlg = MyDlg()
    68. dlg.show()
    69. app.exec_()
    70.  
    71. main()
    To copy to clipboard, switch view to plain text mode 

    I know the latter question has more to do with SQLite, but any help is very much appreciated!

  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: Problems accessing same SQLite database across multiple processes

    Doesn't Windows enforce one-application-only file locking?

    Cheers,
    _

  3. #3
    Join Date
    Mar 2014
    Posts
    6
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: Problems accessing same SQLite database across multiple processes

    Hello anda_skoa,

    Thank you for the reply. I am not sure whether Windows applies such file locking automatically. I've been working to move my application's data store from binary files (which could just as well be JSON or XML) to SQLite mostly because of this concern. I can launch multiple instances of the application, open the same file in both, and edit the file in both. Windows by itself provides no warnings about the file's content being modified in another process, and does not prevent either instance from writing to the binary data store. The file's data simply reflects the state of the data in whichever instance of the app was last saved.

    I believe that I have solved this, though, thanks not to Qt's or SQLite's documentation, but Python's on its sqlite3 module, which reads:

    When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).
    I was unaware that the transaction had to be committed to free the db lock. In the test app, the one query that writes to the database is the INSERT query. So, I passed in the db variable to the model, made it an instance variable of the model, and in the insertRow() method I called on the db object the transaction() and then commit() methods (in that order, as the Qt and PySide documentation dictates) following the call to exec_() on the query object. The problem still persisted, and I realized that it's perhaps because the query is an instance variable (prepared in the __init__ method of the model) and therefore remains indefinitely uncommitted. Once I moved the QSqlQuery() object instantiation to the insertRow() method, the problem was fully solved.

    The only caveat is that, because the dataChanged() signal is only emitted when rows are inserted, changes to the data store are not reflected across the multiple open instances of the app until I actually insert another row in that particular instance. So, I'm just thinking how I could best solve this. Right now all that comes to mind is calling reset() on the model with some sort of polling implementation, but I would hate to be resetting the model on such a constant basis.

    Below is my modified code: (Note that db.isOpen() still does not return what I would expect it to.)

    Qt Code:
    1. from PySide.QtCore import *
    2. from PySide.QtGui import *
    3. from PySide.QtSql import *
    4. import sys
    5.  
    6. class MyDlg(QDialog):
    7.  
    8. def __init__(self):
    9. super(MyDlg, self).__init__()
    10. db = QSqlDatabase.addDatabase("QSQLITE")
    11. filename = "C:/temp/testfile.db"
    12. db.setDatabaseName(filename)
    13. if db.isOpen():
    14. print "Database is already open by another process!"
    15. db.open()
    16.  
    17. self.model = ListModel(db)
    18. view = QListView()
    19. view.setModel(self.model)
    20. add_button = QPushButton("Insert row")
    21. layout = QVBoxLayout()
    22. layout.addWidget(view)
    23. layout.addWidget(add_button)
    24. self.setLayout(layout)
    25.  
    26. add_button.clicked.connect(self.addRow)
    27.  
    28. def addRow(self):
    29. text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
    30. self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
    31.  
    32. class ListModel(QAbstractListModel):
    33.  
    34. def __init__(self, db):
    35. super(ListModel, self).__init__()
    36. self.db = db
    37.  
    38. def rowCount(self, parent=QModelIndex()):
    39. count_query = QSqlQuery()
    40. count_query.prepare("SELECT COUNT(1) from names")
    41. count_query.exec_()
    42. count_query.next()
    43. return count_query.value(0)
    44.  
    45. def data(self, index, role=Qt.DisplayRole):
    46. if not index.isValid():
    47. return None
    48. if role == Qt.DisplayRole:
    49. data_query = QSqlQuery()
    50. data_query.prepare("SELECT name FROM names WHERE rowid=?")
    51. data_query.addBindValue(index.row() + 1)
    52. data_query.exec_()
    53. data_query.next()
    54. return data_query.value(0)
    55.  
    56. def insertRow(self, row, parent=QModelIndex(), text="Default"):
    57. insert_query = QSqlQuery()
    58. insert_query.prepare("INSERT INTO names VALUES (?)")
    59. insert_query.addBindValue(text)
    60. if insert_query.exec_():
    61. self.db.transaction()
    62. self.db.commit()
    63. self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
    64. return True
    65. else:
    66. print insert_query.lastError().databaseText()
    67. return False
    68.  
    69. def main():
    70. app = QApplication(sys.argv)
    71. dlg = MyDlg()
    72. dlg.show()
    73. app.exec_()
    74.  
    75. main()
    To copy to clipboard, switch view to plain text mode 

Similar Threads

  1. Wait for multiple processes
    By Eubie in forum Qt Programming
    Replies: 1
    Last Post: 23rd September 2012, 17:39
  2. QUdpSocket broadcast to multiple processes
    By tomschuring in forum Qt Programming
    Replies: 1
    Last Post: 30th September 2010, 02:33
  3. Display from multiple processes in one GUI
    By abernat in forum Qt Programming
    Replies: 3
    Last Post: 17th September 2009, 21:08
  4. QProcess Bash in Event Filter (Multiple Processes)
    By Arsenic in forum Qt Programming
    Replies: 1
    Last Post: 8th November 2008, 09:42
  5. SQLITE database problems
    By phoenix in forum Newbie
    Replies: 3
    Last Post: 30th April 2007, 22:38

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.