"database table is locked" error on SQLite when testing with mirror database

[Adam Johnson]

Requirements:

• project uses SQLite for database
• has a "replica" db alias set up as test mirror of default
• TestCase has databases = ["default", "replica"]
• Model is either from or two in a FK relationship
• TestCase tries to create an instance of that model

Result: The SQLite foreign key check, PRAGMA foreign_key_check, fails with an OperationalError like:

Traceback (most recent call last):
  File "/.../site-packages/django/test/testcases.py", line 278, in _setup_and_call
    self._post_teardown()
  File "/.../site-packages/django/test/testcases.py", line 1023, in _post_teardown
    self._fixture_teardown()
  File "/.../site-packages/django/test/testcases.py", line 1266, in _fixture_teardown
    connections[db_name].check_constraints()
  File "/.../site-packages/django/db/backends/sqlite3/base.py", line 322, in check_constraints
    violations = cursor.execute('PRAGMA foreign_key_check').fetchall()
  File "/.../site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/.../site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/.../site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/.../site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/.../site-packages/django/db/backends/utils.py", line 82, in _execute
    return self.cursor.execute(sql)
  File "/.../site-packages/django/db/backends/sqlite3/base.py", line 414, in execute
    return Database.Cursor.execute(self, query)
django.db.utils.OperationalError: database table is locked: core_author

Reproduction project: ​https://github.com/adamchainz/django-ticket-33153 . Simply run ./manage.py test to see the above exception.

Removing the FK from the model, the "replica" entry in AuthorTests.databases, or the mirror declaration in settings.py:DATABASES causes the test to pass.

[Adam Johnson]

Okay so this actually reproduces back to Django 2.2 with the example repo. That makes sense since it was when end-of-test checking of deferrable database constraints was added for SQLite.

It looked like a Django 4.0 specific issue to me because it only started occurring for me when adding Django 4.0 support to django-perf-rec (​PR). I will try work out why it hasn't failed on 2.2 onwards....

Edit: the reason was obvious - I added the mirror DB to the failing test's TestCase.databases on the 4.0 branch.

[Mariusz Felisiak]

I think it's a duplicate of #23718. TEST['MIRROR'] requires TransactionTestCase to work with multiple databases.

[Adam Johnson]

Quite possibly! The project is fixed by using TransactionTestCase.