On 13 March 2015 at 01:21, Dinu Marina <dinumarina at gmail.com> wrote:
> 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. Hi Dinu, Am I correct in my understanding then that it is not enough to call sqlite3_reset, I must call sqlite3_finalize after I am done with a statement? Cheers, - Barry > 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 > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
