On 4 Oct 2017, at 12:54am, Howard Kapustein <howard.kapust...@microsoft.com>
wrote:
> We use the default busy-handler w/timeout=250ms and call sqlite_exec("PRAGMA
> optimize;") in a loop until success or non-busy/locked error or some ungodly
> amount of time elapses (~5min). The logs indicate PRAGMA optimize; fails due
> to SQLITE_BUSY w/o ever going through the busy-handler, and then we spin
> trying a few thousand times.
Don’t do that. It defeats the purpose of the timeout you set. If you want a
longer timeout than 250ms, set that as your timeout. SQLite’s own retry
process is clever. It uses exponential backoff and can get at the locks at a
lower level than your own code.
I’ve used timeouts in my own code of 5 minutes. And I never had a user report
the error message they’d see if that failed.
> Docs for PRAGMA optimize; says nothing about busy|locked scenarios, nor any
> need to call this in a transaction (nor even if that's legal). Ditto ANALYZE
> docs say nothing about busy|locked scenarios
>
> What's expected if PRAGMA optimize hits a busy|locked scenario?
> What am I the caller expected to do?
The only locking in SQLite is to lock the entire database. So the initial
question is whether you do have some other thread/process accessing the
database.
I would ask you to reconsider why you’re doing "PRAGMA optimize" while other
parts of your program are accessing the database. Right now it tells you only
whether ANALYZE is needed, and there’s no harm in doing ANALYZE when it’s not
needed.
It makes more sense just to put ANALYZE into some sort of end-of-month
procedure or a manual maintenance procedure your users run only when nobody
else is using the system. That way the routine takes a more predictable amount
of time, which users like.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
