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 > > > >
