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

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 Tue Jul 27 2004 - 21:28:56 CDT