What is the alternative to SELECT... FOR UPDATE in a subquery in Oracle?

Question

Oracle documentations state that FOR UPDATE clause is not supported in subqueries. I have two tables, Parent and Child. In the code, random records from the parent table are selected and the corresponding child records are updated. For example, the table structure might look like:

parent(obj_id string, status integer,....)
child(obj_id string, status integer, parent_id string,....)

and the query might look like:

UPDATE child 
   set status =1 
WHERE parent_id IN (SELECT obj_id 
                    FROM parent where status = 1 
                    and rownum < 10 FOR UPDATE SKIP LOCKED)

This query is executed from two different processes in two different machines.

Can you please explain how to extend the FOR UPDATE locking mechanism to work in subquery too?

Answer 1

You could use PL/SQL and run two separate queries:

CREATE TYPE numbers AS TABLE OF NUMBER(18);
/

DECLARE
  n numbers;
BEGIN
  SELECT obj_id
  BULK COLLECT INTO n
  FROM parent WHERE status = 1 AND rownum < 10
  FOR UPDATE SKIP LOCKED;

  UPDATE child
  SET status = 1
  WHERE parent_id IN (SELECT * FROM TABLE(n));
END;
/