MySQL/Python — committed changes not appearing in

Question

Using MySQL Connector/Python I have a loop that keeps checking a value for a change every 2 seconds. Without all the meat, here is the loop (the print is there for testing purposes:

try:
   while True:
       request = database.get_row(table="states", wherecol="state", whereval="request_from_interface")[0]
       print(request.value)
       time.sleep(2)
except KeyboardInterrupt:
   pass
# back to normal operation

get_row is a simple select query and it returns the namedtuple fetchall (hence the [0] at the end, it will only ever be one row).

The problem is that once it gets the initial value, it keeps returning that value even if I change it. For example, if it is "0" to start, it keeps printing "0" even if I go to adminer and change it and even if I open a new terminal and change it and commit it. I've tried setting the query_cache_size to 0, still no luck. I thought it was a commit problem, but that wasn't it, either. If I change it and reconnect, I see the change, but I'm not sure why it won't change while in the program. Any thoughts?

EDIT: in case it comes up, yes I'm closing the cursor and grabbing a new cursor with every call.

Answer 1

The default isolation level of InnoDB is REPEATABLE READ. This means that subsequent consistent SELECT statements will read the same snapshot established by the first within a transaction. Though you're opening and closing cursors, you're probably doing that inside a transaction and so you're reading the established snapshot over and over again. Either ROLLBACK or COMMIT to end the current transaction so that you can read values committed after the snapshot was taken.