Re: Concurrency in Stored Procedure

Add

for update of total_cash
to your first statement.
The affected row(s) will now be locked until you issue your update and commit or rollback.

Hth,

Sybrand Bakker, Oracle DBA

"Michael Maddox" <mikemad_at_earthlink.net> wrote in message news:TjYj5.30710$Z6.816176_at_newsread1.prod.itd.earthlink.net...
> I have some code similar to the following in an Oracle Stored Procedure:
>
> Select TotalCash into v_TotalCash from Account Where Account ID = 100;
>
> ..... Some verification code
>
> Update Account Set TotalCash = v_TotalCash + p_AddedCash;
>
>
> I believe this is a concurrency issue because it's possible that after
> v_TotalCash is read, someone may update the value of TotalCash in the
> Account table by the time I get to my update command. Therefore, TotalCash
> would be wrong. This would be a better solution:
>
> Update Account Set TotalCash = TotalCash + p_AddedCash;
>
> My question is, is this the only way to resolve a concurrency issue like
> this on Oracle since I don't think it does any kind of read locking?
>
> Thanks for the help!
>
> Michael Maddox
>
>
>
>
>
>
Received on Tue Aug 08 2000 - 00:00:00 CDT