SQLite3 database is Locked in Azure

Question

I have a Flask server Running on Azure provided by Azure App services with sqlite3 as a database. I am unable to update sqlite3 as it is showing that database is locked

    2018-11-09T13:21:53.854367947Z [2018-11-09 13:21:53,835] ERROR in app: Exception on /borrow [POST]
    2018-11-09T13:21:53.854407246Z Traceback (most recent call last):
    2018-11-09T13:21:53.854413046Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/app.py", line 2292, in wsgi_app
    2018-11-09T13:21:53.854417846Z     response = self.full_dispatch_request()
    2018-11-09T13:21:53.854422246Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/app.py", line 1815, in full_dispatch_request
    2018-11-09T13:21:53.854427146Z     rv = self.handle_user_exception(e)
    2018-11-09T13:21:53.854431646Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/app.py", line 1718, in handle_user_exception
    2018-11-09T13:21:53.854436146Z     reraise(exc_type, exc_value, tb)
    2018-11-09T13:21:53.854440346Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/_compat.py", line 35, in reraise
    2018-11-09T13:21:53.854444746Z     raise value
    2018-11-09T13:21:53.854448846Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/app.py", line 1813, in full_dispatch_request
    2018-11-09T13:21:53.854453246Z     rv = self.dispatch_request()
    2018-11-09T13:21:53.854457546Z   File "/home/site/wwwroot/antenv/lib/python3.7/site-packages/flask/app.py", line 1799, in dispatch_request
    2018-11-09T13:21:53.854461846Z     return self.view_functions[rule.endpoint](**req.view_args)
    2018-11-09T13:21:53.854466046Z   File "/home/site/wwwroot/application.py", line 282, in borrow
    2018-11-09T13:21:53.854480146Z     cursor.execute("UPDATE books SET stock = stock - 1 WHERE bookid = ?",(bookid,))
    2018-11-09T13:21:53.854963942Z sqlite3.OperationalError: database is locked

Here is the route -

@app.route('/borrow',methods=["POST"])
def borrow():
    # import pdb; pdb.set_trace()
    body = request.get_json()
    user_id = body["userid"]
    bookid = body["bookid"]
    conn = sqlite3.connect("database.db")
    cursor = conn.cursor()
    date = datetime.now()
    expiry_date = date + timedelta(days=30)
    cursor.execute("UPDATE books SET stock = stock - 1 WHERE bookid = ?",(bookid,))
    # conn.commit()
    cursor.execute("INSERT INTO borrowed (issuedate,returndate,memberid,bookid) VALUES (?,?,?,?)",("xxx","xxx",user_id,bookid,))
    conn.commit()
    cursor.close()
    conn.close()

    return json.dumps({"status":200,"conn":"working with datess update"})

I tried checking the database integrity using pragma. There was no integrity loss. So I don't know what might be causing that error. Any help is Appreciated :)

Answer 1

I got it by setting up the azure mount options with the following configuration:

dir_mode=0777,file_mode=0777,uid=0,gid=0,mfsymlinks,nobrl,cache=strict

But the real solution is to add the flag nobrl (Byte-Range Lock).

Add storageclass example for kubernetes:

---
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: azureclass
provisioner: kubernetes.io/azure-file
mountOptions:
  - dir_mode=0777
  - file_mode=0777
  - uid=0
  - gid=0
  - mfsymlinks
  - nobrl
  - cache=strict
parameters:
  skuName: Standard_LRS