[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
On 15 Jan 2016, at 4:20pm, Olivier Mascia wrote: > I'm only left with the case of a row returning query which the programmer > would not want to step() up to the end. In this case, it is very reasonable > to have the programmer think of "freeing" the query in some way through > either the Statement class going out of scope or the call of some "reset()" > method. This is a common problem when you're writing a library. Assuming that you can provide a _dispose() method which is automatically called when your object slips out of scope, you're fine: just call _finalize() in it. If you cannot depend on this then you are going to have to provide a disposal method and rely on your programmer calling it. Simon.
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
Richard, Simon, > Le 15 janv. 2016 ? 16:30, Richard Hipp a ?crit : > >> But the finalize of a >> statement (using the C++ wrapper we build for our use over C SQLite API) is >> currently differed until another prepare is done using the same Statement >> object, or until the Statement object goes out of scope (destructor). > > That's a problem. Until a prepared statement is either finalized or > reset, SQLite must assume that you might call sqlite3_step() on it > again. And so it has to continue holding various locks and other > state that will be needed by that subsequent sqlite3_step() call. I think this is well understood now. Thanks Richard. > A better approach would be to invoke sqlite3_reset() as soon as you > know that the prepared statement will not be stepped again. That will > release locks (and other resources) and help other operations to > proceed unimpeded. > Le 15 janv. 2016 ? 16:31, Simon Slavin a ?crit : > > Immediately after doing your first _step() you should be thinking to do your > _finalize() or _reset() as soon as practical. It should weigh on your mind > like an open door in a high-crime area. Having done either of those, you can > keep the statement around as long as you like: all it will use up is a little > memory. Thanks Richard and Simon. We indeed need to design the Statement class differently to sqlite3_reset() it as soon as possible. And I now realize that I overlooked one detail, which will help a lot. For select-kind queries I can capitalize on sqlite3_step() having to be called until it returns SQLITE_DONE (so one more time than there are rows). When that happens, I can sqlite3_reset() immediately. The programmer has been done with the previous (last row) since before the last call to step(). Same thing for SQLITE_OK, which would be returned for successful non-row returning queries, it can call reset() right away. I'm only left with the case of a row returning query which the programmer would not want to step() up to the end. In this case, it is very reasonable to have the programmer think of "freeing" the query in some way through either the Statement class going out of scope or the call of some "reset()" method. You see, the whole purpose of using a thin set of classes over the C API is to make it as easy as possible to merge SQL statements within the C++ code of the application. I have done this exercise for some other databases, but I'm new at SQLite. Again thank you very much for the very valuable input this mailing list provides. When we will be code-complete and able to decide for SQLite in our next releases, we will happily contract one of the paid professional support options HWACI offers. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
> Le 15 janv. 2016 ? 13:08, Hick Gunter a ?crit : > > I think you may mean "database connection" instead of "statement handle". > > When you are finished with processing a statement, you should either reset > (if you intead to use it again later) or finalize it. Otherwise SQLite must > assume that you want to continue later and needs to keep around whatever > locks it acquired during processing. Which is also the cause for > sqlite3_wal_checkpoint returning SQLITE_LOCKED. > > You should not be reusing a statement handle before calling finalize on it, > as this would cause a memory leak. Thanks Hick, Be sure I'm not reusing any statement handle without finalizing it. No leaks, we have a checker in place to detect. But the finalize of a statement (using the C++ wrapper we build for our use over C SQLite API) is currently differed until another prepare is done using the same Statement object, or until the Statement object goes out of scope (destructor). We might have to add a method to 'clear' our Statement objects as soon as not needed anymore. Though that probably is not very important in our real programming, the configuration I had in the test which returned LOCKED on the wal_checkpoint is 'synthetic' and not really representative. A Statement is generally re-used for successive different queries while a method is doing whatever its job is, then the local Statement goes out of scope (so its last instance gets finalized). What remains in a multiple consumer set of processes or threads is that at any point in time while there exist a single not-yet-finalized statement, wal_checkpoint is bound to return LOCKED very easily, drastically reducing the windows of time where it could be called successfully. Would it make sense (or even be valid) to use the sqlite3_unlock_notify() mechanism on attempts to wal_checkpoint as we do it successfully on prepare or step? -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
On 15 Jan 2016, at 3:18pm, Olivier Mascia wrote: > Be sure I'm not reusing any statement handle without finalizing it. No > leaks, we have a checker in place to detect. But the finalize of a statement > (using the C++ wrapper we build for our use over C SQLite API) is currently > differed until another prepare is done using the same Statement object, or > until the Statement object goes out of scope (destructor). We might have to > add a method to 'clear' our Statement objects as soon as not needed anymore. > Though that probably is not very important in our real programming, the > configuration I had in the test which returned LOCKED on the wal_checkpoint > is 'synthetic' and not really representative. A Statement is generally > re-used for successive different queries while a method is doing whatever its > job is, then the local Statement goes out of scope (so its last instance gets > finalized). Please don't do this. Until you have done your _finalize() or _reset() that statement can be taking up a lot of memory and/or have temporary files created and/or have locks on files. You want to release all this stuff as soon as possible to prevent your program from being a memory/disk/resource/handle hog. Remember that the only reason _prepare, _step, _finalize/_reset are not all one command is that not all operating systems allow SQLite to callback your program to tell it that the next row is ready. Immediately after doing your first _step() you should be thinking to do your _finalize() or _reset() as soon as practical. It should weigh on your mind like an open door in a high-crime area. Having done either of those, you can keep the statement around as long as you like: all it will use up is a little memory. Simon.
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
> Le 15 janv. 2016 ? 11:54, Olivier Mascia a ?crit : > > What are the circumstances leading to: > > int status = sqlite3_wal_checkpoint_v2(conn, "main", > SQLITE_CHECKPOINT_PASSIVE, 0, 0); > > returning SQLITE_LOCKED immediately? > > > It looks like that the simple fact of having a select statement prepared, > then ran, but not yet finalized, on that same connection, induces the > SQLITE_LOCKED return. I can get it to go away by simply finalizing the last > statement before trying checkpoint. I just did not expected LOCKED to be > returned. Reading https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I > expected to get SQLITE_BUSY. My misunderstanding probably. I probably should have been more descriptive about the conditions: For the purpose of the test, there are some (at least one) transactions committed waiting to be checkpointed and those transactions did inserts on a table, which is the one selected (outside of any explicit transaction). In pseudo code, this means: With one statement handle: begin transaction insert into T(C) values(xyz) commit transaction With the same statement handle: begin transaction select ... from T commit transaction Again with the same statement handle: select ... from T (*) And finally: sqlite3_wal_checkpoint_v2 as above, returning SQLITE_LOCKED. Merely finalizing the statement at point (*) right after the (out of explicit transaction) select and the wal_checkpoint returns SQLITE_OK. Is this perfectly expected? In a somehow more complex configuration where multiple threads or processes (all using distinct connections and private cache) could be running quite any query at any time, it could become quite hard to find an appropriate slot of time to call sqlite3_wal_chekcpoint not returning LOCKED. Should it be valuable to use the sqlite3_unlock_notify mechanism on attempts to wal_checkpoint returning SQLITE_LOCKED? (I understand that in this simple test, if the mere un-finalized statement is the reason of the LOCKED, it won't help, but I'm thinking more general in a multi-consumer processes or threads model.) It might very well be much easier then to leave it to the automated checkpointing mode of SQLite, eventually changing its default of 1000 pages before trigger, if it better fit my needs. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
I think you may mean "database connection" instead of "statement handle". When you are finished with processing a statement, you should either reset (if you intead to use it again later) or finalize it. Otherwise SQLite must assume that you want to continue later and needs to keep around whatever locks it acquired during processing. Which is also the cause for sqlite3_wal_checkpoint returning SQLITE_LOCKED. You should not be reusing a statement handle before calling finalize on it, as this would cause a memory leak. -Urspr?ngliche Nachricht- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Olivier Mascia Gesendet: Freitag, 15. J?nner 2016 12:18 An: SQLite mailing list Betreff: Re: [sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances? > Le 15 janv. 2016 ? 11:54, Olivier Mascia a ?crit : > > What are the circumstances leading to: > > int status = sqlite3_wal_checkpoint_v2(conn, "main", > SQLITE_CHECKPOINT_PASSIVE, 0, 0); > > returning SQLITE_LOCKED immediately? > > > It looks like that the simple fact of having a select statement prepared, > then ran, but not yet finalized, on that same connection, induces the > SQLITE_LOCKED return. I can get it to go away by simply finalizing the last > statement before trying checkpoint. I just did not expected LOCKED to be > returned. Reading https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I > expected to get SQLITE_BUSY. My misunderstanding probably. I probably should have been more descriptive about the conditions: For the purpose of the test, there are some (at least one) transactions committed waiting to be checkpointed and those transactions did inserts on a table, which is the one selected (outside of any explicit transaction). In pseudo code, this means: With one statement handle: begin transaction insert into T(C) values(xyz) commit transaction With the same statement handle: begin transaction select ... from T commit transaction Again with the same statement handle: select ... from T (*) And finally: sqlite3_wal_checkpoint_v2 as above, returning SQLITE_LOCKED. Merely finalizing the statement at point (*) right after the (out of explicit transaction) select and the wal_checkpoint returns SQLITE_OK. Is this perfectly expected? In a somehow more complex configuration where multiple threads or processes (all using distinct connections and private cache) could be running quite any query at any time, it could become quite hard to find an appropriate slot of time to call sqlite3_wal_chekcpoint not returning LOCKED. Should it be valuable to use the sqlite3_unlock_notify mechanism on attempts to wal_checkpoint returning SQLITE_LOCKED? (I understand that in this simple test, if the mere un-finalized statement is the reason of the LOCKED, it won't help, but I'm thinking more general in a multi-consumer processes or threads model.) It might very well be much easier then to leave it to the automated checkpointing mode of SQLite, eventually changing its default of 1000 pages before trigger, if it better fit my needs. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
Dear all, What are the circumstances leading to: int status = sqlite3_wal_checkpoint_v2(conn, "main", SQLITE_CHECKPOINT_PASSIVE, 0, 0); returning SQLITE_LOCKED immediately? It looks like that the simple fact of having a select statement prepared, then ran, but not yet finalized, on that same connection, induces the SQLITE_LOCKED return. I can get it to go away by simply finalizing the last statement before trying checkpoint. I just did not expected LOCKED to be returned. Reading https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I expected to get SQLITE_BUSY. My misunderstanding probably. I have no problem with that, I'm merely gathering knowledge of how to best use it. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/o=m
[sqlite] sqlite3_wal_checkpoint_v2() returning SQLITE_LOCKED: in what circumstances?
On 1/15/16, Olivier Mascia wrote: > But the finalize of a > statement (using the C++ wrapper we build for our use over C SQLite API) is > currently differed until another prepare is done using the same Statement > object, or until the Statement object goes out of scope (destructor). That's a problem. Until a prepared statement is either finalized or reset, SQLite must assume that you might call sqlite3_step() on it again. And so it has to continue holding various locks and other state that will be needed by that subsequent sqlite3_step() call. A better approach would be to invoke sqlite3_reset() as soon as you know that the prepared statement will not be stepped again. That will release locks (and other resources) and help other operations to proceed unimpeded. -- D. Richard Hipp drh at sqlite.org
