Na, it should be the same, everywhere I said finalize you can replace with reset; I had this problem come over and over from people forgetting to finalize (there was no intention to reuse the statement). But reset is the same. My point is just that if you don't have explicit transactions, a read lock is acquired by the first step() and not released until free() or reset() or step() returning SQLITE_MISUSE due to one too many calls and you should check one-rowers (COUNT is the usual suspect for me) first to make sure they are finalized (reset) properly because they are the easiest to miss, since they don't have an ugly loop following. Other than that, there is no reason a lock should be held in autocommit mode.
You could debug the reader client this way: create a second connection and try to create a write lock (BEGIN EXCLUSIVE) at various points. It will return SQLITE_BUSY when you have a leaked lock. On 13.03.2015 02:17, Barry wrote: > 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
