[Solved] Sqlite and concurrency
-
I know this isn't going to be much of Qt question. I have sqlite database open from my Qt application and i want to update sqlite database outside Qt app (ie from terminal, with Qt app running), if I try so i will get Error: database locked as database is locked by running Qt application. Is there any way to open sqlite database from Qt in a way it would support concurrent access? Or is this impossible?
-
Sounds like you have an uncommited transaction in your qt application. The short answer should be: no. However, I'm not an expert of sql lite, I can suggest to have a look "here":http://sqlite.org/lockingv3.html.
-
I'v tried to open database with:
@
db.setConnectOptions("QSQLITE_OPEN_READONLY");
@
So that database should be opened only for reading by Qt. But if I run sqlite3 in terminal on same database file and do update I am still getting same error.
However if I opened two terminals and run sqlite3 in each one of them with same database I am able to do updates and select on the same database without any errors... -
I found out what I did wrong. I have instanciated QSqlQuery, executed it and didn't destroyed it when it was done, so that might be it...
[quote author="fluca1978" date="1319445374"]Sounds like you have an uncommited transaction in your qt application.[/quote]
This lead me there, thanks!:-) -
The problem probably lies in the "next":http://doc.qt.nokia.com/latest/qsqlquery.html#next method (and others like this). Usually queries are managed using cursors (it should be true also for qt), and a set of results are retrieved (and sometimes cached) from the database. Now this lead to the fact that the cursor provides a read lock on the tuples you wanted to update from the other terminal. And this leads to the error you were experiencing. Destroyng the query object means releasing the cursor, so the lock, and the trick is done.
More complex databases use approaches like "MVCC" to allow a better concurrency even in this scenario.