Python SQLite: database is locked

Question

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 <module>
    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?

Answer 1

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)

Answer 2

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

Answer 3

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.

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

Answer 5

One possible reason for the database being locked that I ran into with SQLite is when I tried to access a row that was being written by one app, and read by another at the same time. You may want to set a busy timeout in your SQLite wrapper that will spin and wait for the database to become free (in the original c++ api the function is sqlite3_busy_timeout). I found that 300ms was sufficient in most cases.

But I doubt this is the problem, based on your post. Try other recommendations first.

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