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;