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 <om at integral.be> 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.
