"database table locked errors" when using sqlite in-memory database with LiveServerTestCase

[Juozas Masiulis]

I am getting database table locked errors, when running tests which use sqlite in memory database.
This issue was introduced with this commit: ​https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a
I know this because reversing this commit resolves the issue for me.
If sqlite in memory testing is no longer supported, it should be mentioned in the documentation. If it is supported, there needs to be some parameter in the settings to use liveserver without multithreading. Or just make a new class liveservermultithreaded.

[Tim Graham]

Please provide a sample project that reproduces the problem.

[Juozas Masiulis]

​https://github.com/Euphorbium/django-bug-demo here is the sample project. Sorry for the mess in the test, the issue is with multiprocessing. The test pass with my version of django, check requirements, and fails with the unmodified django.

[Tim Graham]

Is the error consistent? I ran your sample project ten times and the test usually passes. Once in a while, I see this failure:

======================================================================
FAIL: testStuff (bugdemo.tests.TestDatabaseLocking)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django-bug-demo/bugdemo/tests.py", line 75, in testStuff
    self.assertEqual(Question.objects.count(), 30)
AssertionError: 29 != 30

I think you'll have to explain why Django is at fault to move this report forward. Possibly the problem might be dependent on the Python version. I tested with Python 3.4.7, 3.5.5, and 3.6.4.

[Juozas Masiulis]

It is a race condition. It is consistent in my system, but it gets locked by node process, and I did not want to go outside of python to demonstrate the bug. It needs something faster to lock the database consistently.

[Tim Graham]

Unfortunately your comment doesn't help with my understanding of the problem. Can explain why Django is at fault or propose a solution? If not, I'm not sure how to move this issue forward.

[Juozas Masiulis]

Have you tried running it with my version, which is in the requirements.txt? That NEVER fails. This is proof that it is a django problem, and it was introduced in ​https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a

[Tim Graham]

Just because the behavior changed doesn't mean Django is at fault. You'll need to investigate and explain why Django is at fault. It might be that your testing code is at fault.

[Andrew Badr]

Yep, I'm hitting this too. See workaround at ​https://stackoverflow.com/questions/48353002/sqlite-database-table-is-locked-on-tests

[Andrew Badr]

Moving to 'Accepted' -- breaking test suites is a serious enough bug, and multiple people have hit it. Someday/Maybe is not appropriate here.

[Daniel Hahler]

Just a drive-by comment since I've seen a similar issue and found this: check/ensure that transactions are enabled (i.e. with pytest-django transactional_db instead of db should be used).
Also check the code around ​https://github.com/django/django/commit/bece837829eafbc22f2598dadf82c9a8364b085a#diff-5d7d8ead1a907fe91ffc121f830f2a49L1287.

[Chris Spencer]

Replying to Andrew Badr:

Yep, I'm hitting this too. See workaround at ​https://stackoverflow.com/questions/48353002/sqlite-database-table-is-locked-on-tests

This workaround does not work for me. I'm using the LiveServerTestCase to run a series of Selenium tests checking Ajax functionality, so the result of switching the LiveServerThread back to single-threaded WSGIServer only replaces the deadlock error with a timeout error. The Ajax calls timeout waiting for the main server thread to exit, which it never does.

[Matthijs Kooijman]

What seems to be relevant here (might be obvious for others, but was not for me) is that Sqlite does not support a "wait until the lock is released" flow with the way Django implements transactions on Sqlite. This means that whenever you try to write to a database that already holds a write (EXCLUSIVE in Sqlite terms I think) lock, it will immediately fail rather than wait for the lock to be released. The underlying reason for this is described in https://code.djangoproject.com/ticket/29280

[Chris Jerdonek]

FYI, yesterday I found and described the root cause of this issue on #32416. (There I was interested in a different issue that affected non-SQLite databases, though, namely database connections not being closed when LiveServerTestCase is used with the new threading behavior.)

If you read my first comments on that issue, you can see that a possible fix is to define suitable method overrides in ThreadedWSGIServer. Basically the same SQLite-specific connection-sharing logic that LiveServerTestCase.setUpClass() and LiveServerThread.run() currently have needs to be added to ThreadedWSGIServer. The LiveServerTestCase and LiveServerThread logic can be found more or less here:
​https://github.com/django/django/blob/63d239db037f02d98b7771c90422840bbb4a319a/django/test/testcases.py#L1524-L1537
​https://github.com/django/django/blob/63d239db037f02d98b7771c90422840bbb4a319a/django/test/testcases.py#L1465-L1469

And ThreadedWSGIServer's thread creation logic can be found here in CPython's ThreadingMixIn.process_request() (this is where you can work out what needs to be overridden):
​https://github.com/python/cpython/blob/v3.9.1/Lib/socketserver.py#L656-L665

[Chris Jerdonek]

I posted a PR (​PR #14011) on ticket #32416 that, once merged, I believe should allow this ticket to be fixed with a one-line change (not counting any test(s)):

diff --git a/django/test/testcases.py b/django/test/testcases.py
index cc2f24dd5d..8717eec1c5 100644
--- a/django/test/testcases.py
+++ b/django/test/testcases.py
@@ -1472,7 +1472,9 @@ class LiveServerThread(threading.Thread):
         try:
             # Create the handler for serving static and media files
             handler = self.static_handler(_MediaFilesHandler(WSGIHandler()))
-            self.httpd = self._create_server()
+            self.httpd = self._create_server(
+                connections_override=self.connections_override
+            )
             # If binding to port zero, assign the port allocated by the OS.

[Chris Jerdonek]

FYI, #32416 has now been fixed and ​PR #14011 merged (thanks, Mariusz!), which should make this ticket a lot easier to resolve (see comment above).

[Christophe Baldy]

I posted a PR for this ticket (​https://github.com/django/django/pull/16309) following Chris Jerdonek previous fix

[Mariusz Felisiak <felisiak.mariusz@…>]

In 855f5a36:

Fixed #29062 -- Prevented possibility of database lock when using LiveServerTestCase with in-memory SQLite database.

Thanks Chris Jerdonek for the implementation idea.