You should be also aware of a more common pitfall: unclosed result sets. Any lock is held until you read PAST the last row or you call stmt_finalize (or the equivalent abstraction in your DBAL). Always close select statements. On Mar 12, 2015 11:40 AM, "R.Smith" <rsmith at rsweb.co.za> wrote:
> > > 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 >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
