On Aug 12, 2010, at 7:36 AM, Erich Eder wrote: > I've found a strange behaviour when using multiple sessions: A > committed change to an object in one session does not get reflected in > the other session, even after a session.expire_all() (or expire() or > refresh() on the object. In fact, even an explicit query does not > retrieve the changed data. I'm using MySQL. Further investigation > showed that it happens only with InnoDB. Using MyISAM produces the > expected results. Looks like a caching issue with InnoDB.
its not "caching", its transaction isolation, which is why expire_all() is not the issue here (and why expire_all() is not really needed by itself with autocommit=False - rollback() and commit() handle it). at the end, session1 is still open in its second transaction which has loaded a1.data as 123. http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html > > Here is some test code: > -------------------------------------------- > from sqlalchemy import create_engine, Table, Column, Integer > from sqlalchemy.orm import sessionmaker > from sqlalchemy.ext.declarative import declarative_base > > Base=declarative_base() > > class A(Base): > __tablename__="a" > __table_args__ = {'mysql_engine':'InnoDB'} > > id = Column(Integer, primary_key=True) > data = Column(Integer) > > engine = create_engine('mysql://localhost/test') > Base.metadata.bind = engine > Base.metadata.drop_all() > Base.metadata.create_all() > > Session = sessionmaker(bind=engine) > session1 = Session() > session2 = Session() > > a1 = A() > a1.id = 1 > a1.data = 123 > > session1.add(a1) > session1.commit() > > print a1.data # trigger query of a1.data > > a2 = session2.query(A).one() > > print a1,a2 # to see that a1 and a2 are different objects > print a2.data > > a2.data = 999 > session2.commit() > > print "a1.data: ", a1.data > print "session1.query(A.data): ", session1.query(A.data).one()[0] > print "session2.query(A.data): ", session2.query(A.data).one()[0] > > print "session1.expire_all()" > session1.expire_all() > > print "a1.data: ", a1.data > print "session1.query(A.data): ", session1.query(A.data).one()[0] > print "session2.query(A.data): ", session2.query(A.data).one()[0] > ------------------------------------ > > The output is: > 123 > a1.data: 123 > session1.query(A.data): 123 > session2.query(A.data): 999 > session1.expire_all() > a1.data: 123 > session1.query(A.data): 123 > session2.query(A.data): 999 > > > When commenting out the __table_args__ line in order to use MyISAM, > the output is what one would expect: > 123 > a1.data: 123 > session1.query(A.data): 999 > session2.query(A.data): 999 > session1.expire_all() > a1.data: 999 > session1.query(A.data): 999 > session2.query(A.data): 999 > > > Any idea? > > Thanks, > Erich > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
