Problems accessing same SQLite database across multiple processes

[tristam]

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.

from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *
import sys
 
class MyDlg(QDialog):
 
	def __init__(self):
		super(MyDlg, self).__init__()
		db = QSqlDatabase.addDatabase("QSQLITE")
		db.setDatabaseName("C:/temp/testfile.db")
		if db.isOpen():
			print "Database is already open by another process!"
		db.open()
 
		self.model = ListModel()
		view = QListView()
		view.setModel(self.model)
		add_button = QPushButton("Insert row")
		layout = QVBoxLayout()
		layout.addWidget(view)
		layout.addWidget(add_button)
		self.setLayout(layout)
 
		add_button.clicked.connect(self.addRow)
 
	def addRow(self):
		text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
		self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
 
class ListModel(QAbstractListModel):
 
	def __init__(self):
		super(ListModel, self).__init__()
		self.data_query = QSqlQuery()
		self.data_query.prepare("SELECT name FROM names WHERE rowid=?")
		self.count_query = QSqlQuery()
		self.count_query.prepare("SELECT COUNT(1) FROM names")
		self.insert_query = QSqlQuery()
		self.insert_query.prepare("INSERT INTO names VALUES (?)")
 
	def rowCount(self, parent=QModelIndex()):
		self.count_query.exec_()
		self.count_query.next()
		return self.count_query.value(0)
 
	def data(self, index, role=Qt.DisplayRole):
		if not index.isValid():
			return None
		if role == Qt.DisplayRole:
			self.data_query.addBindValue(index.row() + 1)
			self.data_query.exec_()
			self.data_query.next()
			return self.data_query.value(0)
 
	def insertRow(self, row, parent=QModelIndex(), text="Default"):
		self.insert_query.addBindValue(text)
		if self.insert_query.exec_():
			self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
			return True
		else:
			print self.insert_query.lastError().databaseText()
			return False
 
def main():
	app = QApplication(sys.argv)
	dlg = MyDlg()
	dlg.show()
	app.exec_()
 
main()

from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *
import sys

class MyDlg(QDialog):

	def __init__(self):
		super(MyDlg, self).__init__()
		db = QSqlDatabase.addDatabase("QSQLITE")
		db.setDatabaseName("C:/temp/testfile.db")
		if db.isOpen():
			print "Database is already open by another process!"
		db.open()
		
		self.model = ListModel()
		view = QListView()
		view.setModel(self.model)
		add_button = QPushButton("Insert row")
		layout = QVBoxLayout()
		layout.addWidget(view)
		layout.addWidget(add_button)
		self.setLayout(layout)
		
		add_button.clicked.connect(self.addRow)
		
	def addRow(self):
		text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
		self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
		
class ListModel(QAbstractListModel):

	def __init__(self):
		super(ListModel, self).__init__()
		self.data_query = QSqlQuery()
		self.data_query.prepare("SELECT name FROM names WHERE rowid=?")
		self.count_query = QSqlQuery()
		self.count_query.prepare("SELECT COUNT(1) FROM names")
		self.insert_query = QSqlQuery()
		self.insert_query.prepare("INSERT INTO names VALUES (?)")

	def rowCount(self, parent=QModelIndex()):
		self.count_query.exec_()
		self.count_query.next()
		return self.count_query.value(0)
		
	def data(self, index, role=Qt.DisplayRole):
		if not index.isValid():
			return None
		if role == Qt.DisplayRole:
			self.data_query.addBindValue(index.row() + 1)
			self.data_query.exec_()
			self.data_query.next()
			return self.data_query.value(0)
			
	def insertRow(self, row, parent=QModelIndex(), text="Default"):
		self.insert_query.addBindValue(text)
		if self.insert_query.exec_():
			self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
			return True
		else:
			print self.insert_query.lastError().databaseText()
			return False

def main():
	app = QApplication(sys.argv)
	dlg = MyDlg()
	dlg.show()
	app.exec_()
	
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!

[anda_skoa]

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

Cheers,
_

[tristam]

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.)

from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *
import sys
 
