Re: How to lock a row over a SELECT followed by an UPDATE

webmaster_at_vbusers.com (Andrew Baker) wrote in message news:<c19b84e5.0407280224.3651abb_at_posting.google.com>...
> Thanks for the reply, but I think you may have miss-read the SQL (with
> hindsight I wasn't being very clear!). The first statement does a
> select into the store procs output parameter called "next_id_out":
>
> SELECT next_id
> INTO next_id_out
> FROM owner.my_id_table
> WHERE app_id = app_id_in;
>
> The next sql updates the table to move the next available Id on by
> one:
>
> UPDATE owner.my_id_table
> SET next_id = next_id_out + 1
> WHERE app_id = app_id_in;
>
> my problem is that this sp will be called v. intensively by lots of
> processes. So I think that the same Id could be returned twice unless
> I lock the row...
>
> thanks in advance
>
> andrew
>
[snip]
> > >
> > > I need to make sure that nothing changes the id table between me
> > > selecting the id and updating the table with the next available id.
> > >
> > > thanks
> > > andrew
> > >
> > > PS. yes I am new to oracle :)

Since you are new, you may not realize, you are reimplementing an ORACLE feature known as a SEQUENCE. A SEQUENCE will provide exactly what you need without the need to implement stored procedures which single thread processing by putting locks on resources. So instead of

  mystoredproc( next_id_out );

use the SEQUENCE as a pseudo-column on dual:

  select sequencename.nextval into next_id_out from dual ;

SEQUENCE advantages include:
fast returns of next value (no locking to slow things down) session independence (my value will never be the same as your value) application independent (each sequence is named so each can be used independently)
robust (it works for many oracle customers, why write your own?)

SEQUENCE, look for it in an ORACLE SQL Manual near you!

HTH,
  ed Received on Wed Jul 28 2004 - 19:48:38 CEST