Mike,
Michael Bayer wrote:
> On Nov 7, 2007, at 5:19 AM, Werner F. Bruhin wrote:
>
>
>> PassiveDefault is great to know.
>>
>> However I still have a problem with the following.
>>
>> In a program I do something like this:
>> botlot3 = session.query(db.Bottaglot).get(39)
>>
>> Then some other user and/or application changes data (I faked this by
>> setting a debugger break point and used the db admin tool to change
>> some
>> data and committed it) in the database and commits, then when I do
>> this:
>> botlot4 = session.query(db.Bottaglot).get(39)
>>
>> I expected to get the data from the database, however SA gets it from
>> the session (I set echo=True) and I don't see a select being done
>> between the first statement and the second.
>>
>> Searching in the documentation I see that it is documented that
>> "get" is
>> NOT querying the database if the key is present, however I haven't
>> found
>> how I can do a primary key query so that SA goes to the database.
>>
>> I'll keep searching in the doc, but would still appreciate any hints.
>>
>>
>
> Werner -
>
> theres three approaches which can be used individually or together for
> this type of thing.
>
> 1. When running the session within a transaction, either using
> session.begin() or creating your session with transactional=True, you
> let the database's normal transactional behavior handle transaction
> isolation issues (reading one value, making a change based on that
> value and commiting, but then someone else changed in the middle, is a
> transaction isolation issue). I would recommend running within a
> transaction at the very least.
>
> 2. If youd like to explicitly place a "lock" on the row, you can say
> session.query(Foo).with_lockmode('update').get(39). This will use a
> SELECT..FOR UPDATE in order to fetch the row and will then explicitly
> lock the row against concurrent access, until the next UPDATE occurs
> or the transaction is completed/rolled back. This is known as
> "pessimistic locking", since it assumes that a contention issue will
> occur and prevents against it from happening. SELECT..FOR UPDATE
> should be run in a transaction.
>
> 3. Alternatively, "optimistic locking" can be used by setting the
> "version_id_col" option on your mapper() (i.e. mapper(....,
> version_id_col=mytable.c.version_col) ); you add an integer valued
> column to your table which you reference via this setting. The mapper
> will place increasing numbers within the column upon each change to
> the row. When a flush() occurs, the mapper updates the row based not
> only on the primary key columns but also on the expected "version"
> number, and if no row was located in the update, it throws a
> ConcurrencyError. this is known as "optimistic" since it doesn't
> actually prevent the contention issue from happening, but instead when
> it does happen knows enough to abort the transaction.
>
> The Query can also fetch data from the database without using the
> "cache" - if you say query.load(39) it will fetch the row from the
> database unconditionally and re-populate the existing object if
> already present, but note that this removes any changes you've made to
> the object. However, this is not much of a guarantee of anything,
> since if you load() the object, make changes, then flush, theres still
> the possibility that the concurrent changes occured in between the
> load/flush steps. So the pessimistic/optimistic locking approaches
> are better solutions if concurrency issues are expected.
>
> For "slower" concurrency issues, such as you arent concerned about
> concurrency within a small period of time and are instead concerned
> about two users changing some data five minutes apart, I would note
> that an individual Session is typically meant for a single set of
> operations, then its closed. Holding onto a Session for a long time,
> across web requests, etc., is not really its primary usage model. Its
> more like something you "check out", do some things with it, then
> check it back in.
>
>
Thanks for the detailed response. As mentioned earlier I found
"refresh" which did the trick for this problem, but the "query.load" is
really what I was looking for.
Werner
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
