I know usually presented classic scenario for deadlocks and somehow understand it:
session 1: delete from emp where empno=1; -- session 1 locks row empno=1
session 2: delete from emp where empno=2; -- session 2 locks row empno=2
session 1: update emp set sal=2 where empno=2; -- session 1 tries to lock row empno=2 and waits for session 2
session 2: update emp set sal=1 where empno=1; -- session 2 tries to lock row empno=1 and waits for session 1
/* deadlock detected */
But I have a problem with understanding what causes deadlock in this scenario. First I create procedure with pragma autonomous_transaction.
create or replace procedure p_emp_aut ( v_empno emp.empno%type, v_ename emp.ename%type, v_mgr emp.mgr%type, v_deptno emp.deptno%type ) is
pragma autonomous_transaction;
begin
insert into emp (empno, ename, mgr, deptno) values (v_empno, v_ename, v_mgr, v_deptno);
commit;
end;
There is constraint defined on emp.deptno as foreign key to dept.deptno, and this constraint is enabled. Now I do the following:
-- this inserts into dept and holds lock on inserted row (?) until committed or rollbacked
insert into dept (deptno, dname, loc) values (1,'dname1','loc1');
-- this fires procedure in autonomous transaction
exec p_emp_aut(1,'emp1',7900,1);
/* deadlock detected */
My understanding is that foreign key constraint causes checking for value 1 in dept.deptno. But because insert into emp is inside autonomous transaction it cannot yet see the 1 value inserted into dept. OK, so it looks like constraint violation... But why do I see deadlock as a result?
I would appreciate explanation.
Thank you