Re: What to do with this situation?
From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/01
Message-ID: <3437ab24.24909688_at_newshost>#1/1
>These could happen within a very short period of time.
>What's should i do?
>
>Thanx in advance.
>
>
>Thenard
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 01 1997 - 00:00:00 CEST
Date: 1997/10/01
Message-ID: <3437ab24.24909688_at_newshost>#1/1
On Wed, 01 Oct 1997 17:56:23 GMT, Thenardier_at_POBoxes.com (ªü´ö Thenardier) wrote:
>
>I wanna know how Oracle & PL/SQL deals with a situation like this.
>And how to prevent this?
>
>Suppose there is a row:
>
>ProdID Name Qty
>------ --------------- -------
>P00001 Product ABC 100
>
>Now 2 users (A & B) are going to read from and update to it.
>
The way forms does this for example would be:
Time Action
------------- ------------------
1 A reads a row, A reads the rowid and the data (qty=100)
2 B reads a row, B reads the rowid and the data (qty=100)
3 A wants to modify the row. A will issue:
select *
from TABLE
where rowid = THE_ROWID_WE_READ
and qty = 100 and (other_cols = other_cols)
FOR UPDATE NOWAIT;
in short, we reread the row, locking it (for update)
at the same time verify that the data hasn't changed.
we use NOWAIT to make this non blocking in the event
the row was already locked by someone else.
4 B wants to modify the row. B will issue the same
query as above. B will get an error since the row
is already locked. B can tell his user to try again
later.
5 A successfully updates and commits
6 B runs the above query again. Instead of an
error, B gets ZERO rows returned. B can now
tell the user that the row has been changed and
they need to requery to see the new values before
updating.
>Time Sequence Users' Action
>---------------------------------------------------------
>1 A reads. Screen displays Qty = 100.
>
>2 B reads. Screen displays Qty = 100.
>
>3 A wants to update Qty with Qty = Qty - 50.
> A locks the table for update.
>
>4 B wants to update Qty with Qty = Qty - 20
> B tries to lock the table but fails.
>
>5 A has successfully updated the row.
> Now Qty = 50
> (But B's screen still displays = 100 and
> B still wants to update Qty = Qty - 20)!!!
>
>6 B locks the table for update.
> B updates Qty = Qty - 20. ie 100 - 20.
> Now Qty = 80 (not 50 anymore)
>
>These could happen within a very short period of time.
>What's should i do?
>
>Thanx in advance.
>
>
>Thenard
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 01 1997 - 00:00:00 CEST