Why do I need to reconnect to the database to see changes in the tabular data?
I am querying a MySQL table periodically and checking the data on the same row.
I am using MySQLdb to do a job, querying the same table and row every 15 seconds.
In fact, the row data changes every 3 seconds, but the cursor always returns the same value.
Strange thing: after closing the MySQL connection and reconnecting, using a new cursor to execute the same select command, a new value is returned.
The code, which I suspect is wrong, starts 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()
Output:
94 94
If I change the code so that it closes the connection and reconnects, it returns the last 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()
Output:
94 104
Why is this difference in behavior?
Here's the definition 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])
source to share
This is almost certainly the result of transaction isolation. I assume that since you did not specify otherwise that you are using the default storage engine ( InnoDB ) and isolation level ( REPEATABLE READ
):
RE-READING
Isolation level for InnoDB. It prevents any rows that might be changed by other transactions from being modified, thus blocking non-repeatable reads , but not phantom . It uses a moderately strict locking strategy so that all requests in a transaction see data from the same snapshot, that is, the data as it was at the time the transaction began.
See Consensus Non-Blocking Reads in the MySQL docs for details .
In plain English, this means that when you are SELECT
from a table within a transaction, the values you read from the table will not change during the entire period of the transaction ; you will continue to see the state of the table during the opening of a transaction, as well as any changes made in the same transaction.
In your case, changes every 3 seconds are made in a different session and transaction. To "see" these changes, you need to leave the transaction that started when you released the first SELECT
and start a new transaction, which will then "see" a new snapshot of the table.
You can manipulate transactions explicitly with , and in SQL, or by calling and . An even better approach here might be to use context managers ; eg: START TRANSACTION
COMMIT
ROLLBACK
Connection.commit()
Connection.rollback()
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 statement with
, when used with a MySQLdb object Connection
, returns the cursor to you. When you leave the block with
, the Connection.__exit__
following is called :
def __exit__(self, exc, value, tb):
if exc:
self.rollback()
else:
self.commit()
Since all you have done is read data, you cannot rollback or commit; when writing data, remember that leaving a block through an exception will rollback your changes, and usually will cause your changes to change.
Note that this did not close the cursor, it only controlled the transaction context. I'll go into more detail about this in my answer to When to close cursors using MySQLdb , but it says that you don't have to worry about closing cursors at all when using MySQLdb.
You can also make your life a bit easier by passing the database as a parameterMySQLdb.connect
instead of issuing a USE
statement.
This answer to a very similar question suggests two other approaches - you can change the isolation level to READ COMMITTED
or enable autocommit .
source to share