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

From: <sybrandb_at_yahoo.com>
Date: 28 Jul 2004 01:03:20 -0700
Message-ID: <a1d154f4.0407280003.307de600_at_posting.google.com>

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 :)

declare
cursor my_date is

select SELECT	next_id + 1
FROM 	owner.my_id_table
WHERE	app_id = app_id_in

for update of next_id;
dummy number;
begin
open my_date;
fetch my_date into dummy;
update owner.my_table
set next_id = dummy
where current of my_date;
close my_date;
commit;
end;

This approach avoids two sessions select the same next_id. However, this approach will hamper concurrency, and this is the reason why Oracle implemented *sequences*. They don't need explicit locks.

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 28 2004 - 10:03:20 CEST