So the earlier commit ended the transaction but immediately began a new one, that's why a second call to commit had a different behavior? I made the mistake of thinking that the session would be in a similar state to when I originally acquired it, however, this is not true because I still have a open connection to the database and a new transaction as already begun. Correct? For some reason I had the thought a new transaction wouldn't be started with the session after a commit until I actually did something with the session again.
Yeah, expire has no effect so repeatable reads may be on. Thank you for the insight. On Thursday, January 17, 2013 9:38:26 AM UTC-6, Michael Bayer wrote: > > > On Jan 17, 2013, at 10:27 AM, Derek Litz wrote: > > > with mysql_session() as s: > > new_user = User(first_name=u'Derek') > > s.add(new_user) > > s.commit() > > add_last_name(new_user.id) > > # NOTE this makes sense, the object was never updated here > > assert not new_user.last_name == u'Litz' > > > > # NOTE Why doesn't user receive the updated information? > > user = s.query(User).filter(User.id == new_user.id).one() > > > because you made the change to "User" in a different transaction (as a > result of using a different Session). This transaction still continues and > is considered to be "isolated" - SQLAlchemy doesn't invalidate the data > until the transaction ends. If you say, s.expire(user), then you'll get > the new value within the transaction, *assuming* the transaction isolation > here is not enforcing repeatable reads. If MySQL is enforcing repeatable > reads, you'll see the same data for user.last_name for each SELECT > regardless of a change in another transaction. > > all about transaction isolation: > http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html > > > assert not user.last_name == u'Litz' > > # NOTE Despite commit being the last thing I did for this session > > # this fixes the problem. Why didn't it before? > > s.commit() # or s.rollback() or s.close() > > the transaction here ends, ending any "repeatable read" state set up by > that transaction (this would be via MySQL), and additionally the SQLAlchemy > Session expires all the data. The next access starts a new transaction > that gets the latest data. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/C42ExAAYErIJ. To post to this group, send email to sqlalchemy@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.