Sqlite database is locked python

Last updated: Jun 16, 2023
Reading time · 5 min

banner

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

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

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.

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.

Copied!
from sqlalchemy import create_engine engine = create_engine( r"sqlite:///C:\path\to\foo.db", connect_args="timeout": 30>, )

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

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.

Copied!
python # or with python3 python3

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.

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.

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.

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.

Источник

Unlock the SQLite Database

Unlock the SQLite Database in Windows
Unlock the SQLite Database in macOS and Ubuntu

The following tutorial will teach us how to resolve the error database is locked in SQLite. Often, this issue is faced when the system has intentionally or unintentionally locked a database.

Unlock the SQLite Database in Windows

In Windows, the simplest thing is finding the process that is handling the database file. We can use this program to determine the process.

After closing the process, the database would be unlocked.

Unlock the SQLite Database in macOS and Ubuntu

We will do a similar thing mentioned above in these systems as well. Just the procedure of finding the process handling the database might be different.

To find it out, we must perform the following operations:

Suppose our DB file is demo.db :

The below command will show the process that is causing the file to get locked:

The final step lies in killing the process is:

Doing the above steps will unlock the DB file for us. Hence, we can counter the error database is locked in SQLite on various operating systems, including macOS, Windows, and Ubuntu.

Preet writes his thoughts about programming in a simplified manner to help others learn better. With thorough research, his articles offer descriptive and easy to understand solutions.

Источник

Sqlite database is locked python

OperationalError: база данных заблокирована

9 ответов