0

I have written a script that executes inside docker and writes to an sqlite3 db on host. At a time, there are 8-10 such docker containers writing to the same database (each having their own connection).

In my script, I create an sqlite connection and get a cursor and use it for the lifecycle of the whole program (multiple cur.execute(), con.commit(), con.rollback()). I initiate the connection with 20 second timeout:

con = sqlite3.connect('database.db', timeout=20)

but I still get sqlite3: database is locked. errors. I have tried this solution like this:

while True:
    try:
        cur.execute(f"...insert into db statement...")
            break
    except sqlite3.OperationalError:
            continue

and similarly for con.commit() and con.rollback() in place of cur.execute().

My question is, is there a better way to do this? I am not checking if the while loop for any of the containers is stuck in infinite loop. I have seen answers creating connection and cursor inside and cursor inside the while loop, does creating a connection lock the database? does cur.execute() lock it or only con.commit()?

SajanGohil
  • 960
  • 13
  • 26
  • From the SQLite docs: "A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network." In short, it looks like there isn't a better way. Look instead for a DBMS that is designed to be multi-user. – BoarGules Jul 03 '22 at 07:51
  • Would you suggest getting multiple databases? I already have 10 different dbs and split my workflow in 10 parts, i have had socket connection closed issues with mysql (even after increasing max connection limit), (that was wikipedia's software). I want to process ~3 mil tasks as fast as possible, hence i chose sqlite as there is no sockets or server setup. – SajanGohil Jul 03 '22 at 08:19
  • Without knowing a good deal more about your project I would not venture a suggestion. I was just pointing out that the SQLite documentation explains at great length and in a lot of detail why multiple connections are not what is designed for. The simplicity of not having to set up a server comes at a price: without a server, client-server functionality is unavailable or, at best, emulated. – BoarGules Jul 03 '22 at 08:25

0 Answers0