Re: prevent 2 users update same row?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/06
Message-ID: <8f1lbs$3vm$1@nnrp1.deja.com>#1/1

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.
>

It is hardly obscure, many books on databases devote chapters to this issue -- it is known as the "lost update" issue.

Oracle Forms handles it this way:

• when you query the data, it selects the data AND the rowid of the data.
• when the user attempts to UPDATE the data on the screen (before they change the first byte), forms IMMEDIATELY goes to the database and issues:

   for i in 1 .. 3 loop

      select * from T where rowid = :ThatRowid
         and field1 = :field1_as_we_selected_it
         and field2 = :field2_as_we_selected_it
         (and all of the other fields we selected)
      FOR UPDATE NOWAIT;

      if ( 1 row got selected ) RETURN SUCCESS;

      if ( 0 rows got selected ) RETURN "Sorry, record changed, please

requery"

   end loop

   return "Sorry, record is currently locked, try again later"

That is -- it tries three times to lock the record, if the record changed (0 rows returned) it returns that message. If the record is locked and remains locked, it returns that message -- else it returns the fact that the record is OK and you have locked it.

This is "pesimistic" locking. It locks the record early in the transaction. Others can query it, no one else can lock it. We "own" that record. We are assured that when we goto make our changes in the database -- we will be able to. It is called pesimistic not because its "bad" but because it assumes that someone else will try to come along and change the row whilst we are working on it (its a pesimist) and locks the row at the earliest moment to prevent that.

On the other hand, you can do something like this as well (this is more akin to "optimistic" locking - you assume the row will not change but are prepared to tell the user if it does).

• you would select out the data AND the primary key (or rowid).
• you would always RETAIN these original values -- we'll need them in the update
• when you goto update, you would issue:

     update T
        set field1 = :new_value_for_field1,
            field2 = :new_value_for_field2,
            ....
      where rowid = :the_original_rowid_we_selected_for_this_row
        and field1 = :the_orig_val_of_field1_we_selected
        and field2 = :the_orig_val_of_field2_we_selected
        and (so on)


     if ( sql%rowcount = 0 ) then
        Sorry - row changed since you selected, no data changed, YOU
LOSE
     else
        Row updated
     end if;





--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat May 06 2000 - 00:00:00 CDT