OperationalError: database is locked Python SQLite [Solved]

# Table of Contents

# OperationalError: database is locked Python SQLite [Solved]

The "OperationalError: database is locked" error occurs when one thread or process has a lock on the database connection and another thread times out while waiting for the lock to be released.

The Python SQLite wrapper has a default timeout value that determines how long the second thread waits on the lock to be released before timing out and raising an OperationalError.

# Try to increase the timeout

The first thing you can try is to increase the timeout when connecting to the SQLite database.

For example, if you use the sqlite3 module, you can pass a timeout argument to the connect() method.

main.py

Copied!
import sqlite3

# 👇️ Set timeout to 30 seconds
con = sqlite3.connect("tutorial.db", timeout=30)

cur = con.cursor()
cur.execute("CREATE TABLE movie(title, year, score)")

con.close()

The code for this article is available on GitHub

The timeout argument determines how many seconds the connection should wait before raising an OperationalError when a table is locked.

If another connection opens a transaction to modify a table, the table is locked until the transaction is committed.

By default, the timeout is set to 5 seconds.

If you use SQL Alchemy, your connection code might look similar to the following.

main.py

Copied!
from sqlalchemy import create_engine

engine = create_engine(
    "sqlite:////absolute/path/to/foo.db",
    connect_args={"timeout": 30},
)

Or the following if you are on Windows.

main.py

Copied!
from sqlalchemy import create_engine

engine = create_engine(
    "sqlite:///C:\\path\\to\\foo.db",
    connect_args={"timeout": 30},
)

You can also use a raw string for the path on Windows.

main.py

Copied!
from sqlalchemy import create_engine

engine = create_engine(
    r"sqlite:///C:\path\to\foo.db",
    connect_args={"timeout": 30},
)

The code for this article is available on GitHub

# Make sure you haven't opened your SQLite database in SQLite Browser or another GUI

Another common cause of the error is opening your SQLite database in SQLite Browser, DB Browser or another graphical user interface that enables you to view your data.

Try to close your SQLite Browser (or DB Browser) application and restart your development server to see if the issue is resolved.

If you use an outside application to view your SQLite database, it might be locking the database and preventing you to connect.

Once you close the application, the connection will close, the lock will be released and you should be able to connect.

# Having multiple scripts that use the same SQLite database at the same time

The error also occurs if you have multiple scripts that access the same database at the same time.

For example, one script might be writing the database and the other might be reading from the database.

This might be causing the error because SQLite might not be able to handle the concurrent connections.

Something you can try to get around this is to call the cursor.close() method as soon as possible after your queries.

main.py

Copied!
# your query here
cursor.close()

The cursor.close method closes the cursor immediately (rather than when __del__ is called).

The cursor becomes unusable after cursor.close() is called.

A ProgrammingError exception is raised if you attempt to access the cursor after having called close().

If you use Django, it automatically calls cursor.close() for you unless you write raw SQL queries.

# Close all your Python or Django connections to the SQLite database

The error also occurs if you have issued an SQLite query from your terminal (e.g. the Python or Django interpreters).

Your terminal might have an open connection to the SQLite database that has not been closed properly.

You can try to close your terminal to see if the issue resolves.

If you use Django, you can also close all database connections directly from your terminal.

Issue the python command to start the Python interpreter (from your project's root directory).

shell

Copied!
python

# or with python3
python3

Paste the following 2 lines in the Python interpreter.

shell

Copied!
from django import db

db.connections.close_all()

Try to restart your development server after closing all database connections and check if the issue has been resolved.

# Reduce concurrency in your code

If you suspect that the issue is caused by your code:

Try to rewrite your code to reduce concurrency.
Ensure all database transactions are short-lived.

If a connection opens a transaction to modify a table, the table is locked until the transaction is committed.

Make sure you aren't opening transactions and not committing them due to some delay caused by inefficient code, an error or another issue.

For example, when using sqlite3(), you should call the con.commit() method to commit your transactions.

main.py

Copied!
con.commit()

The commit method commits any pending transaction to the database.

If there are no open transactions, this method is a no-op.

# Try to stop all Python processes

If the issue persists, try to stop all Python processes in Task Manager or System monitor (depending on your operating system).

Try to restart your development server and see if you can connect to the database after stopping all Python processes.

# Using the `fuser` command to stop the SQLite process on Linux

If you are on Linux, try using the fuser command to stop the SQLite processes.

Open your terminal in your project's root directory (next to your SQLite file) and run the following command.

shell

Copied!
sudo fuser -v foo.db

Make sure to replace foo.db with the name of your SQLite database file, e.g. app.db or db.sqlite3.

The command will print the running processes that are using the specified file (foo.db).

You can stop the processes to release the lock by using the -k parameter.

shell

Copied!
sudo fuser -k foo.db

Make sure to replace foo.db with the name of your SQLite database file, e.g. app.db.

# If none of the suggestions helped, consider switching to a production-ready database

If none of the suggestions helped, you should consider switching to a production-ready database such as Postgres or MySQL.

SQLite doesn't deal too well with high levels of concurrency.

# Additional Resources

You can learn more about the related topics by checking out the following tutorials:

I wrote a book in which I share everything I know about how to become a better, more efficient programmer.

You can use the search field on my Home Page to filter through all of my articles.

OperationalError: database is locked Python SQLite [Solved]

# Table of Contents