> 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
