Python SQLite: database is locked

Question or problem about Python programming:

I’m trying this code:

import sqlite

connection = sqlite.connect('cache.db')
cur = connection.cursor()
cur.execute('''create table item
  (id integer primary key, itemno text unique,
        scancode text, descr text, price real)''')

connection.commit()
cur.close()

I’m catching this exception:

Traceback (most recent call last):
  File "cache_storage.py", line 7, in 
    scancode text, descr text, price real)''')
  File "/usr/lib/python2.6/dist-packages/sqlite/main.py", line 237, in execute
    self.con._begin()
  File "/usr/lib/python2.6/dist-packages/sqlite/main.py", line 503, in _begin
    self.db.execute("BEGIN")
_sqlite.OperationalError: database is locked

Permissions for cache.db are ok. Any ideas?

How to solve the problem:

Solution 1:

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"

Failing that, read Things That Can Go Wrong and How to Corrupt Your Database Files

Solution 2:

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

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

Solution 3:

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

Solution 4:

The reason mine was showing the “Lock” message was actually due to me having opened an SQLite3 IDE on my mac and that was the reason it was locked. I assume I was playing around with the DB within the IDE and hadn’t saved the changes and therefor a lock was placed.

Cut long story short, check that there are no unsaved changes on the db and also that it is not being used elsewhere.

Solution 5:

Turned out the problem happened because the path to the db file was actually a samba mounted dir. I moved it and that started working.

Solution 6:

In Linux you can do something similar, for example, if your locked file is development.db:

$ fuser development.db
This command will show what process is locking the file:

development.db: 5430
Just kill the process…

kill -9 5430
…And your database will be unlocked.

Solution 7:

Because this is still the top Google hit for this problem, let me add a possible cause. If you’re editing your database structure and haven’t committed the changes, the database is locked until you commit or revert.

(Probably uncommon, but I’m developing an app so the code and database are both being developed at the same time)

Solution 8:

The database is locked by another process that is writing to it. You have to wait until the other transaction is committed. See the documentation of connect()

Solution 9:

Here’s a neat workaround for simultaneous access:

while True: connection = sqlite3.connect('user.db', timeout=1) cursor = connection.cursor() try: cursor.execute("SELECT * FROM queue;") result = cursor.fetchall() except sqlite3.OperationalError: print("database locked") num_users = len(result) # ... 

Solution 10:

You should check out if there is no DBMS administration and development platform working on your database (like pgAdmin), as this is probably the most popular cause of this error. If there is – commit the changes done and the problem is gone.

Hope this helps!