DBAs,

We consistently got a deadlock from one application. I reviewed their codes and the alert log. I guess it is caused from the update inside one trigger which calls a procedure to update a table.. If it is the case, how to fix it?

The following is alert log and the scripts:

*** 2005-04-05 20:51:08.465
*** SESSION ID:(426.57980) 2005-04-05 20:51:08.438
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE EDSC_PRD.USERS SET COUNSELOR_ID= NULL ,PERSONAL_INFO= NULL WHERE COUNSELOR_ID = :b1

CREATE OR REPLACE TRIGGER dms.counselors_bef_del_row
before delete on dms.counselors for each row
declare
error_num number;
error_msg varchar2(100);
begin
delete_counselors(ld.counselor_id);
exception
when others then
error_num := sqlcode;
error_msg := substr(sqlerrm, 1, 100);
raise_application_error(-20101, 'Error in counselors_bef_del_row_2 - ' || error_msg);
end;

CREATE OR REPLACE procedure dms.delete_counselors(v_counselor_id in varchar2) as
pragma autonomous_transaction;
error_num number;
error_msg varchar2(100);
begin
update edsc_prd.users set counselor_id = null, personal_info = null where counselor_id = v_counselor_id;
commit;
update dms.users set counselor_id = null where counselor_id = v_counselor_id;
commit;
exception
when others then
error_num := sqlcode;
error_msg := substr(sqlerrm, 1, 100);
raise_application_error(-20101, 'Error in counselors_bef_del_row_2 - ' || error_msg);
end;

Any input is welcome.


Thanks.