Hi Better to use an integer rather than a date field as 2 simultaneous transactions can still occur on the same second; most date time fields are accurate only to the nearest second.
Regards, John Frank Flynn <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Are you guys interested in yet another solution? It simular but I think > it's even easier if that's an incentive... > > -In your table do put a datetime field, I'll call it lastMod. This will be > the "last updated" date and time (you need time here) > > -In your form have this as a hidden field so it gets submitted with the rest > of the data. > > -When you receive the data to update the record you have I presume the > record ID in $id and the last modified date timein $lastMod. Create a new > $now with today's date and the current time > > Your SQL will look like: > > UPDATE Foo > SET col1 = $col1, > col2 = $col2, > col3 = $col3, > lastMod = $now > WHERE recordID = $id > AND lastMod = $lastMod > > As you can see the will succeed only if lastMod has not been changed. And > if it had been updated it will not fail (it's perfectly valid SQL) but will > not update any records. > > -Now to check if you did update the record... > > SELECT lastMod > FROM Foo > WHERE recordID = $id > > And compare this to your $now value. > > Or in MS SQL you can check the @@rowcount variable - (that's a MS SQL value) > it will be 1 - success or 0 - collision (if it's > 1 you've got duplicate > ID's). > > This has some disadvantage in that your end used could go through all the > effort to update a record only to have their update fail - I suggest putting > a nice error message and refresh the values in the form with the current > ones. > > It has the advantage of avoiding locking, setting flags, rolling back, you > only need to compare one field and more work for you - and it will always > work. > > Good Luck, > Frank > > On 2/1/02 12:42 PM, "[EMAIL PROTECTED]" > <[EMAIL PROTECTED]> wrote: > > > From: "Peter Lovatt" <[EMAIL PROTECTED]> > > Date: Fri, 1 Feb 2002 08:09:56 -0000 > > 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]