class MyDlg(QDialog):
 
	def __init__(self):
		super(MyDlg, self).__init__()
		db = QSqlDatabase.addDatabase("QSQLITE")
		filename = "C:/temp/testfile.db"
		db.setDatabaseName(filename)
		if db.isOpen():
			print "Database is already open by another process!"
		db.open()
 
		self.model = ListModel(db)
		view = QListView()
		view.setModel(self.model)
		add_button = QPushButton("Insert row")
		layout = QVBoxLayout()
		layout.addWidget(view)
		layout.addWidget(add_button)
		self.setLayout(layout)
 
		add_button.clicked.connect(self.addRow)
 
	def addRow(self):
		text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
		self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
 
class ListModel(QAbstractListModel):
 
	def __init__(self, db):
		super(ListModel, self).__init__()
		self.db = db
 
	def rowCount(self, parent=QModelIndex()):
		count_query = QSqlQuery()
		count_query.prepare("SELECT COUNT(1) from names")
		count_query.exec_()
		count_query.next()
		return count_query.value(0)
 
	def data(self, index, role=Qt.DisplayRole):
		if not index.isValid():
			return None
		if role == Qt.DisplayRole:
			data_query = QSqlQuery()
			data_query.prepare("SELECT name FROM names WHERE rowid=?")
			data_query.addBindValue(index.row() + 1)
			data_query.exec_()
			data_query.next()
			return data_query.value(0)
 
	def insertRow(self, row, parent=QModelIndex(), text="Default"):
		insert_query = QSqlQuery()
		insert_query.prepare("INSERT INTO names VALUES (?)")
		insert_query.addBindValue(text)
		if insert_query.exec_():
			self.db.transaction()
			self.db.commit()
			self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
			return True
		else:
			print insert_query.lastError().databaseText()
			return False
 
def main():
	app = QApplication(sys.argv)
	dlg = MyDlg()
	dlg.show()
	app.exec_()
 
main()

from PySide.QtCore import *
from PySide.QtGui import *
from PySide.QtSql import *
import sys

class MyDlg(QDialog):

	def __init__(self):
		super(MyDlg, self).__init__()
		db = QSqlDatabase.addDatabase("QSQLITE")
		filename = "C:/temp/testfile.db"
		db.setDatabaseName(filename)
		if db.isOpen():
			print "Database is already open by another process!"
		db.open()
		
		self.model = ListModel(db)
		view = QListView()
		view.setModel(self.model)
		add_button = QPushButton("Insert row")
		layout = QVBoxLayout()
		layout.addWidget(view)
		layout.addWidget(add_button)
		self.setLayout(layout)
		
		add_button.clicked.connect(self.addRow)
		
	def addRow(self):
		text, o = QInputDialog.getText(self, "Enter a row to add", "Text")
		self.model.insertRow(self.model.rowCount()+1, QModelIndex(), text)
		
class ListModel(QAbstractListModel):

	def __init__(self, db):
		super(ListModel, self).__init__()
		self.db = db
		
	def rowCount(self, parent=QModelIndex()):
		count_query = QSqlQuery()
		count_query.prepare("SELECT COUNT(1) from names")
		count_query.exec_()
		count_query.next()
		return count_query.value(0)
		
	def data(self, index, role=Qt.DisplayRole):
		if not index.isValid():
			return None
		if role == Qt.DisplayRole:
			data_query = QSqlQuery()
			data_query.prepare("SELECT name FROM names WHERE rowid=?")
			data_query.addBindValue(index.row() + 1)
			data_query.exec_()
			data_query.next()
			return data_query.value(0)
			
	def insertRow(self, row, parent=QModelIndex(), text="Default"):
		insert_query = QSqlQuery()
		insert_query.prepare("INSERT INTO names VALUES (?)")
		insert_query.addBindValue(text)
		if insert_query.exec_():
			self.db.transaction()
			self.db.commit()
			self.dataChanged.emit(self.index(row, 0), self.index(row, 0))
			return True
		else:
			print insert_query.lastError().databaseText()
			return False

def main():
	app = QApplication(sys.argv)
	dlg = MyDlg()
	dlg.show()
	app.exec_()
	
main()

To copy to clipboard, switch view to plain text mode