#59 selects not recognising changes to table

[Kevin Smith]

If a row changes during a session connection, select on
this row will not retrieve the changes but the old
snapshot of the row. However, an update to the row
does recognize changes because the update will fail if
the where is structured to only select based on the old
snapshot.

For example, run program number 1 multiple times during
the running of program 2, and the output will tell the
story (note that the tables are InnoDB types):

PROGRAM 1:
import MySQLdb
db = MySQLdb.connect("localhost", "root", "", "DEV")
cursor = db.cursor()
cursor.execute("set autocommit = 0")
cursor = db.cursor()
cursor.execute("update mytable set version = version +
1, sys_mod_date = sysdate() where name = 'me'")
db.commit()
cursor.execute("select * from mytable where name = 'me'")
rs = cursor.fetchone()
print str(rs)

PROGRAM 2:
import MySQLdb
db = MySQLdb.connect("localhost", "root", "", "DEV")
cursor = db.cursor()
cursor.execute("set autocommit = 0")
while 1:
raw_input("hit enter")
cursor = db.cursor()
cursor.execute("select * from mytable where
name = 'me'")
rs = cursor.fetchone()
print str(rs)

[Andy Dustman]

Logged In: YES
user_id=71372

This sounds like normal behavior.

http://www.mysql.com/doc/en/InnoDB_consistent_read.html

[Nobody/Anonymous]

Logged In: NO

Doesn't this have something to do with MySQL's default
transaction level being set to repeatable read? And
therefore the Python stuff is behaving as it should?

[Kevin Smith]

Logged In: YES
user_id=638620

Thanks for the info, I have retested the scenarios with the
knowledge that MySQL's default behaviour is set to
repeatable reads. If I place a commit statement after the
select statement in program 2, everything works "correctly"
This is because the commit will advance the timepoint for
the read. It should be noted that it may be safer to issue
a rollback statement as this will also advance the timeout -
it all depends on what you are trying to or not trying to
accomplish.

This bug should be rejected as it is part of "normal" system
behaviour.

Thanks for taking the time as it has cleared up a rather
irksome issue!

Cheers