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

After thinking through swapping to using a sequence number I came up with a couple of potential gotchas:

1. When we fail over to our DR (disaster recovery) boxes the numbers must continue their sequence. Would a sequence number work under this circumstance?
2. When the box reboots/crashes does it continue the sequence number from where it was before the restart?
3. Can you manually tune the sequence numbers to increment by specified amounts?
4. Is a sequence number system wide? ie. I can it be table specific like IDENTITY columns in SQL server?

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 - 12:56:14 CEST