CODEWITHSUNDEEP
oracle-databaselocking
iprashant7
iprashant7

Reputation: 154

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

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

Answers (1)

Justin Cave
Justin Cave

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

Related Questions