[sqlite] Releasing a read (SHARED) lock
Hmmm, I am guilty of not always stepping until I get SQLITE_DONE (I am
usually selecting by the primary key, and therefore know I will only get a
single result), but I make sure to always call sqlite_reset, so I don't
think that should be the issue.
On further reflection, I suspect it might be the 'obscure side-effect'
mentioned by Dinu. I do have functions that look like:
//bind all parameters
//...
//begin querying statement1:
//Note: stmt1 is only queried once (and hence is still 'open' for now)
sqlite3_step(stmt1);
//Use the results from statement1 to bind statement2
//...
//begin querying statement2:
while(SQLITE3_OK == sqlite3_step(stmt2)) {
//Do things
}
//reset both statements
sqlite3_reset(stmt2);
sqlite3_reset(stmt1);
So, in this situation I do in fact have two SELECT statements open
simultaneously, which should trigger the unreleasable lock. I will try
change my code and post results here.
On 13 March 2015 at 09:02, Dinu Marina wrote:
> You should also check, as R.Smith and Slavin pointed, that you don't
> accidentally have a BEGIN [DEFERRED] somewhere, as that would trigger
> exactly this behavior: it will lock on the first select (the first step()
> actually)
>
>
> On 13.03.2015 02:44, Simon Slavin wrote:
>
>> On 13 Mar 2015, at 12:17am, Barry wrote:
>>
>> On 13 March 2015 at 01:21, Dinu Marina 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?
>>>
>> In order to dispose of a statement you can do either sqlite3_reset() or
>> sqlite3_finalize(). The common mistake is to do something like this:
>>
>> SELECT * FROM myTable WHERE rowid = 36
>>
>> and then not do either _reset() or _finalize() because you know you have
>> asked for only one row so you expect SQLite to have done a _finalize() for
>> you.
>>
>> Simon.
>> ___
>> 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] Releasing a read (SHARED) lock
On 13 March 2015 at 01:21, Dinu Marina 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" 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] Releasing a read (SHARED) lock
You should also check, as R.Smith and Slavin pointed, that you don't accidentally have a BEGIN [DEFERRED] somewhere, as that would trigger exactly this behavior: it will lock on the first select (the first step() actually) On 13.03.2015 02:44, Simon Slavin wrote: > On 13 Mar 2015, at 12:17am, Barry wrote: > >> On 13 March 2015 at 01:21, Dinu Marina 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? > In order to dispose of a statement you can do either sqlite3_reset() or > sqlite3_finalize(). The common mistake is to do something like this: > > SELECT * FROM myTable WHERE rowid = 36 > > and then not do either _reset() or _finalize() because you know you have > asked for only one row so you expect SQLite to have done a _finalize() for > you. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Releasing a read (SHARED) lock
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 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" 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 >>>
[sqlite] Releasing a read (SHARED) lock
On Fri, 13 Mar 2015 08:17:26 +0800, Barry wrote: >On 13 March 2015 at 01:21, Dinu Marina 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? sqlite3_reset() is enough to release the state/context of a statement. Aditionally, any BEGIN TRANSACTION should be paired with a COMMIT or ROLLBACK. >Cheers, > > - Barry -- Regards, Cordialement, Groet, Kees Nuyt
[sqlite] Releasing a read (SHARED) lock
On 13 Mar 2015, at 12:17am, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina 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? In order to dispose of a statement you can do either sqlite3_reset() or sqlite3_finalize(). The common mistake is to do something like this: SELECT * FROM myTable WHERE rowid = 36 and then not do either _reset() or _finalize() because you know you have asked for only one row so you expect SQLite to have done a _finalize() for you. Simon.
[sqlite] Releasing a read (SHARED) lock
On 3/12/15, Barry wrote: > On 13 March 2015 at 01:21, Dinu Marina 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? > One or the other. Call sqlite3_reset() if you intend to reuse prepared statement (run it again). Call sqlite3_finalize() to destroy the prepared statement and release all of its resources back to the system to be reused. Either is sufficient to release any locks held. -- D. Richard Hipp drh at sqlite.org
[sqlite] Releasing a read (SHARED) lock
There is also a more obscure side-effect exhibited by sqlite only: if you issue consecutive unfinalized select statements, it never releases the write lock (although they should be atomic), but it does trip the deadlock mechanism; any writer in the wait will receive SQLITE_BUSY at the very moment the second read statement is issued, regardless of the busy_timeout value. This happens in non-WAL mode. The usual culprit are one-record results, like SELECT COUNT(*)... usually the client expects one row and reads one row, but you need to either try to read 2 rows or explicitly free the cursor. Do note (for evangelism sake) that DBs with random-access cursors do not free the read lock even when you read past the last record, because you could decide to rewind. So always free result sets.
[sqlite] Releasing a read (SHARED) lock
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" 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] Releasing a read (SHARED) lock
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] Releasing a read (SHARED) lock
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. 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
