Thanks for your help, that's going to work great! sk On 5/2/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > Scott Klarenbach <[EMAIL PROTECTED]> wrote on 04/29/2005 05:39:36 > PM: > > > Thanks Shawn, that helps a lot. I like your general idea of handling > > it at application level. I guess my main concern w/ web apps and > > locking the record (even at app level) is orphaned locks, resulting > > from browser closes or other events that don't go through the normal > > channels of updating or cancelling the update. > > > > So, if you set a lock to have a timeout of say 15 minutes, and the > > user WAS simply taking longer than that 15 minutes, how do you handle > > that when they try and save it? > > > > I was thinking, just go ahead with the commit if the record hasn't > > been locked in the meantime by another user, otherwise, prompt them > > saying it's now been locked by another user and your changes won't be > > saved. But this is the same user inconvenience caused by optomistic > > locking. > > That's basically what I do. Here's my general logic. It may or may not fit > your situation. > > If userA has a record locked and that lock has that timed out but userA > still had the page open for editing, userB could follow along and change the > same record (it appears to be unlocked because userA took too long to save > their changes). If userA finally decides to save their changes, the > application code detects that they no longer have a lock on the record > (either because userB has the record locked or because the lock was cleared > when userB released it) and offers userA two choices: restart the edit from > the current state or abandon their changes. Either way, you avoid changing > userB's update unintentionally. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > > > > > > Thanks, > > Scott. > > > > On 4/29/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > > > > Scott Klarenbach <[EMAIL PROTECTED]> wrote on 04/29/2005 > 02:28:25 > > > PM: > > > > > > > > > > Hello, > > > > > > > > I'm using MySQL 5.0.3 Beta, and I'm hoping for some general > > > > tips/experience you guys may have had regarding optomistic vs > > > > pessimistic locking in a web app (PHP 5.0.3) > > > > > > > > I'm more of a windows programmer, and I've always implemented > > > > pessimistic over optomistic as it's much more professional and > > > > attractive to the end user. > > > > > > > > The problem as you know, is that web development makes pessimistic > > > > locking much more difficult, because of the user closing the browser, > > > > and a bunch of other factors I can't control. > > > > > > > > Question: which type of locking do you usually implement in your web > > > > apps, and do you do it at a DB level or in your application layer? > > > > > > > > Any thoughts on a custom locking scheme (ie, a lock table that is > > > > written to with a user id and record id and timestamp)? > > > > > > > > Other solutions/suggestions are greatly appreciated. > > > > > > > > Thanks in advance. > > > > > > > > Scott. > > > > > > > > > > I don't use record locking until it's time to actually do something to > the > > > data. Even then it depends on what I am doing. As Mathias already > suggested, > > > InnoDB works better for this because it can lock individual rows and has > > > full transactional support. > > > > > > In my webapp the users do mostly reads (which do not require locks) and > few > > > edits. In order to prevent another user from editing the same record > that > > > someone else is already editing, I have added a field to those table > that > > > require concurrency checking and fill in that field with the > > > application-login of the user requesting to edit the record. It looks > > > something like this: > > > > > > UPDATE datatable > > > SET mtxEditor = 'user_id' > > > WHERE pkid = #### > > > AND mtxEditor is null; > > > > > > SELECT mtxEditor > > > FROM datatable > > > WHERE pkid = ####; > > > > > > If I get a match, then I allow the user to navigate to the "edit" web > page, > > > otherwise they get the "view" web page and a popup saying that therecord > is > > > already being edited by <insert name here>. That way I don't have 2 > users > > > trying to make concurrent changes and the second or later users are told > who > > > has that record open so they can check with that person to see if they > are > > > done or if they just forgot to save their changes. > > > > > > Now, if the user exits the page manually (the page gets the onunload > event) > > > or decides to cancel their edit, I request a page that cleares > themtxEditor > > > field. That works something like this: > > > > > > UPDATE datatable > > > SET mtxEditor = null > > > WHERE pkid = #### > > > AND mtxEditor ='userid'; > > > > > > When it comes time to apply the effects of the edit, I check the > mtxEditor > > > field one more time to make sure that nobody has hijacked the page or > that > > > the user didn't navigate away (causing their edit lock to go away) and > come > > > back (a user can't update the record unless they are the one editing > it). > > > It's not perfect but it works remarkably well for the application-level > > > locking I need to provide. > > > > > > One enhancement to this would be to provide a "sunset timer". Whena user > is > > > assigned as the editor set a datetimefield to 10 or 20 minutes from > NOW(). > > > If the user hasn't submitted their updates by then, they have to > re-request > > > to edit the page. That way, in case someone manages to leave the page > > > without tripping the onunload event (which would trigger the reset of > the > > > mtxEditor field) you still have a way of "unlocking" the record for the > next > > > user. > > > > > > BTW, I use Hungarian notation only if a field is used more like a > variable > > > than the other data-related fields. In this case the 'mtx' is my > shorthand > > > for 'mutex' because that field is acting as a mutual exclusion flag to > be > > > read by the application. The name of the application user editing a > record > > > adds no useful value to the rest of the data stored on the table (if I > > > listed the properties of the object being stored in the table, the > editing > > > user isn't one of them. Am I making sense?). > > > > > > It's generally a BAD idea to lock any rows for longer than what is > > > absolutely necessary to complete the transaction. That means you should > not > > > lock a row (at the database level) and wait for a user to update it > before > > > you release the lock. To do that completely ruins your database > concurrency. > > > Move that kind of lock into the application layer and manage your > > > user-vs-user concurrency from there. > > > > > > Shawn Green > > > Database Administrator > > > Unimin Corporation - Spruce Pine > > > > > > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
