Opened 6 years ago

Closed 16 months ago

#29062 closed Bug (fixed)

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

Reported by: Juozas Masiulis Owned by: Christophe Baldy
Component: Testing framework Version: 2.0
Severity: Normal Keywords: sqlite, testing, databases
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (24)

comment:1 by Tim Graham, 6 years ago

Please provide a sample project that reproduces the problem.

comment:2 by Tim Graham, 6 years ago

Resolution: needsinfo
Status: newclosed
Summary: Sqlite in memory database does not work for testing."database table locked errors" when using sqlite in-memory database with LiveServerTestCase

comment:3 by Juozas Masiulis, 6 years ago

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.

comment:4 by Juozas Masiulis, 6 years ago

Resolution: needsinfo
Status: closednew

comment:5 by Tim Graham, 6 years ago

Resolution: worksforme
Status: newclosed

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.

comment:6 by Juozas Masiulis, 6 years ago

Resolution: worksforme
Status: closednew

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.

comment:7 by Tim Graham, 6 years ago

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.

comment:8 by Tim Graham, 6 years ago

Resolution: needsinfo
Status: newclosed

comment:9 by Juozas Masiulis, 6 years ago

Resolution: needsinfo
Status: closednew

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

comment:10 by Tim Graham, 6 years ago

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.

comment:11 by Tim Graham, 6 years ago

Triage Stage: UnreviewedSomeday/Maybe

comment:12 by Andrew Badr, 6 years ago

comment:13 by Andrew Badr, 6 years ago

Triage Stage: Someday/MaybeAccepted

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

comment:14 by Daniel Hahler, 5 years ago

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.

in reply to:  12 comment:15 by Chris Spencer, 5 years ago

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.

comment:16 by Matthijs Kooijman, 4 years ago

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

comment:17 by Chris Jerdonek, 3 years ago

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


Last edited 3 years ago by Chris Jerdonek (previous) (diff)

comment:18 by Chris Jerdonek, 3 years ago

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.

comment:19 by Chris Jerdonek, 3 years ago

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).

comment:20 by Christophe Baldy, 17 months ago

Owner: changed from nobody to Christophe Baldy
Status: newassigned

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

comment:21 by Mariusz Felisiak, 17 months ago

Has patch: set

comment:22 by Mariusz Felisiak, 16 months ago

Patch needs improvement: set

comment:23 by Mariusz Felisiak, 16 months ago

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:24 by Mariusz Felisiak <felisiak.mariusz@…>, 16 months ago

Resolution: fixed
Status: assignedclosed

In 855f5a36:

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

Thanks Chris Jerdonek for the implementation idea.

Note: See TracTickets for help on using tickets.
Back to Top