Why do I need to reconnect to the database to see changes in table data?

Question

I periodically query a MySQL table and check data in the same row.

I use MySQLdb to do the job, querying the same table and row every 15 seconds.

Actually, the row data changes every 3 seconds, but the cursor always return the same value.

The strange thing is: after I close the MySQL connection and reconnect, using a new cursor to execute the same select command, the new value is returned.

The code that I suspect to be wrong is begins after the comment:

config = SafeConfigParser()
config.read("../test/settings_test.conf")

settings = {}
settings["mysql_host"] = config.get("mysql","mysql_host")
settings["mysql_port"] = int(config.get("mysql","mysql_port"))
settings["mysql_user"] = config.get("mysql","mysql_user")
settings["mysql_password"] = config.get("mysql","mysql_password")
settings["mysql_charset"] = config.get("mysql","mysql_charset")

#suspected wrong code
conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
#during 15 second, I manually update the row and commit from mysql workbench
time.sleep(15)    

cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()
print result
conn.close()

The output is:

94
94

If I change the code so that it closes the connection and re-connects, it returns the latest value instead of repeating the same value:

conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
conn.close()

time.sleep(15)
#during that period, I manually update the row and commit from mysql workbench

conn = mysql_from_settings(settings)
cur = conn.cursor()
cur.execute('use database_a;')
cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()[0]
print result
conn.close() 

The output is:

94
104

Why this difference in behavior?

Here is the definition of mysql_from_settings:

def mysql_from_settings(settings):
    try:
        host = settings.get('mysql_host')
        port = settings.get('mysql_port')
        user = settings.get('mysql_user')
        password = settings.get('mysql_password')
        charset = settings.get('mysql_charset')
        conn=MySQLdb.connect(host=host,user=user,passwd=password,port=port,\
               charset=charset)

        return conn
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])

Answer 1

This is almost certainly the result of transaction isolation. I'm going to assume, since you haven't stated otherwise, that you're using the default storage engine (InnoDB) and isolation level (REPEATABLE READ):

REPEATABLE READ

The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

For more details, see Consistent Nonlocking Reads in the MySQL docs.

In plain English, this means that when you SELECT from a table within a transaction, the values you read from the table will not change for the duration of the transaction; you'll continue to see the state of the table at the time the transaction opened, plus any changes made in the same transaction.

In your case, the changes every 3 seconds are being made in some other session and transaction. In order to "see" these changes, you need to leave the transaction that began when you issued the first SELECT and start a new transaction, which will then "see" a new snapshot of the table.

You can manage transactions explicitly with START TRANSACTION, COMMIT and ROLLBACK in SQL or by calling Connection.commit() and Connection.rollback(). An even better approach here might be to take advantage of context managers; for example:

conn = mysql_from_settings(settings)
with conn as cur:
    cur.execute('use database_a;')
    cur.execute('select pages from database_a_monitor where id=1;')
    result = cur.fetchone()[0]
print result
#during 15 second, I manually update the row and commit from mysql workbench
time.sleep(15)    

cur.execute('select pages from database_a_monitor where id=1;')
result = cur.fetchone()
print result
conn.close()

The with statement, when used with MySQLdb's Connection object, gives you back a cursor. When you leave the with block, Connection.__exit__ is called:

def __exit__(self, exc, value, tb):
    if exc:
        self.rollback()
    else:
        self.commit()

Since all you've done is read data, there's nothing to roll back or commit; when writing data, remember that leaving the block via an exception will cause your changes to be rolled back, while leaving normally will cause your changes to be committed.

Note that this didn't close the cursor, it only managed the transaction context. I go into more detail on this subject in my answer to When to close cursors using MySQLdb but the short story is, you don't generally have to worry about closing cursors when using MySQLdb.

You can also make your life a little easier by passing the database as a parameter to MySQLdb.connect instead of issuing a USE statement.

This answer to a very similar question offers two other approaches—you could change the isolation level to READ COMMITTED, or turn on autocommit.

Answer 2

The problem you face not related with Python but MySql setting:

After changing bellow in mysql databse you would fix that:

Login mysql as root

mysql> set global transaction isolation level read committed;

For permanent: (Even after restart mysql)

mysql> set global transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like '%isolation%'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.01 sec)