[bug#33210] Cuirass: Use a SQLite in single-thread mode

["Clément Lassieur" <clement@lassieur.org>]

Hey Danny,

Danny Milosavljevic <dannym@scratchpost.org> writes:

> Hi Clément,
>
> On Tue, 06 Nov 2018 01:50:11 +0100
> Clément Lassieur <clement@lassieur.org> wrote:
>
>> > This is not supposed to happen in relational database systems.  The reason
>> > why it happens here is because we use the same transaction (session) for
>> > both the updates done by the evaluator and the queries done by the web
>> > interface.  It would be much better if the queries by the web interface
>> > had their own transaction.  It was fine before but in some refactoring,
>> > "evaluate" ceased to be an external program and I didn't notice what
>> > happened to it.  
>> 
>> Yes, I know, but I remember that I told you[1] that there was an easy
>> (one line) fix for this. :-)
>
> That would really only be a workaround (arguably still better than what we
> have now - which is basically you ask for a banana and I give you an apple
> - what if other procedures in cuirass assume it's a banana? :) ).
>
> We would be doing the work that SQLite would have done, but we'd do it in a bad
> way (by blocking everyone else).
>
> It's not really useful to undo all the progress relational databases have made.
>
> If we had multiple transactions in progress touching the same row, SQLite would
> use MVCC to hold diverging copies of the same row at the same time, blocking
> nobody.

It blocks everyone indeed, because we take care of the scheduling in a
rather basic way.  But if I understand correctly, the overall spent time
is more or less the same: only the order of the requests differs.  There
is an essential difference however: if we take care of the scheduling,
we won't have SQLITE_BUSY blocking the Fibers scheduler all the time.
And blocking the Fibers scheduler has an impact on all other possibly
unrelated Fibers clients.  When guile-sqlite3 handles SQLITE_BUSY
correctly, I'll be happy to switch back to a multi-threading SQLite.
While waiting for it, I believe our users want a fast Cuirass, and I'd
like the time spent in the Fibers scheduler to be balanced by removing
the SQLite now useless mutexes.

Does that make sense?

Clément