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.
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
> 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
> 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
> 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
> it will be 1 - success or 0 - collision (if it's > 1 you've got duplicate
> 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
> a nice error message and refresh the values in the form with the current
> 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
> Good Luck,
> 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
> > question)
> > Hi
> > Interesting problem! Systems would be so much easier to build if we
> > 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
> > 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
> > edit. For example if the record was opened 3 mins ago, and the timeout
> > the user2 gets a message saying 'Record in use try again in 2 minutes'
> > was opened 6 minutes ago set the owner of the locked record to user2,
> > reset the timestamp.
> > If / when user1 submits, refuse the update, and inform user1, and
> > 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.
> > before timeout and warns user1 to save (update record and reload for
> > editing)?
> > Probably more hassle than its worth, but you could also take a snapshot
> > 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
> > 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
> >> Win32 and MS-SQL. This system needs to be scalable but more
> >> 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
> >> user could also be making changes to the same record and if they post
> >> before the other one they could overwite each others changes. For
> >> 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
> >> 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
> >> 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
> >> time the row was modified?? */
> >> START TRANSACTION $tranname
> >> GO
> >> ";
> >> But that probably won't work thinking about it (and looking at the
> >> 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
> >> own? - I very much doubt that last comment!)
> >> Any help would be most appreciated, I don't need all of the PHP code
> >> 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]