CODEWITHSUNDEEP
pythonsqlite
peiman F.
peiman F.

Reputation: 1658

sqlite3 database is locked

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

Upvotes: 6

Views: 9234

Answers (3)

Shubham Pandey
Shubham Pandey

Reputation: 1

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

Upvotes: 0

Parfait
Parfait

Reputation: 107747

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

Upvotes: 3

Colonel Thirty Two
Colonel Thirty Two

Reputation: 26609

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.

Upvotes: 1

Related Questions