sqlite3 database is locked

Question

i got database lock error when i have to delete the record from actions table.

there are two program that reading and writing on a sqlite3 database

one is a c program that write the results of hardware actions on a sqlite3 table and other is a python script that read the records from sqlite and process them and delete the rows after finished the job.

but the python script show database is locked error on delete the row..

db name : db.db

db table : TABLE 'actions' ( 'rid' INTEGER PRIMARY KEY AUTOINCREMENT, 'owner' INTEGER, 'action' TEXT, 'node' TEXT, 'value' TEXT

the python script:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
import time
import os.path
import requests
#import urllib.parse

#defines
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR+"/dbs/", "db.db")
wd_file_path = os.path.join(BASE_DIR, "wd")
pid = os.getpid()
conn = sqlite3.connect(db_path, isolation_level=None ,timeout=30000)
print ("Opened database successfully");
while True:
    res = conn.execute("select * from 'actions' where 'owner'='1';")
    #conn.commit()
    data=res.fetchone()
    print(data)
    if (data is None) :
        print('nothing @ '+str(time.time()))
        with open(wd_file_path, 'w') as file_:
            file_.write("{'pid'='"+str(pid)+"','time'='"+str(time.time())+"'}")
        time.sleep(0.5)
    else:
        #print(data)
        r = requests.post("http://127.0.0.1/json.php", data={'act': data[2], 'val': data[4]})
        #if (r.text == '1'):
        conn.execute("delete from 'actions' where 'rid'="+str(data[0])+";")
        conn.commit()
        #else:
        #   print(r.text)

as you can see i put isolation_level=None and timeout=30000 on my connection but i get database lock error many time

The script might be locking the database. Why are you wrapping the DB queries, file write and web post in a while loop that is actually infinite since there is no `break?` — Parfait, Aug 29 '15 at 00:21
why you open database out of the while loop? you myst open and close connection at the `while True:` — roozgar, Sep 02 '15 at 20:02

Parfait · Accepted Answer · 2015-08-29T16:36:37.523

3

Consider removing the infinite while True loop and use a connection cursor for execute and fetch statements:

conn = sqlite3.connect(db_path, isolation_level=None ,timeout=30000) 
print("Opened database successfully")

cur = conn.cursor()
cur.execute("select * from 'actions' where 'owner'='1';") 

for data in cur.fetchall() 
  print(data) 

  if (data is None): 
    print('nothing @ '+str(time.time())) 
    with open(wd_file_path, 'w') as file_: 
      file_.write("{'pid'='"+str(pid)+"','time'='"+str(time.time())+"'}") 
    time.sleep(0.5) 
  else: 
    #print(data) 
    r = requests.post("http://127.0.0.1/json.php", data={'act': data[2], 'val': data[4]}) 
    #if (r.text == '1'): 
    cur.execute("delete from 'actions' where 'rid'="+str(data[0])+";") 
    conn.commit() 
    #else: 
    # print(r.text)

cur.close()
conn.close()

edited Aug 29 '15 at 16:36

answered Aug 29 '15 at 13:42

Parfait

104,375
17
94
125

1

Thank you for your answer.but `while True` will help me to monitor database for new records every time, and i must check database every second to find any new record and handle them to my web service... so if i remove it i must find other loop to check database after 1 or 0.5 seconds.... – peiman F. Aug 29 '15 at 13:49
2

Then, consider using the cur.fetchall() loop. See edit. – Parfait Aug 29 '15 at 16:39
ok. after a cycle and fetch all record script will die!? how can i get other records after this? – peiman F. Aug 29 '15 at 19:27
Re-run the script to get other records. Remember ALL records currently in query will be fetched at script's runtime. There will be no OTHER records. In fact, you don't have to run it every second. Just grab records in batches periodically (i.e. hourly/daily). So, have your scheduler run the script at the specified time you need it. Having a script run indefinitely is taxing on cpu resources, memory, and database engine. – Parfait Aug 29 '15 at 19:38
ok.got the point.but this is a hardware controller so i need to get commands from/to hardware any seconds so what is your idea if i put all codes (from database connect to last commit) in a `while True` loop?! – peiman F. Aug 29 '15 at 20:45
Try wrapping all including cursor open and close within [iterative threading](http://stackoverflow.com/questions/3393612/run-certain-code-every-n-seconds) instead of indefinite `while True` loop to avoid hogging CPU. – Parfait Aug 29 '15 at 20:59

score 1 · Answer 2 · answered Aug 29 '15 at 04:08

The if (data is None) : branch doesn't commit, so the Python process indefinitely holds a read lock on the database. Add conn.commit() to that branch (or move the one in the else branch out of the if/else).

I also suggest not using SQLite as a queue like this. Consider a better tool, such as named pipes if you're on Linux.

score 0 · Answer 3 · answered Jun 08 '20 at 07:39

0

while using flask i just set app.run(debug=False) and it worked for me..

Do keep one backup of your database
delete your journal log file
replace the locked database with the backed up one

answered Jun 08 '20 at 07:39

Shubham Pandey

1

sqlite3 database is locked

3 Answers3