'SQLite3 database is Locked in Azure

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

sqlazuresqlite


Solution 1:[1]

I use Azure app service on Docker on Linux, and have the same issue. If you are using Azure app service on Windows, the problem is different from mine.

The problem is that /home is mounted as CIFS filesystem which can not deal with SQLite3 lock.

My workaround is to copy db.sqlite3 file to some directory other than /home, and properly set permissions and ownerships of the db.sqlite3 file and its directory as well. Then, let my project read/write it. However, this workaround is pretty awkward. I don't recommned.

Solution 2:[2]

Presumably this solution is not safe for production workloads but at least I got it working by executing the following command:

sqlite3 <database-file> 'PRAGMA journal_mode=wal;'

After running the above command, my database stored on an Azure File share works inside a container Web App.

Solution 3:[3]

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

Solution 4:[4]

This answer appears toward the top of a typical Google search for this issue so I thought I'd add a couple of additional tips:

For those running JavaScript and using Sequelize as the interface to your SQLite DB, running

await sequelize.query('PRAGMA journal_mode=WAL;')

prior to creating your database will allow you to read/write the DB file in an Azure web app running under a Linux service plan. I have a separate script that creates one via a call to sequelize.sync(). I'm storing the DB file in a separate directory under /home within the file system for the Linux container. It seems to run fine and my workload is expected to be very light. Note that you don't need to set the journal mode again when your app starts and you try to connect to the database, that mode will be set in the file itself (this wasn't obvious from the SQLite docs).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Philipp Haider
Solution 3 hakre
Solution 4 Chris Tybur

Related Questions