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?

score 62 · Answer 1 · answered Apr 29 '10 at 21:48

62

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

That you don't have a hung process sitting on the file (unix: $ fuser cache.db should say nothing)
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.
Ask the database shell to check itself: $ sqlite3 cache.db "pragma integrity_check;"
Backup the database $ sqlite3 cache.db ".backup cache.db.bak"
Remove cache.db as you probably have nothing in it (if you are just learning) and try your code again
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

answered Apr 29 '10 at 21:48

msw

42,753
9
87
112

I'm letting this answer stand as it is generally useful, but my other answer is probably the correct one. – msw Apr 29 '10 at 22:04
1

Even better: add your other answer as the 7th thing to check ;) – tzot Apr 29 '10 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. – Soid Apr 30 '10 at 09:03

score 58 · Answer 2 · answered Dec 23 '11 at 16:26

58

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

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

answered Dec 23 '11 at 16:26

Anthony DeRosa

897
7
6

4

Looks like the default is 5 seconds, per https://docs.python.org/2/library/sqlite3.html#sqlite3.connect – storm_m2138 May 24 '17 at 22:33
3

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. – Anthony DeRosa Apr 07 '18 at 23:50

score 25 · Answer 3 · answered May 17 '15 at 07:08

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

I have the same Issue that it does not work on an SMB Share. I tried youre solution with adding nobrl but still the same error. Localy it works fine. — Sandro4912, Aug 05 '20 at 08:49

Erion S · Answer 4 · 2017-03-28T20:22:29.353

13

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.

edited Mar 28 '17 at 20:22

answered Oct 12 '15 at 22:11

Erion S

1,603
18
24

Soid · Accepted Answer · 2015-05-01T22:05:43.950

11

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.

edited May 01 '15 at 22:05

answered Sep 10 '12 at 18:51

Soid

2,585
1
30
42

score 11 · Answer 6 · edited Jan 10 '23 at 05:14

11

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

$ fuser development.db

This command will show which process is locking the file:

development.db: 5430

Just kill the process...

kill -9 5430

...And your database will be unlocked.

edited Jan 10 '23 at 05:14

Michael Ruth

2,938
1
20
27

answered Jul 15 '15 at 08:36

arunjos007

4,105
1
28
43

score 5 · Answer 7 · edited Aug 05 '16 at 21:25

5

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

edited Aug 05 '16 at 21:25

RegularlyScheduledProgramming

1,347
1
11
27

answered Jun 15 '10 at 14:24

Jithin

77
5

2

Did you forget a `continue`? – remram Jul 02 '18 at 03:11
14

Give him a `break` – Eric Mar 22 '19 at 09:42
btw `timeout=1` is superflous: the default timeout of 5s is likely enough in most cases. – Eric Mar 22 '19 at 09:44

score 5 · Answer 8 · answered Dec 09 '16 at 22:20

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)

score 4 · Answer 9 · answered Apr 29 '10 at 21:51

4

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

answered Apr 29 '10 at 21:51

Florian Diesch

1,025
10
16

score 3 · Answer 10 · answered Apr 29 '10 at 21:52

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.

score 3 · Answer 11 · answered Mar 28 '18 at 09:58

3

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.

answered Mar 28 '18 at 09:58

PyFox

383
3
11

score 2 · Answer 12 · answered Sep 20 '16 at 23:11

I had the same problem: sqlite3.IntegrityError

As mentioned in many answers, the problem is that a connection has not been properly closed.

In my case I had try except blocks. I was accessing the database in the try block and when an exception was raised I wanted to do something else in the except block.

try:
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('''INSERT INTO ...''')
except:
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    cur.execute('''DELETE FROM ...''')
    cur.execute('''INSERT INTO ...''')

However, when the exception was being raised the connection from the try block had not been closed.

I solved it using with statements inside the blocks.

try:
    with sqlite3.connect(path) as conn:
        cur = conn.cursor()
        cur.execute('''INSERT INTO ...''')
except:
    with sqlite3.connect(path) as conn:
        cur = conn.cursor()
        cur.execute('''DELETE FROM ...''')
        cur.execute('''INSERT INTO ...''')

score 2 · Answer 13 · edited Jan 17 '17 at 13:51

