Hi all, yes there's some great ideas here, the one from Frank will work as
MS-SQL's datetime stamp uses microseconds I believe (I will check). JUST
CHECKED - NO it appears it uses only up to seconds, so an additional part
would be needed.
I was still a little confused but including the SQL (using where ID AND ...)
has cleared up that side of things for me as I was thinking along the lines
of using SQL IF statements.
The motivation is both ease of coding and performance hit onthe server (to
my mind the simplest solution will probably be the easiset on the ser(s)
too). I'm using a 2 tier system (although I'm developing on single tier!)
so I do have some performance available, but I don't want to kill anything
off!
Cheers this would have taken me forever (possibly never) without your help!
Any further suggestions that you can come up would be most appreciated.
Thanks very much
Ollie
Everyone else's messages:
> This is good brain-storming guys!
> 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.
Good point, John.
However MySQL (AFAIK) does not have any time functions returning values less
than one second. So are you
proposing to use PHP's microtime function? That being the case, the two
components (seconds and micro-secs)
would have to be added together. Also the field would either have to be
changed to accept a floating-point
value, or the combined number multiplied up to be integral microseconds (of
the Unix epoch).
> > Are you guys interested in yet another solution? It simular but I think
> > it's even easier if that's an incentive...
=always interested in constructive suggestions - I've learned from this
conversation - as well as enjoying the
challenge/getting the brain cells to all march around in the same
direction...
> > -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).
=if using MySQL then MySQL_affected_rows() would be more efficient - no call
to the RDMBS.
=Unfortunately if this database requires an additional SELECT, then the
solution requires a SELECT (to populate
the form), and UPDATE (attempt), and a second SELECT to confirm the UPDATE.
This is exactly the same 'cost' as
earlier suggestions. (ignoring the UPDATE-clash situation which will have
the same effect/cost in all cases)
> > 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.
=which is the accepted fate of all of these suggestions - and the inevitable
(if very occasional) impact of
multi-user systems - always assuming that such a fact is of major interest
to the user!!!
> > 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.
=So given all of the questions above, I wondered why use an (extra) time
field at all?
=I don't think that the original question mentioned how many fields were
being updated by the form interaction
(please correct me if I'm wrong), but I think the outline above could be
applied reasonably effectively, even if
several fields were being updated (and it MATTERED to the user if some other
user updated one or more,
meantime).
=Borrowing from Frank's code:
UPDATE Foo
SET col1 = $col1,
col2 = $col2,
col3 = $col3
WHERE recordID = $id
AND col1 = $col1ValueFromSelect,
AND col2 = $col2ValueFromSelect,
AND col3 = $col3ValueFromSelect
=This will complete without incident if the row has been untouched between
the initial SELECT and the UPDATE,
and will fail if any UPDATE meantime has affected one of the pertinent data
fields. NB any UPDATE that does
affect the subject-row, but does NOT affect the subject fields, will not
trigger a spurious/erroneous 'alert'
response - if such a thing were possible in the original scenario.
=The operation will need to be followed up by another SELECT (if that's what
MS-SQL demands) or the equivalent
of a MySQL_affected_rows() [as discussed above]. As before, this result
sends the user back to the form, or
assures all that the database integrity is good.
=The 'cost' remains at two SELECT statements and one UPDATE, but (data
characteristics permitting - and assuming
no one can spot anything missing) seems not to introduce the possibility of
a spurious 'alert' and appears more
'elegant'/self-documenting.
=Regards,
=dn
> > > 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.
> > >> 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.
--
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]
