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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---