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

I *highly* recommend using an Oracle sequence rather than a table to store this value. I cannot fathom of any business requirement by which you would need to use a table to generate incrementing ID's instead of a sequence (with the exception of a business mandate that ID's must never skip numbers).

Seriously, do not try to do this level of locking and releasing manually except as a last resort.

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 - 17:24:32 CEST