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 the record 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 the mtxEditor 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". When a 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
