Re: Pessimistic Record Locking
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 . 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 th
Re: Pessimistic Record Locking
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 . 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 tim
Re: Pessimistic Record Locking
I could set the user's session timeout to be the same duration as the record lock timeout...that way, in any event where the user's lock would have expired, he would have to log back into the system anyway...but this may be inconvenient as well, as I know a lot of user's could be idle for some time, and would be annoyed if they had to log back in every time... On 4/29/05, Scott Klarenbach <[EMAIL PROTECTED]> wrote: > 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. > > 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 the record is > > already being edited by . 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
Re: Pessimistic Record Locking
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. 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 the record is > already being edited by . 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
Re: Pessimistic Record Locking
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 . 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
RE: Pessimistic Record Locking
Hi, Try to have optimistic locking in the database server (row level locking a.k.a. innodb storage), et let your transactions managed by the server. Any line of code like "lock table" will generate a very bad web application performances. You can add connection pooling if you want to manage total number of users. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Scott Klarenbach [mailto:[EMAIL PROTECTED] Sent: vendredi 29 avril 2005 20:28 To: My SQL Subject: Pessimistic Record Locking 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pessimistic Record Locking
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
