Select and update in same transaction with python sqlite

Question

I would like to do one transaction with a select and an update query on the same sqlite3 database with Python, to make sure no other thread can make a select while I have not done the update query with the first thread.

The base is very simple, kind of a queue of jobs to do, and I want to make sure multiple thread can't get the same job id.

with sqlite3.connect('database.sqlite') as db:
    db_cursor = db.cursor()
    db_cursor.execute("SELECT id FROM mytable WHERE status=?", 'todo')
    myrow = db_cursor.fetchone()
    if myrow :
        id = myrow[0]
        db_cursor.execute("UPDATE mytable SET status=? WHERE id=?", ['done', id])
        # id is used after that.

Would the isolation-level parameter be a solution? Does the isolation work while I have released the connection, or only up to the "fetchone" function?

Thanks.

Answer 1

You could certainly use locking or transactions here, but you might not really need any of that.

Just make sure the job is still available when you take it:

with sqlite3.connect('database.sqlite') as db:
    while 1:
        db_cursor = db.cursor()
        db_cursor.execute("SELECT id FROM mytable WHERE status=?", 'todo')
        # ^^^^^^^ Consider adding LIMIT 1 here, you don't need all rows if you only
        # use one.

        myrow = db_cursor.fetchone()
        if not myrow :
            break

        id, = myrow
        accepted = db_cursor.execute("UPDATE mytable SET status=? WHERE id=? AND status=?", ['done', id, 'todo']) 
        # ^^^^^^ This will return the number of rows updated. 
        # Note that we only update if the status is still 'todo', so if we get 1 updated 
        # row, we're sure no one else took our job. This works because UPDATE is atomic.

        # A performance improvement would be to select multiple rows above, 
        # and try another one (maybe at random) if you didn't get your "first pick"

        if not accepted: 
            # Whoops this job was taken! Try again and get another one
            continue

        # This job is yours, do your thing!

Note that this might not perform very well under high contention. As usual: try a simple solution first, iterate once you've identified bottlenecks (in your case: iterating means using an actual task broker).