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.
mike
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
