I am using SQLite. I can see the COMMIT, and there is only one. The SQL log shows that the COMMIT is immediately followed by a retrieve of the same row, but it's retrieving a value that was flushed ( but not committed ) from a different session!
So, are these 2 sessions connected to the same database session? (I was assuming they have a separate database session) Are my 2 sessions somehow connected? I was thinking that they are completely separate, but maybe they are not? On Apr 15, 12:21 pm, Conor <[email protected]> wrote: > NickPerkins wrote: > > Just when I thought I understood the Session object, I found this > > behavior: > > ( I am starting to think about concurrency and locking ) > > > I create 2 sessions, and load each with the same ( persisted ) object. > > The objects appear to be independent, even after both sessions have > > flushed, > > but when I commit one session, it suddenly picks up the change from > > the other session! > > > This is confusing me! > > What is going on here? > > ( my business object is a "Policy", and column "test" is not the PK ) > > > class PolicyNotFoundException(Exception): > > pass > > > def open_policy(key): > > session = Session() > > try: > > policy = session.query(Policy).filter_by(key=key).all()[0] > > return policy, session > > except: > > raise PolicyNotFoundException > > > def test_concurrent_updates(): > > policy1,session1 = open_policy('NICK') > > policy2,session2 = open_policy('NICK') # retrieves on pk...gets > > same record > > assert( policy1 != policy2 ) > > assert( session1 != session2 ) > > assert( policy1 in session1 ) > > assert( policy1 not in session2 ) > > assert( policy2 in session2 ) > > assert( policy2 not in session1 ) > > > policy1.test = 'ONE' > > session1.flush() > > > policy2.test = 'TWO' > > session2.flush() > > > assert( policy1.test == 'ONE' ) # not affected by flush of session > > 2 > > > session1.commit() > > assert( policy1.test == 'TWO' ) # really? > > I see two possible explanations: > > 1. (More likely) You are using a database that does not support > transactions (e.g. MySQL with MyISAM tables). If this is the case, > every insert/update/delete is immediately visible to other DB > connections. In this case, assert(policy1.test == 'ONE') succeeds > because the policy1 object has a cached value for its 'test' > attribute. If you had put session1.expire(policy1)) between > session2.flush() and assert(policy1.test == 'ONE'), the assertion > would fail. Since session1.commit() expires every object in > session1, policy1 will then pick up the new value from the DB. > 2. Your sessions have autocommit enabled on your sessions. I think > SQLAlchemy would auto-expire objects in this case (causing > assert(policy1.test == 'ONE') to fail), but I'm not sure. > > I would recommend that you enable SQL logging on your engine so you can > observe exactly when policy1 fetches the new values from the DB, and > when COMMITs are getting issued to the DB. > > -Conor -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
