0

I am new to database programming and I am trying to understand how I can implement sqlite3 for my task.

Currently I have an Application which manages tasks from users and processes them on 3 remote Computers with a specific licence on the Network.

The users write to a Json file (from a GUI) called Job List and the remote computer reads this file and remove the first task that matches their criteria and place them into a separate JSON-File. Then it updates this file with messages while the task is being processed.

To avoid concurrency my workaround was to create a .lock file every time the remote computer tries to read the Job List or the user tries to write in the Job list.

My Implementation of this in sqlite3 is as follows:

import sqlite3

con = sqlite3.connect("JobList.db")
cur = con.cursor()
cur.execute("create table tasks
 (user text, path text, code int, msg text, report text, exe text, status int)")
con.commit()
con.close()

After the users add their jobs to this list, a script running on the 3 remote computers should connect every 10 seconds to this database and look for tasks to process.

The status value of 0 should imply the task is ready for grabs, 1 = is being processed, 2 = processed.

Let us say computer 1 finds a task to process

        con = sqlite3.connect(PREPROCESS,timeout=20)
        cur = con.cursor()
        sel = cur.execute('select * from tasks')
        
        if (fetch := sel.fetchone()):
            user, path, code, msg, report, exe, status = fetch
            cur.execute('update tasks set status=1')
            con.commit()
            con.close()
            dotheTask(path,exe)
        else:
            con.close()
            return

Since select Statements don't lock the database, it is likely that computer 2 will read the old value of status = 0 and try to update it to 1 as soon as the lock on the database is gone. This would result in both the computers doing the same job.

How can I avoid the computers to do the same task?

SharonShaji
  • 79
  • 6
  • possible dupe https://stackoverflow.com/questions/4060772/sqlite-concurrent-access ? – python_user Sep 01 '21 at 14:47
  • @python_user My question is basically how can I lock the execution of select statement? – SharonShaji Sep 01 '21 at 14:56
  • "Programs on multiple computers using the same database" is a scenario that calls for a traditional client/server database like postgresql or mariadb, not SQLite. – Shawn Sep 01 '21 at 15:09
  • 1
    Generally, the answer to this is to add some concept of a virtual lock in the database itself so workers can take "ownership" of a row or table while they're working on it. That said, [it's probably a bad idea](https://www.sqlite.org/useovernet.html) to have remote computers access a SQLite database. – Anon Coward Sep 01 '21 at 15:32
  • @AnonCoward Thanks for the link. What are the alternatives that you recommend? – SharonShaji Sep 02 '21 at 07:30
  • Some database that provides a server and is designed to be accessed over the network like mysql or postgresql. – Anon Coward Sep 02 '21 at 15:19

0 Answers0