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?
>
> I want a standard ability to have a user who is editing a row on some GUI to
> have "control" of that row through a lock. Pessimistic locking. I've read
> various posts talking of having another table that stores the primary key and
> table name which is written to keep the "lock table" and therefore all
> processes "must" check this first.. but what if they don't?
>
> Is there an SA way to deal with this, or do I start inventing?
>
> This is the desired outcome;
>
> Code can lock a row and leave it locked until unlocked or the session ends.
> Other attempts to write to that row will receive a locked exception.
> Some way of enforcing access to the DB for writes must use locking -
> otherwise it's a strange "opt in" approach. i.e. it's only locked if you
> bother to check if it's locked!
>
> If what I read is true, and I need to create my own lock table, is there a
> nice "generic" way (i.e. I don't know or care what table schema is being
> locked via my handy lock table) of getting the primary key of the row in
> question to pass to the lock_row(yourKey). Should I use the primary key, or
> is there some other unique way of identifying it within a table?
>
> In this case, I'm running on MySQL, but I would prefer a DB agnostic solution
> for this project.
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.
>
> Cheers
> Warwick
>
>
> --
> 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.
