prevent 2 users update same row?

From: <mergler_at_my-deja.com>
Date: 2000/05/06
Message-ID: <8evqp0$8k9$1@nnrp1.deja.com>#1/1

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. Received on Sat May 06 2000 - 00:00:00 CDT