Yeah, just reading through the logs, they confirm my new understanding :).
Thanks much!
2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine INSERT INTO user
(first_name, last_name) VALUES (%s, %s)
2013-01-17 10:09:41,335 INFO sqlalchemy.engine.base.Engine ('Derek', None)
2013-01-17 10:09:41,336 INFO sqlalchemy.engine.base.Engine COMMIT
2013-01-17 10:09:41,337 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit) *<------- New Transaction Begun Immediately*
On Thursday, January 17, 2013 9:59:37 AM UTC-6, 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/-/W_8sWLc-AWcJ.
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.
