you are updating the primary key there. you are updating every column.
ops$tkyte%ORA10GR2> create table p ( x int primary key, y int );
Table created.
ops$tkyte%ORA10GR2> create table c ( x references p, y int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into p values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> insert into p values ( 2, 1 );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into c values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> insert into c values ( 1, 1 );
1 row created.
ops$tkyte%ORA10GR2> declare
2 pragma autonomous_transaction;
3 l_rec p%rowtype;
4 begin
5 select * into l_rec from p where x = 2;
6 update p set row = l_rec where x = 2;
7 commit;
8 end;
9 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 6
you will want to index those foreign keys to prevent the full table lock on the child.