Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround

[Thomas Lotterer]

On Sun, Mar 13, 2005, Jan-Eric Duden wrote:

> The issue 1159 was marked as "not_a_bug".
> So is there a misunderstanding?
> 
Maybe. I tried hard to explain my point of view.

--
[EMAIL PROTECTED], Cable & Wireless

Re: [sqlite] getting rid of dirty SQLITE_BUSY workaround

[Thomas Lotterer]

Re,
this solution rocks. Thanks a lot!

On Sun, Mar 13, 2005, D. Richard Hipp wrote:
> On Sun, 2005-03-13 at 16:49 -0500, D. Richard Hipp wrote:
> > On Sun, 2005-03-13 at 21:56 +0100, Thomas Lotterer wrote:
> > 
> > > I cannot believe it is normal behavior of a database application
> > > running on a multitasking operating system to assume there will
> > > only be one writer and otherwise let the application fail or do
> > > retries by itself.
> > > 
> > 
> > I'll look into it.
> > 
> 
> http://www.sqlite.org/cvstrac/chngview?cn=2385
> 
I imported that improvement into our OpenPKG sqlite package [1] and did
a lot of testing today. As reported previously with the original code
and as little as two writers I ran into the infinity/failure problem.
Now with that patch applied the database access works as I expected it:

using
- no busy_handler occasionally results in SQLITE_BUSY errors
- the busy_timeout sometimes results in SQLITE_BUSY erros
- a busy_handler doing infinite retries the write eventually succeeds

Of course, not using a busy_handler must result in SQLITE_BUSY penalty
even for a small amount of parallelism. Using busy_timeout is a perfect
balance between successful writing and preventing the application to
appear to hang. For computers having patience with writing using a
infinite retry will really succeed sometime.

This time I stressed the database with 100 parallel writers each doing
100 inserts. Everything worked fine as expected and described above.
Exactly 1 entries made it into the database.

I want to mention an interesting side effect. Some processes have bad
karma and have to wait a very long time for others to complete. Even for
others which were launched later. The whole 100x100 activity executes
in a highly unbalanced manner. It is my understanding that this works
as designed because the database has no scheduler. It's just a matter
of luck who catches the next unlocked state after the previous process
releases it's locks. This doesn't hurt me. I just want to document the
effect.

Finally I rebuilt my perl-dbi to come back to my original issue and the
problem vanished, too. Good job!

[1] http://cvs.openpkg.org/chngview?cn=22577

--
[EMAIL PROTECTED], Cable & Wireless

[sqlite] getting rid of dirty SQLITE_BUSY workaround

[Thomas Lotterer]

I've created an application that makes use of SQLite3. Occasionally,
multiple instances of that application run at the same time and need
to write to the database simultaneously. When I decided to use SQLite
I was under the impression that the support for multiple writers doing
massive parallel writes in SQLite is weak but I was assuming this is a
performance issue only. However, it turned out that this is a functional
limitation. Even with only two writers INSERTs fail in a nonpredictable
manner. I played with the busy handler and opened a ticket [1] but in
the end I can only choose between deadlock and failure. There is a
workaround for the latter encapsulating every SQL execution with a retry
when the failure was SQL_BUSY and that's exactly what I did [2].

I cannot believe it is normal behavior of a database application running
on a multitasking operating system to assume there will only be one
writer and otherwise let the application fail or do retries by itself.

What are you guys doing to prevent such conditions? I mean, if someone
uses SQLite for a very simple web application that application must
either only read the database. Or otherwise the users have to pray no
two of them ever click the submit button at the same time.

[1] http://www.sqlite.org/cvstrac/tktview?tn=1159
[2] http://cvs.openpkg.org/chngview?cn=22124

--
[EMAIL PROTECTED], Cable & Wireless