#29280: Fix for a class of SQLite "database is locked" problems
-------------------------------------+-------------------------------------
Reporter: Ove Kåven | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) |
Severity: Normal | Keywords: sqlite, databases
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 1
UI/UX: 0 |
-------------------------------------+-------------------------------------
I'd like to propose a change like this, which I think would fix a class of
SQLite "database is locked" problems, e.g. maybe #29062. But it's possible
you want to add a config option, or an argument to transaction.atomic(),
or something of the kind.
{{{
diff --git a/django/db/backends/sqlite3/base.py
b/django/db/backends/sqlite3/base.py
index 3989028930..391a50789e 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -272,7 +272,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
Staying in autocommit mode works around a bug of sqlite3 that
breaks
savepoints when autocommit is disabled.
"""
- self.cursor().execute("BEGIN")
+ self.cursor().execute("BEGIN IMMEDIATE")
def is_in_memory_db(self):
return self.creation.is_in_memory_db(self.settings_dict['NAME'])
}}}
Explanation: Consider a type of transaction consisting of, for example, an
update_or_create() call, which you run in a "with transaction.atomic()"
context. Suppose two threads or processes run such a transaction at
exactly the same time (but with different keys, doesn't matter, SQLite
locks the whole database anyway).
1. The current transaction.atomic() implementation results in a "BEGIN",
which tells SQLite to start deferred transactions, i.e., to *not* acquire
any locks before it has to.
2. Normally, update_or_create would first do a "SELECT FOR UPDATE", but
SQLite doesn't support that, so a plain "SELECT" is done. Both threads
acquire a shared read lock, and so the SELECTs succeed for both threads.
3. Next, update_or_create needs to do an INSERT or UPDATE (not important
which), so the threads needs to upgrade the read locks to write locks.
Unfortunately, only one thread can have the write lock.
Now, the designers of SQLite apparently realized that if you already have
the read lock, then it's not a good idea to start a blocking wait on the
write lock. If you wait while holding the read lock, then nobody will ever
be able to acquire exclusive access to the database, and everything will
just hang. On the other hand, if you drop the read lock and then wait,
then you lose the 'serializable' isolation guarantee that SQLite tries to
give. Hence, SQLite has only one choice.
4. The thread that didn't get the write lock immediately gets a "database
is locked" error. Its transaction is aborted.
The timeout mentioned in the Django documentation will have absolutely no
effect on this, and it doesn't matter how short-lived your transactions
are. It only matters that they do a read before they do a write.
I can see three possible solutions to this problem:
1. "Don't do that". Don't use SQLite, or don't do concurrency. (Many
answers on StackOverflow.com and such are essentially this.)
2. Treat the "database is locked" as if it were a "serialization error",
which it kind of is. That is, the app must retry the transaction until it
commits. That works, but is somewhat unsatisfactory.
3. Grab the write lock immediately (like "SELECT FOR UPDATE" would have,
if SQLite had supported it). That's what "BEGIN IMMEDIATE", in the above
patch, does. After all, if you're not holding the read lock when you try
to grab the write lock, then this particular problem won't happen. (Of
course, the lock timeout thing that's mentioned in the Django
documentation can still happen if your transactions are too long-lived,
but that's different.)
And I think that if you're using "with transaction.atomic()" in the first
place, then you most likely want to write to the database, in which case
there's not much reason not to always grab the write lock right away. But
even if it's decided that some config option would be good, it probably
shouldn't be too hard to add? Or?
--
Ticket URL: <https://code.djangoproject.com/ticket/29280>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.8b6bd74bbf0257177044b893d9c1a3cf%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
