[sqlalchemy] Locking strategy
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. 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.
Re: [sqlalchemy] Locking strategy
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 SELECTFOR 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.
Re: [sqlalchemy] Locking strategy
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 SELECTFOR 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.