2

Your cache.db is being currently used by another process.
Stop that process and try again, it should work.

edited Jan 17 '17 at 13:51

Bradley Wilson

1,197
1
13
26

answered Jan 17 '17 at 12:57

Avinash Jeeva

512
5
10

score 2 · Answer 14 · answered Apr 30 '21 at 17:46

2

in my case, the 'locked' message was happening due to the unsaved changes that I have done in DB BROWSER(SQL LITE), I had to save them then when I executed my script again, the problem was solved, hope this helps someone like in my case.

answered Apr 30 '21 at 17:46

Lcool

321
2
5

score 1 · Answer 15 · answered Apr 29 '10 at 22:03

1

Oh, your traceback gave it away: you have a version conflict. You have installed some old version of sqlite in your local dist-packages directory when you already have sqlite3 included in your python2.6 distribution and don't need and probably can't use the old sqlite version. First try:

$ python -c "import sqlite3"

and if that doesn't give you an error, uninstall your dist-package:

easy_install -mxN sqlite

and then import sqlite3 in your code instead and have fun.

answered Apr 29 '10 at 22:03

msw

42,753
9
87
112

I checked to use sqlite3 and it works different. It creates db-journal file and waits. Then "database is locked" again while sqlite without "3" doesn't wait for anything. – Soid Apr 30 '10 at 09:08

score 1 · Answer 16 · edited Sep 07 '18 at 08:28

I had this problem while working with Pycharm and with a database that was originally given to me by another user.

So, this is how I solve it in my case:

Closed all tabs in Pycharm that operate with the problematic database.
Stop all running processes from the red square botton in the top right corner of Pycharm.
Delete the problematic database from the directory.
Upload again the original database. And it worked again.

score 1 · Answer 17 · answered Jan 14 '22 at 17:32

1

Just another possibility that happened to me, I was opening the database twice, the first opening blocked the second. Check that you are not doing that.

answered Jan 14 '22 at 17:32

Julen

319
2
9

score 0 · Answer 18 · answered Feb 10 '18 at 13:00

0

I also had this problem. I was trying to enter data into the database without saving changes I had made in it. after i saved the changes worked

answered Feb 10 '18 at 13:00

Adriano Oliveira

1
1

2

Use comment for this type of short answers. If you are answering anything be explanatory, add more information, site examples and provide references. – Munim Munna Feb 10 '18 at 13:12

score 0 · Answer 19 · answered Dec 12 '19 at 23:46

in my case ,the error happened when a lot of concurrent process trying to read/write to the same table. I used retry to workaround the issue

def _retry_if_exception(exception):
    return isinstance(exception, Exception)

@retry(retry_on_exception=_retry_if_exception,
       wait_random_min=1000,
       wait_random_max=5000,
       stop_max_attempt_number=5)
def execute(cmd, commit=True):
   c.execute(cmd)
   c.conn.commit()

score 0 · Answer 20 · answered May 12 '20 at 07:57

Even when I just had one writer and one reader, my issue was that one of the reads was taking too long: longer than the stipulated timeout of 5 seconds. So the writer timed out and caused the error.

So, be careful when reading all entries from a database especially from one which the size of the table grows over time.

score 0 · Answer 21 · edited Mar 08 '21 at 13:15

I found this worked for my needs (thread locking):

conn = sqlite3.connect(database, timeout=10)

Docs

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements, uri])

When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception. The default for the timeout parameter is 5.0 (five seconds).

score 0 · Answer 22 · answered Oct 23 '20 at 12:30

Easy solution: check once if you have opened the database in another window or in another terminal. That also locks your database. In my case, I closed all the other terminals that were locking the database (a terminal tab in Pycharm). Check each tab of the terminals of your IDE as well if there is a terminal that left the database open. exit() all the terminals should work unlocking the database.

score 0 · Answer 23 · answered May 27 '22 at 06:41

0

I am using sqlite3,I solve the block problem by adding the transaction isolation level conn= sqlite3.connect('image.db',isolation_level=None,timeout=0.01);

answered May 27 '22 at 06:41

zhoupan0401

1
1

Python SQLite: database is locked

23 Answers23

Linked