Перейти к содержимому

Database is locked sqlite3 python почему

  • автор:

Database is locked sqlite3 python почему

Last updated: Apr 11, 2024
Reading time · 5 min

banner

# Table of Contents

  1. OperationalError: database is locked Python SQLite
  2. Try to increase the timeout
  3. Make sure you haven’t opened your SQLite database in SQLite Browser or another GUI
  4. Having multiple scripts that use the same SQLite database at the same time
  5. Close all your Python or Django connections to the SQLite database
  6. Reduce concurrency in your code
  7. Try to stop all Python processes
  8. Using the fuser command to stop the SQLite process on Linux
  9. If none of the suggestions helped, consider switching to a production-ready database

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

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

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.

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.

  1. Issue the python command to start the Python interpreter (from your project’s root directory).
Copied!
python # or with python3 python3
  1. Paste the following 2 lines in the Python interpreter.
Copied!
from django import db db.connections.close_all()

close all database connections to sqlite

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:

  1. Try to rewrite your code to reduce concurrency.
  2. 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.

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.

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:

  • Convert a string to a Class object in Python
  • ImportError: cannot import name ‘url’ from django.conf.urls
  • ModuleNotFoundError: No module named ‘django’ in Python
  • You must either define the environment variable DJANGO_SETTINGS_MODULE or call settings.configure() before accessing settings
  • How to restart a Python Script

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

How to Unlock the SQLite Database

How to Unlock the SQLite Database

  1. Unlock the SQLite Database in Windows
  2. 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 :
$ fuser demo.db 
The below command will show the process that is causing the file to get locked:
> demo.db: 5430 
The final step lies in killing the process is:
kill -9 5430 

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.

Related Article — Python SQLite

  • How to Insert Data Into an SQLite Database Using Python
  • How to Fix Sqlite3.OperationalError: Unable to Open Database File
  • How to Show Tables in SQLite
  • How to Install SQLite in Python

How do I prevent SQLite database locks?

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

So, as far as I understand I can: 1) Read db from multiple threads ( SELECT ) 2) Read db from multiple threads ( SELECT ) and write from single thread ( CREATE , INSERT , DELETE ) But, I read about Write-Ahead Logging that provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. Finally, I’ve got completely muddled when I found it, when specified:

  • Trying to CREATE or DROP a table or index while a SELECT statement is still pending.
  • Trying to write to a table while a SELECT is active on that same table.
  • Trying to do two SELECT on the same table at the same time in a multithread application, if sqlite is not set to do so.
  • fcntl(3,F_SETLK call on DB file fails. This could be caused by an NFS locking issue, for example. One solution for this issue, is to mv the DB away, and copy it back so that it has a new Inode value

So, I would like to clarify for myself, it is necessary to avoid the lock? Can I read and write at the same time from two different threads? Thanks.

Python SQLite: database is locked

I’m presuming you are actually using sqlite3 even though your code says otherwise. Here are some things to check:

  1. That you don’t have a hung process sitting on the file (unix: $ fuser cache.db should say nothing)
  2. There isn’t a cache.db-journal file in the directory with cache.db; this would indicate a crashed session that hasn’t been cleaned up properly.
  3. Ask the database shell to check itself: $ sqlite3 cache.db «pragma integrity_check;»
  4. Backup the database $ sqlite3 cache.db «.backup cache.db.bak»
  5. Remove cache.db as you probably have nothing in it (if you are just learning) and try your code again
  6. See if the backup works $ sqlite3 cache.db.bak «.schema»

answered Apr 29, 2010 at 21:48
43.3k 9 9 gold badges 90 90 silver badges 115 115 bronze badges

I’m letting this answer stand as it is generally useful, but my other answer is probably the correct one.

Commented Apr 29, 2010 at 22:04
Even better: add your other answer as the 7th thing to check 😉
Commented Apr 29, 2010 at 23:33

Thanks for your response. I have no any data in this database (cache.db is 0 byte size) so it’s not necessary to backup it. 1) fuser doesn’t output anything 2) no db-journal file before starting 3) sqlite3 cache.db «pragma integrity_check;» says ok 5) I tried to remove and rename cache.db file many times 😉 Now I’ve tested it on another machine but on the same OS Ubuntu 9.10 server edition and I’ve got the same result. This error happens when I install python-sqlite package.

Commented Apr 30, 2010 at 9:03

Set the timeout parameter in your connect call, as in:

connection = sqlite.connect('cache.db', timeout=10) 

answered Dec 23, 2011 at 16:26
Anthony DeRosa Anthony DeRosa
957 8 8 silver badges 6 6 bronze badges
Looks like the default is 5 seconds, per docs.python.org/2/library/sqlite3.html#sqlite3.connect
Commented May 24, 2017 at 22:33

When your call to connect fails with the «database is locked» error message, it’s because another connection is already accessing the database. By specifying a timeout (timeout=10 in this case), you’re giving the other thread time to complete its transaction and close the connection, and then your connection is able to proceed. Without the timeout, the attempt to connect immediately fails.

Commented Apr 7, 2018 at 23:50

I know this is old, but I’m still getting the problem and this is the first link on Google for it. OP said his issue was that the .db was sitting on a SMB share, which was exactly my situation. My ten minutes’ research indicates that this is a known conflict between sqlite3 and smb; I’ve found bug reports going back to 2007.

I resolved it by adding the «nobrl» option to my smb mount line in /etc/fstab, so that line now looks like this:

//SERVER/share /mnt/point cifs credentials=/path/to/.creds,sec=ntlm,nobrl 0 0 

This option prevents your SMB client from sending byte range locks to the server. I’m not too up on my SMB protocol details, but I best I can tell this setting would mostly be of concern in a multi-user environment, where somebody else might be trying to write to the same db as you. For a home setup, at least, I think it’s safe enough.

My relevant versions:

  • Mint 17.1 Rebecca
  • SMB v4.1.6-Ubuntu
  • Python v3.4.0
  • SQLite v3.8.2
  • Network share is hosted on a Win12R2 server

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *