Python's MySqlDB not getting updated row

Question

I have a script that waits until some row in a db is updated:

con = MySQLdb.connect(server, user, pwd, db)

When the script starts the row's value is "running", and it waits for the value to become "finished"

while(True):
    sql = '''select value from table where some_condition'''
    cur = self.getCursor()
    cur.execute(sql)
    r = cur.fetchone()
    cur.close()
    res = r['value']
    if res == 'finished':
        break
    print res
    time.sleep(5)

When I run this script it hangs forever. Even though I see the value of the row has changed to "finished" when I query the table, the printout of the script is still "running".

Is there some setting I didn't set?

EDIT: The python script only queries the table. The update to the table is carried out by a tomcat webapp, using JDBC, that is set on autocommit.

Answer 1

This is an InnoDB table, right? InnoDB is transactional storage engine. Setting autocommit to true will probably fix this behavior for you.

conn.autocommit(True)

Alternatively, you could change the transaction isolation level. You can read more about this here: http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

The reason for this behavior is that inside a single transaction the reads need to be consistent. All consistent reads within the same transaction read the snapshot established by the first read. Even if you script only reads the table this is considered a transaction too. This is the default behavior in InnoDB and you need to change that or run conn.commit() after each read.

This page explains this in more details: http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Answer 2

I worked around this by running

c.execute("""set session transaction isolation level READ COMMITTED""")

early on in my reading session. Updates from other threads do come through now.

In my instance I was keeping connections open for a long time (inside mod_python) and so updates by other processes weren't being seen at all.