On 2015-03-12 04:38 AM, Barry wrote: > Hello everybody, > > I have a situation where two processes are accessing the same SQLite > database. One process only reads from the database, one process reads and > writes. > > These processes keep a single database connection open for the lifetime of > the process. > > It seems to me that once the reader process accesses the database (after it > performs its first SELECT statement), it maintains a lock on the database > until the connection is closed (when the program is exited). This prevents > the writer process from updating the database.
Hi Barry, This is the usual situation when one of your transactions in the "reading only" database does not finalize. i.e. you started a transaction there and did not end it with either "END TRANSACTION" or "COMMIT" or "ROLLBACK". In the non-WAL DB it will simply hold the lock preventing changes. In the WAL DB it will hold the lock for its own view of the data but let the other writer write.. however it won't see the changes for itself. This is very common actually. Just find every transaction you start (explicitly or implicitly) and make sure you end it and when you end it, see what the return value is from SQLite and whether it reports any error. HTH! Ryan > > I tried changing to WAL. This made the writer process able to commit its > changes, but now the reader does not see any modifications made to the > database until it is restarted (It seems to see a snapshot of the DB at the > time of its first read). > > I am using prepared statements: On opening the DB, I create all my prepared > statements. When I need to execute a statement, I bind to the statement, > call sqlite3_step (possibly multiple times), then call sqlite3_reset. I do > not finalise the statements until the program closes. > > In order to simulate 'save' behaviour, the writer process always holds a > transaction open. When the user chooses 'save', the current transaction is > committed and a new transaction is begun. (I understand that the reader > will not see any changes in the uncommitted transaction, but is not seeing > any committed transactions either). > > I have checked quite thoroughly through my code and cannot find any > instances of statements executed without a sqlite3_reset quickly following. > > Is this intended behaviour - that once a connection has performed a read, > it maintains its lock on the database for its lifetime? Is SQLite smart > enough to know that the pages it holds in cache of the reader are invalid > after the writer has made changes to the DB on disk? > > If this is not the intended behaviour - is there a way I can find out which > statements are causing the lock to be held open? Or can I force SQLite to > discard its cache? > > Any help would be appreciated. > > Regards, > > Barry Smith > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
