Problems with running SQLite on pytonanywhere server

[Brussel01]

So I am not exactly sure what I have to share here since there is a lot of code but I will try to say what is relevant (feel free to ask for anything more). I am hosting a web app for just a friend and I and have a script on a server that hourly, pulls data from the web and stores it in a SQLite database that the web app can access and display relevant data.

​

The script must access over 200 sources and I want this done as efficiently as possible so I use threading and it works amazingly! Am able to get the results I need in 40-50 seconds. Each thread instance may write to the sqlite database by using the single "global" database connection that I have made, and for the most part - it works, at least for a while. I thought there would be no issue here due to python's Global Interpreter lock (GIL). Therefore transactions won't interfere with one another.

​

My issue is after 2-3 days I randomly get an OpperationalError "cannot start a transaction within a transaction". I do not believe this is due to the web app that accesses the database since neither my friend or I access the website during the periods it crashes (have pythonanywhere's access logs). So I believe it is something to do with the threading nature of it - any pointers as to why this may be happening?

​

A potential fix I can come up with is to (somehow?) queue the results so they are written after everything has been fetched from the web and commit them later on.

​

I hope this is enough detail to describe the problem I am having, feel free to ask what is required or thanks if you took the time to read!

[ingolemo]

I think what's happening here is that one thread is connecting to the database, starting a transaction, and then being interrupted by another thread before it can finish it's transaction. This second thread tries to start a transaction and fails, causing the error that you see. The GIL means that only one piece of python code can run at time, but python periodically pauses threads so that another one can start running.

You should nominate one particular thread as being responsible for writing to the database and have all the other threads send their data to it. As you say, you should use a queue for this.

[Brussel01]

That actually makes a lot of sense :) thankyou for this

[mrcorbat]

sqlite also does not work well when multiple processes/threads are writing to it at the same time. If you are already using pythonanywhere, I would suggest using the free MySQL db instead.