#59 selects not recognising changes to table

MySQLdb-0.9.2
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2003-06-27
Kevin Smith
No

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)

Discussion

  • 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
    Kevin Smith
    2003-07-10

    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