[Ur] sqlite, sequence, database is locked

[Adam Chlipala]

That's an interesting idea, Austin.  I was going to respond that my 
intention has been for Ur/Web to hide all issues with concurrency 
control, restarting transactions as necessary.  I believe that logic 
already works properly for PostgreSQL, and I just don't do as much 
testing with SQLite.  So, I would be happy to implement either the kind 
of solution I had in mind OR the solution that Austin suggests.

To implement either fix, I'll need some detailed suggestions specific to 
SQLite.

*OPTION 1.* Change the generated C code to detect concurrency errors 
that imply restarts.  Someone would ideally build a representative 
minimal example with "urweb -debug" and examine the generated 
/tmp/webapp.c file, to make suggestions on changes.

*OPTION 2.* Change the SQL schemas generated automatically by Ur/Web, to 
add extra settings that preclude the problem Sergey reported.  Someone 
would need to use the 'sql' .urp directive with SQLite as the backend, 
then tell me which diff should be applied to generated files.  It sounds 
like Austin probably already knows the exact change that's called for, 
but I'm presenting both options in case it becomes clear that Option 1 
is preferable.

On 02/28/2017 01:28 PM, Austin Seipp wrote:
> An alternative might be to turn on WAL mode for your SQLite database. 
> This will allow
> readers and writers (SELECT and INSERTs) to proceed concurrently and 
> might alleviate
> this problem for you, without having to do some kind of heuristic backoff.
>
> (Perhaps Ur/Web should enable WAL by default in the Schema it 
> generates for SQLite)
>
> On Tue, Feb 28, 2017 at 11:42 AM, Sergey Mironov <grrwlf at gmail.com 
> <mailto:grrwlf at gmail.com>> wrote:
>
>     Hi. My sqlite-based application uses sequences during GET processing.
>     AFAIK sequences are implemented as INSERTs for sqlite backend, so I
>     think the question is about generic sqlite behavior.
>
>     In my application, some transactions take a considerable time to
>     produce a page. During the process, other requests, even those which
>     only access sequences, are denied with 'Database is locked' error. Is
>     it possible to treat the sqlite locking error as a 'Bounded error'
>     rather than to terminate the request immediately? By bounded error I
>     mean to attempt to process transaction several times after certain
>     delay. What are possible negative effects of such a modification?
>
>     Regards,
>     Sergey
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.impredicative.com/pipermail/ur/attachments/20170228/148c5bea/attachment.html>