RE: [PHP-DB] Multi-User Update Problem
To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm On 30 November 2004 14:45, SCALES, Andrew wrote: Thanks very much for your help. The main difficulty I was having really was unlocking the record again if the user crashed out or just closed down their browser/computer (something they have a bad habit of doing) but storing the time the record was locked and ignoring the lock if it's over that time sounds interesting. I may try storing the data in a session variable and then comparing that to the database before the updated data is inserted into the record as Bastien suggested. We wanted to keep hits on the db to a minimum, but seen as some extra traffic will be necessary anyway I may just try that. Another approach to this might be: Keep a column in your database table for time of last update of each record. When a user reads a record for update, don't lock it at this point, but save the time at which it was read into the user's session (or somewhere in the database). On receiving a potential update, check the time the record was read (as recorded above) and the last update time of the record (lock the record as part of this query) -- if the update time is later then the read time, someone else has updated in the interim and you should abandon ship; otherwise, update the record including a new last-updated value; in either case, unlock the record. This methodology keeps both the lock and unlock in the same script (and potentially within a single database transaction), so no need for any external checks for locked records, and minimizes the amount of time for which any one record is locked. Cheers! Mike - Mike Ford, Electronic Information Services Adviser, Learning Support Services, Learning Information Services, JG125, James Graham Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United Kingdom Email: [EMAIL PROTECTED] Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
I apologise if my explanation caused confusion. What I meant to say was that the update page will pull down the relative record as chosen by a search page prior to the update page and then populate the form with that data. Then when someone submits the form all of the fields will be updated in that record, even if they haven't changed anything. The problem is that we have some pages where more than one person has to work on the same record and so there's always a risk of two people with the same record open at once overwriting each other's changes. Thanks very much for your suggestions though. Andy -Original Message- From: Norland, Martin [mailto:[EMAIL PROTECTED] Sent: Monday, 29 November 2004 15:36 To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Multi-User Update Problem -Original Message- Does anyone know a good way of locking out access to a record on a MySQL database when someone has the update page open? The problem is that we have a local intranet site which is accessed by members of different departments. Now at the moment when someone loads the page all of the data is pulled into the form, and then when they submit, all of that data is passed back to the database, including anything they haven't changed. snip You should really rethink whether sending all those records is necessary, or wise. Is the convenience of mass updates really worth the headache? What we are trying to avoid is situations where for example someone leaves the form open on their computer and goes away for lunch. In the meantime someone else at a different station makes changes to the form. Then when the first person comes back from lunch, they submit the form, thereby writing over all of the second person's changes with the old data. snip My personal opinion on this: Any database that's so simple/small that it should have its entire contents updated every change, should only be managed by a single person. If that isn't an option, then the individuals should be modifying individual records instead of the entire recordset. This means a listing page, and selecting the record (or records, using checkboxes) to update. This is where you can easily set your locking, perhaps with a timestamp that will time out after X minutes, to prevent users overwriting. If neither of these are options, you have larger problems then you'll be able to properly solve in a web environment. Forcing the browser to re-display the page every minute [set the href explicitly, not reload, in case of post/etc.] is something that should at least reduce the chances of massive overwrite errors. Not that I particularly approve of that method :) It is, however, a relatively functional solution - if you're stuck with neither of the above as being options. - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
Thanks very much for your help. The main difficulty I was having really was unlocking the record again if the user crashed out or just closed down their browser/computer (something they have a bad habit of doing) but storing the time the record was locked and ignoring the lock if it's over that time sounds interesting. I may try storing the data in a session variable and then comparing that to the database before the updated data is inserted into the record as Bastien suggested. We wanted to keep hits on the db to a minimum, but seen as some extra traffic will be necessary anyway I may just try that. Thank you both for the advice, Andy -Original Message- From: Gryffyn, Trevor [mailto:[EMAIL PROTECTED] Sent: Monday, 29 November 2004 15:44 To: [EMAIL PROTECTED] Cc: Bastien Koert; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Multi-User Update Problem Yeah, all good thoughts. Someone else had a similar question that I answered recently (I think it was in private email) and in addition to the thoughts below, you might also look into recording WHEN the record was opened (and locked) so you can expire the lockout. I'd use this in conjunction with a browser side META REFRESH and a PRAGMA cache expire command so if the person who opened the record doesn't do anything with it within 2 hours, their browser auto-refreshes (maybe notifying them that their session was terminating and if they want to save the work they've done already) and it unlocks the file. You could also have a nightly job run that looks for all records locked over 2 hours ago or something and auto-unlock them (or just have the script that looks to see if something's locked see if it was over 2 hours ago and ignore the Locked == TRUE tag in the database). Anyway, the idea is to expire the session and user's cache so that they'll get a Warning: Page Expired making it impossible for them to update something after 2 hours has passed or something like that. Also to free up locked pages on a regular basis or allow people to get into them again if they time out. -TG -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Monday, November 29, 2004 10:22 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Multi-User Update Problem Its a tough one. My personal fav is to mark the record as locked by changing a field called 'locked' from 0 to 1. Check for this when extracting the record and if is present, alert the user this record is locked (if you want to get fancy you could even track who locked it and the tell the user that) and show a non-updatable form of the page (either with the fields as readonly or by creating some kind of view only page where the data is simply echoed out). If you want to get REALLY out there, you could track who requested the locked record and notify them when it was released(by the original requestor submitting the form). The possibilities are endless The other option is to create/write an entire concurrency module that will store the record in a separate entity (another table or session object) and then compare any changes made upon submission. I have seen this but I don't really like it, since the ultimate choice is left to the user as to what data is correct. Bastien From: SCALES, Andrew [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Multi-User Update Problem Date: Mon, 29 Nov 2004 11:16:08 - Hello, Does anyone know a good way of locking out access to a record on a MySQL database when someone has the update page open? The problem is that we have a local intranet site which is accessed by members of different departments. Now at the moment when someone loads the page all of the data is pulled into the form, and then when they submit, all of that data is passed back to the database, including anything they haven't changed. What we are trying to avoid is situations where for example someone leaves the form open on their computer and goes away for lunch. In the meantime someone else at a different station makes changes to the form. Then when the first person comes back from lunch, they submit the form, thereby writing over all of the second person's changes with the old data. Does anyone know of a good way to solve or work around this problem? We have thought about setting a flag on the database whenever someone pulls down the data, however if their browser crashes or they shutdown with the window open then this is going to leave the data locked. Thanks very much, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
You could always lock a record and only allow that user to see the locked files (maybe showing an icon indicating that it SHOULD already be checked out in another browser window and ask if the user still wants to open the file (in the case of a computer crash or they closed their browser without properly checking the file back in, etc). Keeping hits on a database to a minimum is a good goal, but databases are meant to be used and abused too.. So don't be afraid to do a little select or update from time to time. It's when you do things like select * from MillionRowTable repeatedly that you have issues. :) Good luck! -TG -Original Message- From: SCALES, Andrew [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 9:45 AM To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Multi-User Update Problem Thanks very much for your help. The main difficulty I was having really was unlocking the record again if the user crashed out or just closed down their browser/computer (something they have a bad habit of doing) but storing the time the record was locked and ignoring the lock if it's over that time sounds interesting. I may try storing the data in a session variable and then comparing that to the database before the updated data is inserted into the record as Bastien suggested. We wanted to keep hits on the db to a minimum, but seen as some extra traffic will be necessary anyway I may just try that. Thank you both for the advice, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
Its a tough one. My personal fav is to mark the record as locked by changing a field called 'locked' from 0 to 1. Check for this when extracting the record and if is present, alert the user this record is locked (if you want to get fancy you could even track who locked it and the tell the user that) and show a non-updatable form of the page (either with the fields as readonly or by creating some kind of view only page where the data is simply echoed out). If you want to get REALLY out there, you could track who requested the locked record and notify them when it was released(by the original requestor submitting the form). The possibilities are endless The other option is to create/write an entire concurrency module that will store the record in a separate entity (another table or session object) and then compare any changes made upon submission. I have seen this but I don't really like it, since the ultimate choice is left to the user as to what data is correct. Bastien From: SCALES, Andrew [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Multi-User Update Problem Date: Mon, 29 Nov 2004 11:16:08 - Hello, Does anyone know a good way of locking out access to a record on a MySQL database when someone has the update page open? The problem is that we have a local intranet site which is accessed by members of different departments. Now at the moment when someone loads the page all of the data is pulled into the form, and then when they submit, all of that data is passed back to the database, including anything they haven't changed. What we are trying to avoid is situations where for example someone leaves the form open on their computer and goes away for lunch. In the meantime someone else at a different station makes changes to the form. Then when the first person comes back from lunch, they submit the form, thereby writing over all of the second person's changes with the old data. Does anyone know of a good way to solve or work around this problem? We have thought about setting a flag on the database whenever someone pulls down the data, however if their browser crashes or they shutdown with the window open then this is going to leave the data locked. Thanks very much, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
-Original Message- Does anyone know a good way of locking out access to a record on a MySQL database when someone has the update page open? The problem is that we have a local intranet site which is accessed by members of different departments. Now at the moment when someone loads the page all of the data is pulled into the form, and then when they submit, all of that data is passed back to the database, including anything they haven't changed. snip You should really rethink whether sending all those records is necessary, or wise. Is the convenience of mass updates really worth the headache? What we are trying to avoid is situations where for example someone leaves the form open on their computer and goes away for lunch. In the meantime someone else at a different station makes changes to the form. Then when the first person comes back from lunch, they submit the form, thereby writing over all of the second person's changes with the old data. snip My personal opinion on this: Any database that's so simple/small that it should have its entire contents updated every change, should only be managed by a single person. If that isn't an option, then the individuals should be modifying individual records instead of the entire recordset. This means a listing page, and selecting the record (or records, using checkboxes) to update. This is where you can easily set your locking, perhaps with a timestamp that will time out after X minutes, to prevent users overwriting. If neither of these are options, you have larger problems then you'll be able to properly solve in a web environment. Forcing the browser to re-display the page every minute [set the href explicitly, not reload, in case of post/etc.] is something that should at least reduce the chances of massive overwrite errors. Not that I particularly approve of that method :) It is, however, a relatively functional solution - if you're stuck with neither of the above as being options. - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Multi-User Update Problem
Yeah, all good thoughts. Someone else had a similar question that I answered recently (I think it was in private email) and in addition to the thoughts below, you might also look into recording WHEN the record was opened (and locked) so you can expire the lockout. I'd use this in conjunction with a browser side META REFRESH and a PRAGMA cache expire command so if the person who opened the record doesn't do anything with it within 2 hours, their browser auto-refreshes (maybe notifying them that their session was terminating and if they want to save the work they've done already) and it unlocks the file. You could also have a nightly job run that looks for all records locked over 2 hours ago or something and auto-unlock them (or just have the script that looks to see if something's locked see if it was over 2 hours ago and ignore the Locked == TRUE tag in the database). Anyway, the idea is to expire the session and user's cache so that they'll get a Warning: Page Expired making it impossible for them to update something after 2 hours has passed or something like that. Also to free up locked pages on a regular basis or allow people to get into them again if they time out. -TG -Original Message- From: Bastien Koert [mailto:[EMAIL PROTECTED] Sent: Monday, November 29, 2004 10:22 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Multi-User Update Problem Its a tough one. My personal fav is to mark the record as locked by changing a field called 'locked' from 0 to 1. Check for this when extracting the record and if is present, alert the user this record is locked (if you want to get fancy you could even track who locked it and the tell the user that) and show a non-updatable form of the page (either with the fields as readonly or by creating some kind of view only page where the data is simply echoed out). If you want to get REALLY out there, you could track who requested the locked record and notify them when it was released(by the original requestor submitting the form). The possibilities are endless The other option is to create/write an entire concurrency module that will store the record in a separate entity (another table or session object) and then compare any changes made upon submission. I have seen this but I don't really like it, since the ultimate choice is left to the user as to what data is correct. Bastien From: SCALES, Andrew [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [PHP-DB] Multi-User Update Problem Date: Mon, 29 Nov 2004 11:16:08 - Hello, Does anyone know a good way of locking out access to a record on a MySQL database when someone has the update page open? The problem is that we have a local intranet site which is accessed by members of different departments. Now at the moment when someone loads the page all of the data is pulled into the form, and then when they submit, all of that data is passed back to the database, including anything they haven't changed. What we are trying to avoid is situations where for example someone leaves the form open on their computer and goes away for lunch. In the meantime someone else at a different station makes changes to the form. Then when the first person comes back from lunch, they submit the form, thereby writing over all of the second person's changes with the old data. Does anyone know of a good way to solve or work around this problem? We have thought about setting a flag on the database whenever someone pulls down the data, however if their browser crashes or they shutdown with the window open then this is going to leave the data locked. Thanks very much, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
