Regression in Django 3 related to ORM in async tasks (OperationalError: database is locked)

[Andrey Zelenchuk]

​https://docs.djangoproject.com/en/3.1/topics/async/#async-safety

Certain key parts of Django are not able to operate safely in an async environment <...>. The ORM is the main example <...>.

This is not accurate. Actually, the ORM is not able to operate safely in some asynchronous (async) environments (for example, ​async views), but can do it (and perfectly did it in Django 2) in some other async environments (for example, see the steps below).

Starting from Django 3.0 (see ​commit a415ce7), Django ORM prevents reusing database (DB) connections between async tasks. This breaks some use cases that worked before.

Steps to reproduce

The full demo project demonstrating this bug: ​https://github.com/AndreyMZ/django-ticket-31997

It is based on the Polls application from the ​tutorial. The meaningful part is polls/management/commands/demo.py:

import asyncio
import os

import django
from django.core.management.base import BaseCommand
from django.db import transaction
from django.utils import timezone

from polls.models import Question, Choice

N = 100
M = 10


class Command(BaseCommand):
    def handle(self, *args, **options):
        if django.VERSION >= (3, 0):
            # https://docs.djangoproject.com/en/3.1/topics/async/#envvar-DJANGO_ALLOW_ASYNC_UNSAFE
            os.environ["DJANGO_ALLOW_ASYNC_UNSAFE"] = "true"
        
        asyncio.run(handle_async())


async def handle_async():
    with transaction.atomic():
        Question.objects.all().delete()

        async def _process_task(i):
            await asyncio.sleep(0) # Real application would make e.g. an async HTTP request here.
            with transaction.atomic():
                question = Question.objects.create(question_text=f"demo question {i}", pub_date=timezone.now())
                for j in range(N // M - 1):
                    Choice.objects.create(question=question, choice_text=f"demo choice {i}.{j}")

        tasks = [_process_task(i) for i in range(M)]
        await asyncio.gather(*tasks)

To reproduce the bug, checkout the project and run the following:

python manage.py migrate
docker-compose build
docker-compose up django-2
docker-compose up django-3

Actual result

C:\mysite>docker-compose up django-2
Starting mysite_django-2_1 ... done
Attaching to mysite_django-2_1
mysite_django-2_1 exited with code 0

C:\mysite>docker-compose up django-3
Starting mysite_django-3_1 ... done
Attaching to mysite_django-3_1
django-3_1  | Traceback (most recent call last):
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
django-3_1  |     return self.cursor.execute(sql, params)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 413, in execute
django-3_1  |     return Database.Cursor.execute(self, query, params)
django-3_1  | sqlite3.OperationalError: database is locked
django-3_1  |
django-3_1  | The above exception was the direct cause of the following exception:
django-3_1  |
django-3_1  | Traceback (most recent call last):
django-3_1  |   File "manage.py", line 21, in <module>
django-3_1  |     main()
django-3_1  |   File "manage.py", line 17, in main
django-3_1  |     execute_from_command_line(sys.argv)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
django-3_1  |     utility.execute()
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/core/management/__init__.py", line 395, in execute
django-3_1  |     self.fetch_command(subcommand).run_from_argv(self.argv)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 330, in run_from_argv
django-3_1  |     self.execute(*args, **cmd_options)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/core/management/base.py", line 371, in execute
django-3_1  |     output = self.handle(*args, **options)
django-3_1  |   File "/workspace/polls/management/commands/demo.py", line 18, in handle
django-3_1  |     asyncio.run(handle_async())
django-3_1  |   File "/usr/local/lib/python3.7/asyncio/runners.py", line 43, in run
django-3_1  |     return loop.run_until_complete(main)
django-3_1  |   File "/usr/local/lib/python3.7/asyncio/base_events.py", line 587, in run_until_complete
django-3_1  |     return future.result()
django-3_1  |   File "/workspace/polls/management/commands/demo.py", line 32, in handle_async
django-3_1  |     await asyncio.gather(*tasks)
django-3_1  |   File "/workspace/polls/management/commands/demo.py", line 27, in _process_task
django-3_1  |     question = Question.objects.create(question_text=f"demo question {i}", pub_date=timezone.now())
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/manager.py", line 85, in manager_method
django-3_1  |     return getattr(self.get_queryset(), name)(*args, **kwargs)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 447, in create
django-3_1  |     obj.save(force_insert=True, using=self.db)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/base.py", line 751, in save
django-3_1  |     force_update=force_update, update_fields=update_fields)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/base.py", line 789, in save_base
django-3_1  |     force_update, using, update_fields,
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/base.py", line 892, in _save_table
django-3_1  |     results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/base.py", line 932, in _do_insert
django-3_1  |     using=using, raw=raw,
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/manager.py", line 85, in manager_method
django-3_1  |     return getattr(self.get_queryset(), name)(*args, **kwargs)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/query.py", line 1249, in _insert
django-3_1  |     return query.get_compiler(using=using).execute_sql(returning_fields)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1395, in execute_sql
django-3_1  |     cursor.execute(sql, params)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 98, in execute
django-3_1  |     return super().execute(sql, params)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 66, in execute
django-3_1  |     return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
django-3_1  |     return executor(sql, params, many, context)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
django-3_1  |     return self.cursor.execute(sql, params)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
django-3_1  |     raise dj_exc_value.with_traceback(traceback) from exc_value
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
django-3_1  |     return self.cursor.execute(sql, params)
django-3_1  |   File "/usr/local/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 413, in execute
django-3_1  |     return Database.Cursor.execute(self, query, params)
django-3_1  | django.db.utils.OperationalError: database is locked
mysite_django-3_1 exited with code 1

