OperationalError: database is locked

Python Programming

Question or problem about Python programming:

I have made some repetitive operations in my application (testing it), and suddenly I’m getting a weird error:

OperationalError: database is locked

I’ve restarted the server, but the error persists. What can it be all about?

How to solve the problem:

Solution 1:

From django doc:

SQLite is meant to be a lightweight
database, and thus can’t support a
high level of concurrency.
OperationalError: database is locked
errors indicate that your application
is experiencing more concurrency than
sqlite can handle in default
configuration. This error means that
one thread or process has an exclusive
lock on the database connection and
another thread timed out waiting for
the lock the be released.
Python’s SQLite wrapper has a default
timeout value that determines how long
the second thread is allowed to wait
on the lock before it times out and
raises the OperationalError: database
is locked error.
If you’re getting this error, you can
solve it by:

Switching to another database backend. At a certain point SQLite becomes too “lite” for real-world applications, and these sorts of concurrency errors indicate you’ve reached that point.
Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
Increase the default timeout value by setting the timeout database option


Solution 2:

In my case, It was because I open the database from SQLite Browser. When I close it from the browser, the problem is gone.

Solution 3:

The practical reason for this is often that the python or django shells have opened a request to the DB and it wasn’t closed properly; killing your terminal access often frees it up. I had this error on running command line tests today.

Edit: I get periodic upvotes on this. If you’d like to kill access without rebooting the terminal, then from commandline you can do:

from django import db db.connections.close_all() 

Solution 4:

I disagree with @Patrick’s answer which, by quoting this doc, implicitly links OP’s problem (Database is locked) to this:

Switching to another database backend. At a certain point SQLite becomes too “lite” for real-world applications, and these sorts of concurrency errors indicate you’ve reached that point.

This is a bit “too easy” to incriminate SQlite for this problem (which is very powerful when correctly used; it’s not only a toy for small databases, fun fact: An SQLite database is limited in size to 140 terabytes).

Unless you have a very busy server with thousands of connections at the same second, the reason for this Database is locked error is probably more a bad use of the API, than a problem inherent to SQlite which would be “too light”. Here are more informations about Implementation Limits for SQLite.

Now the solution:

I had the same problem when I was using two scripts using the same database at the same time:

  • one was accessing the DB with write operations
  • the other was accessing the DB in read-only

Solution: always do cursor.close() as soon as possible after having done a (even read-only) query.

Here are more details.

Solution 5:

As others have told, there is another process that is using the SQLite file and has not closed the connection. In case you are using Linux, you can see which processes are using the file (for example db.sqlite3) using the fuser command as follows:

$ sudo fuser -v db.sqlite3 USER PID ACCESS COMMAND /path/to/db.sqlite3: user 955 F.... apache2 

If you want to stop the processes to release the lock, use fuser -k which sends the KILL signal to all processes accessing the file:

sudo fuser -k db.sqlite3 

Note that this is dangerous as it might stop the web server process in a production server.

Thanks to @cz-game for pointing out fuser!

Solution 6:

I encountered this error message in a situation that is not (clearly) addressed by the help info linked in patrick’s answer.

When I used transaction.atomic() to wrap a call to FooModel.objects.get_or_create() and called that code simultaneously from two different threads, only one thread would succeed, while the other would get the “database is locked” error. Changing the timeout database option had no effect on the behavior.

I think this is due to the fact that sqlite cannot handle multiple simultaneous writers, so the application must serialize writes on their own.

I solved the problem by using a threading.RLock object instead of transaction.atomic() when my Django app is running with a sqlite backend. That’s not entirely equivalent, so you may need to do something else in your application.

Here’s my code that runs FooModel.objects.get_or_create simultaneously from two different threads, in case it is helpful:

from concurrent.futures import ThreadPoolExecutor import configurations configurations.setup() from django.db import transaction from submissions.models import ExerciseCollectionSubmission def makeSubmission(user_id): try: with transaction.atomic(): e, _ = ExerciseCollectionSubmission.objects.get_or_create( student_id=user_id, exercise_collection_id=172) except Exception as e: return f'failed: {e}' e.delete() return 'success' futures = [] with ThreadPoolExecutor(max_workers=2) as executor: futures.append(executor.submit(makeSubmission, 296)) futures.append(executor.submit(makeSubmission, 297)) for future in futures: print(future.result()) 

Solution 7:

This also could happen if you are connected to your sqlite db via dbbrowser plugin through pycharm. Disconnection will solve the problem

Solution 8:

For me it gets resolved once I closed the django shell which was opened using python manage.py shell

Solution 9:

I’ve got the same error! One of the reasons was the DB connection was not closed.
Therefore, check for unclosed DB connections. Also, check if you have committed the DB before closing the connection.

Solution 10:

I had a similar error, right after the first instantiation of Django (v3.0.3). All recommendations here did not work apart from:

  • deleted the db.sqlite3 file and lose the data there, if any,
  • python manage.py makemigrations
  • python manage.py migrate

Btw, if you want to just test PostgreSQL:

docker run --rm --name django-postgres \ -e POSTGRES_PASSWORD=mypassword \ -e PGPORT=5432 \ -e POSTGRES_DB=myproject \ -p 5432:5432 \ postgres:9.6.17-alpine 

Change the settings.py to add this DATABASES:

DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'myproject', 'USER': 'postgres', 'PASSWORD': 'mypassword', 'HOST': 'localhost', 'PORT': '5432', } } 

…and add database adapter:

pip install psycopg2-binary 

Then the usual:

python manage.py makemigrations python manage.py migrate 

Hope this helps!