Hi I don't think there can be a perfect solution, because you don't have a true persistent session between the user and the system.
For the timeout watch a real user (or time yourself) and see how long it takes to do the update. If locking user2 out for any length of time causes problems go for a timeout close to the time it takes, and risk upsetting user1 sometimes. If user2 can easily go and do something else for five minutes set the timeout at 2 or 3 times the measured update time to allow for the slowest one finger typist! You could also log the system in use to see how many times user1's update gets refused, and adjust the timeout accordingly. I use a timeout system for restricted access user sessions, so I have most of the code you need, if you would like it. email me off list if you would like it. HTH Peter ----------------------------------------------- Excellence in internet and open source software ----------------------------------------------- Sunmaia www.sunmaia.net tel. 0121-242-1473 ----------------------------------------------- > -----Original Message----- > From: Oliver Cronk [mailto:[EMAIL PROTECTED]] > Sent: 01 February 2002 08:30 > To: Peter Lovatt; [EMAIL PROTECTED] > Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a > newbie question) > > > Thanks for that answer, you filled in some of the blanks for the table / > logging solution, but I am now looking at row locking instead of > a seperate > table (and then doing things similar to what you outlined). > > The main problem is the darn timeout - how long should it be etc? And if I > use row locking and don't unset the locks / use a timeout then the db will > get completely locked up if the user doesn't update I would imagine! > > Darn users!! This script is complicated enough without this!! > > Cheers > > Ollie > > -----Original Message----- > From: Peter Lovatt [mailto:[EMAIL PROTECTED]] > Sent: 01 February 2002 08:10 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a > newbie question) > > > > Hi > > Interesting problem! Systems would be so much easier to build if we didn't > have to allow for users :) > > Two suggestions, depending on how you want the data dealt with. > > A table of rows in use, with a time stamp and an owner. When > user1 opens the > record, stamp it with owner and time. If user2 wants to use the record, > check when it was 'locked' and apply a timeout based on how long > it takes to > edit. For example if the record was opened 3 mins ago, and the > timeout is 5, > the user2 gets a message saying 'Record in use try again in 2 > minutes' If it > was opened 6 minutes ago set the owner of the locked record to user2, and > reset the timestamp. > > If / when user1 submits, refuse the update, and inform user1, and whatever > handing you need after that. > > If no user2 has tried to open the record, then user1 can still submit, > because they still own it, even if there is a timeout. > > If you are feeling flash maybe a JavaScript timer that pops up 1 minute > before timeout and warns user1 to save (update record and reload for more > editing)? > > Probably more hassle than its worth, but you could also take a snapshot of > the data, when user1 starts, and if more than one user tries to edit the > record, save the updates in a temp table, compare the updated record with > the original snapshot, and do some sort of intelligent amalgamation. > > HTH > > Peter > > > > -----Original Message----- > > From: Oliver Cronk [mailto:[EMAIL PROTECTED]] > > Sent: 31 January 2002 23:09 > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Ensuring users don't overwrite each other (NOT a > > newbie question) > > > > > > > > Hi there, currently writing an e-CRM system for an intranet using PHP on > > Win32 and MS-SQL. This system needs to be scalable but more importantly > > there will be anything up to 400 users (unlikely, but the max > > amount) using > > the same records (updating information about customers etc) and I > > worry that > > whilst one user has a form open (via one of my PHP scripts) that another > > user could also be making changes to the same record and if they post it > > before the other one they could overwite each others changes. For info: > > database is normalised to 3NF so that side of things should be okay. > > > > I have thought of a couple of solutions: > > > > Row Locking when a user has a record - and if another user wants > > to use that > > record PHP tells them its in use. But if the forst user > doesn't make any > > changes how will the db know to unlock the row and there might be > > potential > > deadlock issues. Also I'm not sure of the SQL for row locking > > (do you use a > > SELECT with a ROWLOCK hint?). > > > > Another idea was to have a log or temp table - that would get > written into > > when ever some opens a record but this has the same issues as the first > > solution I think. > > > > An another idea is T-SQL and transactions but I'm not sure if that will > > solve the problem (and I've never used T-SQL before - therefore > > I'm not sure > > of its capabilities) > > eg: > > When the script is started by the first user (to bring up the existing > > record) perhaps a transaction is started (if they can persist between > > batches?): > > > > $tranname = "@tran".$id; > > $sqlstr = "TRANSACTION $tranname > > > > SELECT rows from CASES > > WHERE id = $id > > GO > > > > /* maybe find the date / time from a system table sp_something > of the last > > time the row was modified?? */ > > > > START TRANSACTION $tranname > > GO > > "; > > > > But that probably won't work thinking about it (and looking at > the stupid > > senseless code I have written above!!!!) The transcation probably > > need to be > > around the update SQL doesn't it? And then do a rollback if it finds > > another user has updated lately? And then reload the data and > > send it back > > to the form for the user to check (then they can update - after > > checking the > > other users data?) > > > > Anybody have a solution /views on this? Anybody had to fix a similar > > problem? Or is all this paranoia (will the DB handle this problem on it > > own? - I very much doubt that last comment!) > > > > Any help would be most appreciated, I don't need all of the PHP > code just > > the concepts will do (I have been using PHP/MS-SQL for a while) or some > > example T-SQL if you think thats the solution I should go for. > > > > Thanks very much in advance... > > > > Oliver Cronk > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
