Reputation: 154
What is the best way to lock an individual row in the following scenario:
--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.
PS. yes I am new to oracle :)
Upvotes: 0
Views: 62
Reputation: 231891
You just need to add a FOR UPDATE
to your SELECT
SELECT next_id
INTO next_id_out
FROM owner.my_id_table
WHERE app_id = app_id_in
FOR UPDATE;
Of course, it doesn't make much sense to use a SELECT
to lock a row immediately before updating it. Just running the UPDATE
will lock the row.
Upvotes: 2