You can only get the behaviour you want by explicitly locking the child table in shared mode before locking the parent row.

It is an intentional feature of Oracle to avoid taking a shared lock over the child table if possible. The way it is done is that when a row of the parent table is updated or deleted, Oracle does a current mode get of the target index leaf block instead of a consistent get. If it finds an uncommitted change that would violate referential integrity if both transactions were to be committed, it then waits on the TX lock for that transaction in shared mode.

This only happens during the preparation of the update, and to prevent a race condition an exclusive buffer lock is held on the index leaf block until the change has been applied to the parent table and index blocks. That works because the same trick of doing a current mode get on the index leaf block is used against the primary key index of the parent table when doing an insert to the child table. Of course, because this functionality relies on buffer locks rather than row-level locks, and because the retention of buffer locks must be brief, it would not be feasible to use this mechanism to exclude conflicting DML against the related table. That is why taking a row-level lock against the parent table does not exclude conflicting DML against the child.

If there is no index on the foreign key, then none of this is possible, so a shared lock is taken over the child table instead. But once again, to reduce lock retention, because this is a very restrictive lock, locking is delayed until a change is applied.

I have found an example that surprised me, in which issuing a select for update on a master table does not ban to do an insert in the slave table (over the master key locked, of course).

Session 1:
----------

SQL> create table maestro (clave char(2), constraint pk_maestro PRIMARY KEY (CLAVE));

Table created.

SQL> create table esclavo (clave char(2), constraint pk_esclavo PRIMARY KEY (CLAVE),
  2                        CONSTRAINT FK_ESCKAVO FOREIGN KEY (CLAVE) REFERENCES MAESTRO);

Table created.

SQL> insert into maestro values ('a1');

1 row created.

SQL> commit;

Commit complete.

SQL> select clave from maestro where clave = 'a1' for update;

CL
--
a1


Session 2:
----------

SQL> insert into esclavo values ('a1');

1 row created.


Session 1:
----------

SQL> update maestro set clave = 'a3' where clave = 'a1';

This update waits. The question is, why doesn't the first session which has done a select for update have the right to update the record. The lock seems to be acquired by the insert instead? Is that established by the DBA in any way so we can modify the behaviour?