PDA

View Full Version : QSqlDatabase, threaded access and locks



neuron
4th February 2009, 12:27
My application has a main window, with a read only model to the database, edit windows, with read/write models, a scraper (in main thread) that edits the database with QSqlQuery and a scanner (seperate thread), that updates data with QSqlQuery from a seperate QSqlDatabase.

Now this should be threadsafe, I'm using sqlite3, and it I have seperate QSqlDatabase connections for the main thread and the scanner, however I'm getting the occasional "database is locked". Which isn't really an error, as far as I can tell it's just another connection using it.

The question is how I can work around it? I was hoping to set sqlite3_busy_timeout() to a few seconds (as no query should take longer), but it doesn't seem to be possible with Qt's sql drivers.

I'm also consider using a QMutex to lock access to the database, the problem is I'm not entirely sure where I need to lock.
For example in transactions in the scanner thread, should I:
1 : lock before the transaction, unlock after
2 : Lock before commit, unlock after.
3 : Lock before UPDATE/INSERT in the transaction, and unlock after.

Of course keeping the lock from start of transaction to the end of "safest", there's a good chance that'll lock up my gui :/

sepehr
4th February 2009, 16:45
I guess the reason you are getting "database is locked" is because since Sqlite databases are simply files on harddrive and one connection at a time blocks the file, other threads with separate connections can not access the database so you may consider locking the database when one connection is using it



Of course keeping the lock from start of transaction to the end of "safest", there's a good chance that'll lock up my gui

you stated queries take few moments,so that won't be a problem

neuron
4th February 2009, 16:55
I guess the reason you are getting "database is locked" is because since Sqlite databases are simply files on harddrive and one connection at a time blocks the file, other threads with separate connections can not access the database so you may consider locking the database when one connection is using it

Sqlite3 can have infinite number of read processes, and only locks on writes.



you stated queries take few moments,so that won't be a problem

Yeah, the issue is I start a transaction for the files table, do a file scan, then commit. I've now rewritten that to do file scan, transaction, commit data from file scan, commit. And that'll allow me to lock during the entire transaction without a major performance issue.