Subject: bug#32234: Cuirass: The SQLite built in busy handler might block the Fibers scheduler

From: [email-masked] (Ludovic Courtès)
Date: Mon, 23 Jul 2018 11:18:43 +0200

Hi,

Clément Lassieur <[email-masked]> skribis:

> I'm trying to understand why Berlin web API times out[1].

Just to be clear, it does block on things involving non-trivial database
queries:

--8<---------------cut here---------------start------------->8---
ludo@berlin ~$ time wget -q -O -  http://localhost:8081/specifications
real    0m0.007s
user    0m0.004s
sys     0m0.000s
ludo@berlin ~$ time wget -q -O -  http://localhost:8081/api/latestbuilds?nr=2
real    0m28.033s
user    0m0.004s
sys     0m0.000s
--8<---------------cut here---------------end--------------->8---

I’m not sure it’s due to locking though; we may lack an index or two,
“explain query plan” should help.

> I think the SQlite built in busy handler may block the Fibers scheduler.
> We use "PRAGMA busy_timeout = 30000;", which is an alternative to
> calling sqlite3_busy_timeout(), whose description[2] is:
>
>     This routine sets a busy handler that sleeps for a specified amount
>     of time when a table is locked. The handler will sleep multiple
>     times until at least "ms" milliseconds of sleeping have
>     accumulated. After at least "ms" milliseconds of sleeping, the
>     handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY.
>
> To me this sounds like non-cooperative and non-resumable code.

Indeed!

> A solution would be to set a custom handler through
> sqlite3_busy_handler[3] that would be Fibers compatible, i.e. it would
> let the scheduler schedule other fibers instead of just sleeping, using
> Fibers 'sleep' procedure[4].

AIUI the handler is called from C, and thus it’s a non-resumable
continuation, so this wouldn’t work.

Perhaps instead we need to set the timeout to a small value and handle
SQLITE_BUSY at the call site in our code.  We could define a macro that
automatically retries upon SQLITE_BUSY.

Thoughts?

Ludo’.