On Sep 30, 2010, at 10:02 AM, Michael Bayer wrote: > > On Sep 30, 2010, at 4:43 AM, Warwick Prince wrote: > >> Hi All >> >> I would like some advice / best practice on the following basic problem >> please. I'm new to SQL so am groping with some things that used to be >> basic. :-( >> >> I've looked around in SA and have only found a few small notes on locking. >> There is a "for_update" but when I try this it appears to be locked out for >> "me" as well! Perhaps the answer is here and I'm missing something? >> >> <snip> > > The usual approach to pessimistic locking on a simple scale is to use > "SELECT....FOR UPDATE NOWAIT", which locks the selected rows in the current > transaction. Other transactions which attempt to get to the row are > blocked. The NOWAIT means it will raise immediately instead of blocking. > > If you've tried this and "you" seem to be locked out, then you need to get > your transactions straight - you'd need to perform subsequent operations with > the same transaction as that which began the "for_update". > > However, if the "lock" we're talking about isn't just a matter of ensuring > proper concurrency across transactions, and is more like a business-level > lock - i.e. "User XYZ is editing this document", then yes this is not a DB > concurrency issue, its a datamodel one. You'd build the "lock" table as > described, and build a system within your GUI that acquires a row from this > table before allowing operations to proceed. > > How the application does that depends much on what kind of application it is > and over what span such a lock is created. You'd have to trap user actions > at the top level of activity which would be locked, sometimes Python > decorators are good at this, such as : > > @requires_lock > def user_do_something(x, y, z, ...): > .. > > where @requires_lock would look at the current user, maybe look at the > arguments of user_do_something(), determine if that user has acquired a > locking object.
Another option is to use database advisory locks (where available). http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html "Advisory Locks" Cheers, M -- 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.
