SQLite uses the "singleton thread pool" by default which shares one connection
per thread. This so that a :memory: connection works as expected.
There's a good deal of discussion and advice on this topic at
http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html?highlight=sqlite#threading-behavior
.
On Apr 15, 2010, at 5:41 PM, NickPerkins wrote:
> 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 <conor.edward.da...@gmail.com> 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 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.
