Problems with SQLite database locked

Hi,

I have recently started using Cellprofiler (v. 11515) in batch mode on a Windows 2008 server (32 bit). Previously, I’d output data to CSV file via ExportToSpreadsheet, but since Cellprofiler overwrites those files in batch mode, I have switched to using ExportToDatabase to export the data into a SQLite file. This works fine for most of the time; however, when several Cellprofiler instances are running simultaneously, it seems they sometimes block each other when trying to write data to the database. This results in Cellprofiler terminating during the ExportToDatabase module, reporting that the database was locked. The more instances I have running at the same time, the higher the likelihood of this occuring.
To remedy this, would it be possible to modify the ExportToDatabase module to something like this (in pseudo code) “while (database is locked) wait, try to write data again”.

cheers,
Karl

Hi Karl,

This question has come up before here. In short, we have decided not to implement a means to allow simultaneous writes to an SQLite database due to the complexity of the problem.

Regards,
-Mark

Hi Mark,

Sorry, I didn’t see that the question had already been posed.

Wouldn’t it be possible to solve the problem by increasing the timeout value when connecting to the SQLite database? So that the database would wait longer for the lock to be removed?
I.e. change (in exporttodatabase.py):

def connect_sqlite(db_file): '''Creates and returns a db connection and cursor.''' import sqlite3 connection = sqlite3.connect(db_file) cursor = connection.cursor() return connection, cursor

to e.g.

def connect_sqlite(db_file): '''Creates and returns a db connection and cursor.''' import sqlite3 connection = sqlite3.connect(db_file, timeout=30) cursor = connection.cursor() return connection, cursor

Unfortunately, I don’t have the developer’s version installed so I can’t try it myself.

cheers,
Karl

That doesn’t sound unreasonable, Karl. I’ll make that change - it seems a reasonable compromise. Also, for anyone else viewing this, please look at the caveats for SQLite multiprocessing - the locking may not work for NFS file systems: sqlite.org/faq.html#q5

Hi Karl,

Just a quick follow-up (after a long delay): The adjustment Lee mentioned is not yet released, but you can get Lee’s change with our latest public build from source code here. The trunk build is not as heavily vetted as our releases, so you should take that into account; please note the caveats mentioned on the page linked.

Regards,
-Mark