Select For Update Locks Joined Rows

Select for update locks the joined rows even thou columns from the table are not in the select list. The rows used in a predicate sub query are not locked. Here is an example. First preparing tables:

drop table bar;

drop table foo;

create table foo as select column_value i from table(sys.odcinumberlist(1,2,3,4));

alter table foo add constraint foo_pk primary key (i);

create table bar as select i,i j from foo;

alter table bar add constraint bar_pk primary key(j);

alter table bar add constraint bar_foo_fk foreign key(i) references foo;

Selecting for update. The foo table used in exists predicate is not locked.

select j from bar where j = 1 and exists (select 0 from foo where foo.i=bar.i) for update;

--only the row in BAR table is locked

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) BAR

rollback;

When joining is used, also the joined row in FOO table is locked

select bar.j from bar inner join foo on foo.i=bar.i where j = 1 for update;

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) FOO
--Row-X (SX) BAR

rollback;

Access Path Suggestor

Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.

with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
  from all_constraints fk, all_constraints pk
 where fk.r_owner= pk.owner
   and fk.r_constraint_name = pk.constraint_name
   and fk.constraint_type = 'R' 
), pths as (
 select o1,t1,constraint_name,o2,t2,'-<' dir
   from fks
 union all
 select o2,t2,constraint_name,o1,t1,'>-' dir
   from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
 select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
   from pths
 where o1 = :owner1
   and t1 = :table1
 union all 
 select s.o1,s.t1,s.constraint_name,s.o2,s.t2
      , s.dir,lvl+1
      , prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
  from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
 where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
 select lvl,cycle,pth
   from rcte
 where o2 = :owner2
   and t2 = :table2
  order by lvl,pth
;