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

[mrts]

Running the attached test cases in Ubuntu Hardy with Python 2.6, saving models to database fails with

Process ConcurrentAccess-79:
Traceback (most recent call last):
  File "/usr/local/lib/python2.6/multiprocessing/process.py", line 231, in _bootstrap
    self.run()
  File "./db_locked_error.py", line 18, in run
    f.save()
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/base.py", line 307, in save
    self.save_base(force_insert=force_insert, force_update=force_update)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/base.py", line 379, in save_base
    result = manager._insert(values, return_id=update_pk)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/manager.py", line 138, in _insert
    return insert_query(self.model, values, **kwargs)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/query.py", line 888, in insert_query
    return query.execute_sql(return_id)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/sql/subqueries.py", line 308, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/models/sql/query.py", line 1700, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/site-packages/Django-1.0_final-py2.6.egg/django/db/backends/sqlite3/base.py", line 167, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: database is locked

[mrts]

The corresponding Django project

[mrts]

This seems to be SQLite-specific, e.g. MySQL backend is not affected. If it's hard to fix (e.g. extra heavy-weight locking machinery), it should be probably wontfixed.

[Malcolm Tredinnick]

There's no Django bug here; it's expected behaviour. The threadsafety level for sqlite3 is 1, which means connections cannot be shared between threads.

In normal operations, the backend is imported into each thread, so there's a separate DatabaseWrapper instance and hence a separate connection. Your test case is not normal, in that it's reusing the connection between threads. The bug is in the assumption that it's safe to do that.

[Gábor Farkas]

if i'm not mistaken, the test-case uses the multiprocessing-module, so the code is single-threaded, but uses multiple processes.

[mrts]

OperationalError: database is locked seems to imply the code is thread-aware (assuming locked denotes locked by a thread-aware lock). I'm not aware of the internals, but setting DATABASE_OPTIONS = {'timeout': 30} as suggested by Brian Beck in ​http://groups.google.com/group/django-developers/browse_thread/thread/d320de970c2a4016 solved the problem. So connections cannot be shared between threads seems to be incorrect -- Django ORM seems to do it's job quite well when timeout is larger with the sample code.

I'd say this should be documented and I'm willing to do it (unfortunately I'm currently dead busy).

[mrts]

See a related discussion at ​http://lists.initd.org/pipermail/pysqlite/2005-November/000241.html

[mrts]

And at ​http://lists.initd.org/pipermail/pysqlite/2005-May/000021.html

[mrts]

Docs that explain the reason of and solution for the error (AFAICS).

[Ramiro Morales]

Replying to mrts:

OperationalError: database is locked seems to imply the code is thread-aware
[...]
So connections cannot be shared between threads seems to be incorrect -- Django ORM seems to do it's job quite well when timeout is larger with the sample code.

I don't know if these mailing list threads and documentation on multithreaded access to SQLite databases are relevant, as gabor mentioned, the mutiprocessing Python 2.6 module (formerly pyprocessing) you used for your tests don't use threads but processes, see this SQLite ​FAQ entry. It seems very logical the fact that if you are firing 100 (!) processes all of them trying to insert one record to the same SQLite database table, at least some of them will try to do so in a concurent way and you will get the failure.

[mrts]

Where do you get the impression I'm not aware that processes are used instead of threads in multiprocessing? Concurrent processing idioms and problems are the same whether threading or multiprocessing is used, only GIL reduces concurrency (and hides some problems) in threading context a bit. And I'm obviously launching that many processes to assure that the problem indeed manifests, this is a test case.

The mailing list threads are relevant for having a general idea of the issues. I hope the explanation that I've attached is sufficient, if you find it is not or that I've missed something or made a mistake in the explanation, please correct it and attach a new patch.

[Ramiro Morales]

Replying to mrts:

Where do you get the impression I'm not aware that processes are used instead of threads in multiprocessing? Concurrent processing idioms and problems are the same whether threading or multiprocessing is used

I wouldn't add documentation that talks about multithreading misleading people to think it's a supported configuration. If (as you know very well) Django documentation makes no explicit mention about multithreading being supported at all; the why should a warning about failures (with a hypothetical multithreaded scenario reproduced not with threads but concurrent processes) with a DB backend to a software (SQLite) that is the one that supports concurrency to the lesser extent be added?

[mrts]

Your last comment is misleading as a) Django is generally threadsafe and I personally use it routinely with threaded apache and mod_wsgi with no ill effects, b) this is not a thread, but a general concurrency issue that pops up even in Google cache (see ​http://209.85.135.104/search?q=cache:kEMOo-HuvzgJ:www.rkblog.rk.edu.pl/w/p/django-nginx/+django+OperationalError:+database+is+locked ) for live sites running on top of mod_python. You are perhaps right that "threaded environment" and "thread" are not appropriate terms, a more generic term that encompasses both processes and threads might be required. Someone could amend the patch with such a term.

[anonymous]

Replying to mrts:

I've been having this same issue after switching from parallel python (pp) to multiprocessing under 2.5 using psycopg2 -- under heavy load I get the error InternalError: SET TRANSACTION ISOLATION LEVEL must be called before any query (usually, though it can vary). As near as I can tell, this is an issue caused by multiple processes using the same database connection; it seems if I import a model or db.connection anywhere and then run worker processes, the imports aren't "redone" (even if explicitly added as imports in the worker function) which leads to multiple processes using the same db connection which of course leads to them stepping on each other trying to make queries.

I'm not entirely sure they're caused by the same thing, but I figured I'd annotate it here anyway.

[Jacob]

(In [10371]) Fixed a whole bunch of small docs typos, errors, and ommissions.

Fixes #8358, #8396, #8724, #9043, #9128, #9247, #9267, #9267, #9375, #9409, #9414, #9416, #9446, #9454, #9464, #9503, #9518, #9533, #9657, #9658, #9683, #9733, #9771, #9835, #9836, #9837, #9897, #9906, #9912, #9945, #9986, #9992, #10055, #10084, #10091, #10145, #10245, #10257, #10309, #10358, #10359, #10424, #10426, #10508, #10531, #10551, #10635, #10637, #10656, #10658, #10690, #10699, #19528.

Thanks to all the respective authors of those tickets.

[Jacob]

Milestone 1.1 deleted