the "beginning" of a transaction is implicit when using the Python DBAPI, typically when the first SQL statement is emitted subsequent to the last commit or rollback.
the subtlety in your example, as I look more closely, is that it says: commit() add_last_name(new_user.id) the "commit" ends the transaction. then "new_user.id" emits a SELECT for the row again. while this is just the primary key, it also checks that the row hasn't been deleted. But in any case that's your new transaction, before add_last_name() gets called. On Jan 17, 2013, at 10:59 AM, Derek Litz wrote: > 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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. 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.