Expected result

C:\mysite>docker-compose up django-2
Starting mysite_django-2_1 ... done
Attaching to mysite_django-2_1
mysite_django-2_1 exited with code 0

C:\mysite>docker-compose up django-3
Starting mysite_django-3_1 ... done
Attaching to mysite_django-3_1
mysite_django-3_1 exited with code 0

Possible solution

We should invent and implement some more sophisticated mechanism to prevent reusing DB connections between async views. Such mechanism must not prevent reusing DB connections between other async tasks.

Workaround (limited)

Variant 1

Patch django/db/utils.py:

django\db\utils.py

@@ +1 @@
+import os
 import pkgutil
+import threading
 from importlib import import_module
 from pathlib import Path
 
@@ -145 +147 @@
         # their code from async contexts, but this will give those contexts
         # separate connections in case it's needed as well. There's no cleanup
         # after async contexts, though, so we don't allow that if we can help it.
-        self._connections = Local(thread_critical=True)
+        if os.environ.get('DJANGO_ALLOW_ASYNC_REUSE_DB_CONNECTIONS'):
+            self._connections = threading.local()
+        else:
+            self._connections = Local(thread_critical=True)
 
     @cached_property
     def databases(self):

Variant 2

Monkey-patch django.db.connections (e.g. in mysite/__init__.py):

import os
import threading

import django.db
import django.db.utils


class ConnectionHandler(django.db.utils.ConnectionHandler):
    def __init__(self, databases=None):
        self._databases = databases
        if os.environ.get('DJANGO_ALLOW_ASYNC_REUSE_DB_CONNECTIONS'):
            self._connections = threading.local()
        else:
            self._connections = django.db.utils.Local(thread_critical=True)

def django_db_connections_exist() -> bool:
    # noinspection PyProtectedMember
    connections = django.db.connections._connections
    databases = django.db.connections.databases
    return any(getattr(connections, alias, None) for alias in databases)

def monkey_patch_django_db_connections():
    assert not django_db_connections_exist()
    django.db.connections = ConnectionHandler()


monkey_patch_django_db_connections()

Warning

Do not use this workaround (do not set the DJANGO_ALLOW_ASYNC_REUSE_DB_CONNECTIONS environment variable) for processes which use ​async views!