Python hangs on MySQL Update

Question

I've been working on this for a while now and searches on the internet have not come up with a solution.

I want to check if another process is accessing my DB before accessing it. I have a table with columns id and state. If state is 1 then I want to change it to 0 and continue to access, otherwise I want to leave it alone. Below is my code, I can't see why it won't work BUT it hangs every time on the last line here cursor.execute(set_lock)

get_lock = "SELECT state FROM running WHERE id=1"
cursor.execute(get_lock)
result = cursor.fetchall()
if(result[0][0]):
    set_lock = "UPDATE running SET state=0 WHERE id=1"
    cursor.execute(set_lock)

I'm pretty sure it has something to do with the reuse of the cursor, but I can't see why it should be a problem, I've done it before successfully. Any help you be great.

Cheers

I thought I had the problem solved but the fix was flakey, it seemed to work to start with but then started returning the wrong state. Running the MySQL statement in PHPMyAdmin it returned 1 as it should, but when printing the result it returned 0 and so skipped the content of the if statement.

cursor.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
get_lock = "SELECT state FROM running WHERE id=1;"
cursor.execute(get_lock)
result = cursor.fetchall()
if(result[0][0]):
    set_lock = "UPDATE running SET state=0 WHERE id=1;" //Lock
    cursor.execute(set_lock)
    cursor.execute("COMMIT;")

    ---- DO WHAT I NEED TO DO ----

    set_lock = "UPDATE running SET state=1 WHERE id=1;" //Unlock
    cursor.execute(set_lock)
    cursor.execute("COMMIT;")

cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
cursor.close()

Any ideas?

You should read http://stackoverflow.com/questions/10935850/when-to-use-select-for-update in the meantime. Your current implementation might be open for race conditions depending on your transaction isolation. — Ilja Everilä, Mar 31 '16 at 11:41
After select for update and update you need to release the row lock by committing. Otherwise the other select for update will block waiting for the row lock. — Ilja Everilä, Mar 31 '16 at 16:41

score 1 · Answer 1 · edited May 23 '17 at 12:24

Found the solution thanks to following leads from Ilja's comment.

get_lock = "SELECT state FROM running WHERE id=1 FOR UPDATE;"
cursor.execute(get_lock)
result = cursor.fetchall()
if(result[0][0]):
    set_lock = "UPDATE running SET state=0 WHERE id=1;"
    cursor.execute(set_lock)
    cursor.execute("COMMIT;")

Initially this wasn't working due to a typo in the SELECT statement. Also the last statement is important cursor.execute("COMMIT;").

after reading this question I seemed to have a solution but was intermittent in its behaviour.

cursor.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;")
get_lock = "SELECT state FROM running WHERE id=1;"
cursor.execute(get_lock)
result = cursor.fetchall()
if(result[0][0]):
    set_lock = "UPDATE running SET state=0 WHERE id=1;" //Lock
    cursor.execute(set_lock)
    cursor.execute("COMMIT;")

    ---- DO WHAT I NEED TO DO ----

    set_lock = "UPDATE running SET state=1 WHERE id=1;" //Unlock
    cursor.execute(set_lock)
    cursor.execute("COMMIT;")

cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;")
cursor.close()

I actually meant you should use `SELECT ... FOR UPDATE`. With `READ UNCOMMITTED` you do get the updated value (through a dirty read), if the other process manages to issue the `UPDATE` before `SELECT` in the other, but there is still a small time window where both processes may `SELECT`, see that the lock is not set and then issue 2 `UPDATE`s and proceed thinking they acquired the lock. — Ilja Everilä, Mar 31 '16 at 14:44
The way I read it lead me to the `READ UNCOMMITTED` method. I see what you are saying. I will see how it goes but keep you're comments in mind. Thanks for your help. — Stuart Clarke, Mar 31 '16 at 14:53

Python hangs on MySQL Update

1 Answers1