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

Thanks to everyone for helping me out with this!

One of the replys noted that the only reason to use a table instead of a sequence would be to have sequential ids and this is indeed a requirement of our auditing systems.

So I have had to use a "FOR UPDATE" cursor and the initial testing I have done shows that it absolutely flys (bear in mind the current database is a Sybase box (which is imho marginally better than Access!).

Thanks again

andrew

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0407280948.7b4f06a6_at_posting.google.com>...
> 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 Thu Jul 29 2004 - 10:57:37 CEST