Select And Update In Same Transaction With Python Sqlite

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

Solution 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:
    while1:
        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()
        ifnot myrow :
            breakid, = 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"ifnot accepted: 
            # Whoops this job was taken! Try again and get another onecontinue# 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).