"OperationalError: database is locked" with Python 2.6 multiprocessing and SQLite backend

[mrts]

It seems that current DB lock management doesn't play nice with the
new Python 2.6 multiprocessing package and SQLite. See [1]. The same
error also popped up in Google search under mod_python [2].

I wasn't able to reproduce this with MySQL.

[1] http://code.djangoproject.com/ticket/9409
[2] http://209.85.135.104/search?q=cache:kEMOo-HuvzgJ:www.rkblog.rk.edu.pl/w/p/django-nginx/+django+OperationalError:+database+is+locked

[Ned Batchelder]

I wouldn't expect SQLite to do well in a multi-process environment.
Concurrency is SQLite's weak point, which makes sense given its heritage
as an embedded database.

--Ned.
http://nedbatchelder.com

--
Ned Batchelder, http://nedbatchelder.com

[Brian Beck]

On Oct 21, 7:27 am, mrts <m...@mrts.pri.ee> wrote:
> It seems that current DB lock management doesn't play nice with the
> new Python 2.6 multiprocessing package and SQLite. See [1]. The same
> error also popped up in Google search under mod_python [2].

As others have pointed out, this isn't an issue with Django. The
easiest solution is to make this error less common with a higher
timeout. In settings.py:

DATABASE_OPTIONS = {'timeout': 30}

[mrts]

On Oct 27, 5:16 pm, Brian Beck <exo...@gmail.com> wrote:
>
> As others have pointed out, this isn't an issue with Django.  The
> easiest solution is to make this error less common with a higher
> timeout.  In settings.py:
>
> DATABASE_OPTIONS = {'timeout': 30}

Thanks Brian, increasing the timeout fixed the problem.

IMHO this should be documented, so I reopened http://code.djangoproject.com/ticket/9409
and changed the component to Documentation.

[mrts]

On Oct 27, 6:57 pm, mrts <m...@mrts.pri.ee> wrote:
> On Oct 27, 5:16 pm, Brian Beck <exo...@gmail.com> wrote:
> > As others have pointed out, this isn't an issue with Django.  The
> > easiest solution is to make this error less common with a higher
> > timeout.  In settings.py:
>
> > DATABASE_OPTIONS = {'timeout': 30}
>
> Thanks Brian, increasing the timeout fixed the problem.
>

> IMHO this should be documented, so I reopenedhttp://code.djangoproject.com/ticket/9409

> and changed the component to Documentation.

I've attached the explanation to
http://code.djangoproject.com/attachment/ticket/9409/database_is_locked_docs.diff

[Brian Beck]

On Oct 27, 1:46 pm, mrts <m...@mrts.pri.ee> wrote:
> > IMHO this should be documented, so I reopenedhttp://code.djangoproject.com/ticket/9409
> > and changed the component to Documentation.
>

> I've attached the explanation tohttp://code.djangoproject.com/attachment/ticket/9409/database_is_lock...

I agree, and this explanation looks good. +1

[oggie rob]

> I agree, and this explanation looks good.  +1

Its a bit deeper than that... but I'm waiting for my friend to respond
(he worked on sqlite issues at my last company). Hopefully I'll hear
from him today and be able to add some more details.

-rob

[oggie rob]

On Oct 27, 11:21 am, oggie rob <oz.robhar...@gmail.com> wrote:
> Its a bit deeper than that... but I'm waiting for my friend to respond
> (he worked on sqlite issues at my last company). Hopefully I'll hear
> from him today and be able to add some more details.

Okay, so I got the good word :)

First off, I want to acknowledge the work that Ben Cottrell put into
finding these issues. It seems like this is usually discovered only
with a specific setup and it took a lot of time and effort for him to
track down the issues.

Essentially you'll need to use pysqlite 2.5.0 for this to work. Even
increasing the timeouts won't "solve" the problem, they'll just allay
them a little (and at some point you'll see the locks again).
Certainly, there is a limit to how quickly transactions can get done
with sqlite (and thus the timeout is still relevant), but I don't
think you will want to rely on that.

The reason pysqlite 2.5.0 works is due to the fix in changeset 337
(http://oss.itsystementwicklung.de/trac/pysqlite/changeset/
337%3A80ee6488cb53). It is much more likely to be noticed if you're
running in a multi-threaded environment (and from what I could
determine, this is the reason it hasn't been more of an issue to the
django community, given that sqlite is fairly widely used).

Also, because django 'prefers' sqlite3 to pysqlite, you'll need to
patch django/db/backends/sqlite3/base.py after you've installed it.
(This change should probably be merged into trunk as it allows for
installing the most recent version of pysqlite, as opposed to whatever
sqlite3 you have in your python version. In other words, I think
django should "prefer" pysqlite as a more intentional setup.)

Finally, one issue we didn't really dig into but Ben did discover, is
changing from a shared (i.e. "reading") to an exclusive (i.e.
"writing") lock. Example: you're iterating through a queryset, and
inside the same transaction you do some write operation, *and* you
have threads colliding with each other (both trying to get an
exclusive lock), you may see the db locked exception. A simple
workaround for this is to force an evaluation on your iterating
queryset (i.e. wrap it in list()), or run the write operations after
you have finished the iteration.

If this looks like a lot of work to get sqlite going, you might be
right :)

I hope that covers everything. I'm willing to update the ticket, but
first do you think you could try the pysqlite fix that I suggested
(and remove the timeout change to be certain that doesn't interfere)?
I want to be sure that it solves it.
-rob