Hi,
This is a technique I first saw in the O'Reilly book
"Oracle Design". The idea is to use integers rather than
dates, and treat the integers as semaphores (or latches in
Oracle terminology). This technique is very portable
as it does not require date types.
Create an integer column called LOCKCNT in the table.
When retrieving your record, remember the last value of LOCKCNT
in lastlockcnt.
When updating, use
UPDATE table SET col1 = ??, ..., LOCKCNT = LOCKCNT + 1
WHERE key = ??? and LOCKCNT = lastlockcnt;
If the record has been modified by someone else, the UPDATE will
fail.
Regards, John
Dl Neil <[EMAIL PROTECTED]> wrote in message
05cf01c1acaa$19f774b0$c200a8c0@jrbrown">news:05cf01c1acaa$19f774b0$c200a8c0@jrbrown...
> 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 alw
ays
> > > 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]
