Re: prevent 2 users update same row?

Hi.

 Try using either
  CURSOR ... FOR UPDATE; and UPDATE ... CURRENT OF <cursor>; instead   of SELECT FOR UPDATE or
 Use old values for verification:
  SELECT * FROM tbl WHERE id = 1;
  UPDATE tbl SET col1 = 'FOO-NEW-1', COL2 = 'FOO-NEW-2'    WHERE id = 1 AND col1 = 'FOO-OLD-1' AND col2 = 'FOO-OLD-2';   Check SQLCOUNT for number of updated rows ( if it's 0 - then   somebody else updated the row you are trying to update ).

 HTH. Michael.

In article <8evqp0$8k9$1_at_nnrp1.deja.com>,   mergler_at_my-deja.com wrote:
> The scenario I am trying to prevent is as follows:
>
> T1 User A 'SELECT * from TBL WHERE ID=1'
> T2 User B 'SELECT * from TBL WHERE ID=1'
> T3 User A 'UPDATE TBL SET COL1='FOO',COL2='FOO-TOO' WHERE ID=1' then
> COMMIT
> T4 User B 'UPDATE TBL SET COL1='FOO-FOO',COL2='' WHERE ID=1' then
> COMMIT
>
> T4 has just overwritten the update from T3. Any changes made by T3
 are
> gone. Is this a problem? Yes, according to my customer! :(
>
> To me this is quite an obscure occurence of events and I question the
> real need to prevent this (in our application, its not likely to
 happen
> unless you *try* to do it), but nevertheless we must prevent this.
>
> I have tried using sequences within triggers and using the SELECT FOR
> UPDATE clause and I have found that I cannot prevent T4 from occuring.
> I could be doing something wrong of course, but I would sincerely
> appreciate any insight, past experience, or voodoo and/or magic tricks
> that can prevent this scenario from occuring. Thanks!
>
> Regards,
> Jeff Mergler
> QSI Corporation
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat May 06 2000 - 00:00:00 CDT