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

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;

     UPDATE owner.my_id_table 
     SET next_id = next_id_out + 1
     WHERE app_id = app_id_in;

andrew

rolympia_at_hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0407271828.1da3ac2_at_posting.google.com>...
> Unless you will do some other procedural processing or checking
> between your SELECT and your UPDATE (w/c you did not include in your
> code sample), then you could just use one statement:
>
> UPDATE owner.my_id_table
> SET next_id = next_id + 1
> WHERE app_id = app_id_in;
>
> Otherwise, check out "FOR UPDATE" in the manuals: Concepts, or SQL
> Guide, or Application Developer's Guide (Fundamentals). But I really
> think the above would work for your purposes.
>
> HTH.
>
> webmaster_at_vbusers.com (Andrew Baker) wrote in message news:<c19b84e5.0407271054.54ae108a_at_posting.google.com>...
> > What is the best way to lock an individual row in the following
> > scenerio:
> >
> > --TODO - LOCK THIS ROW
> > -- Return the next id
> > SELECT next_id
> > INTO next_id_out
> > FROM owner.my_id_table
> > WHERE app_id = app_id_in;
> >
> > -- Update the next id on the table
> > UPDATE owner.my_id_table
> > SET next_id = next_id_out + 1
> > WHERE app_id = app_id_in;
> >
> > 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 :)
Received on Wed Jul 28 2004 - 12:24:22 CEST