-
update in trigger causes deadlock
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.
-
You could try changing this line:
Code:
...delete_counselors(:OLD.counselor_id);
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Just a hunch, but try taking out one of the commits. You should only need to do one commit. You might want to change this to an instead of trigger.
You may also want to read this.
-
Thanks for the reply.
More information: the database version is 8.1.7.4.
It seems that even putting the commit into a SP using pragma autonomous transaction, commiting in a trigger is not gonna work.
The error mesage is:
ORA-20101: Error in counselors_bef_del_row_2 - ORA-04092: cannot COMMIT in a trigger.
The trigger is on table dms.counselors, it has a primary key on counselor_id. The edsc_prd.users and dms.users have foreigh keys which reference the dms.counselors's column counselor_id.
The purpose of this trigger is trying to do:
before deleting a row in the parent table, update the rows in child tables which reference the to-delete counselor_id to null instead of deleting cascade. but getting the error:
ORA-20101: Error in counselors_bef_del_row_2 - ORA-20101: Error in
counselors_bef_del_row_2 - ORA-00060: deadlock detected while waiting for resource.
-
Originally posted by Lily_Liu_2004
Thanks for the reply.
More information: the database version is 8.1.7.4.
It seems that even putting the commit into a SP using pragma autonomous transaction, commiting in a trigger is not gonna work.
The error mesage is:
ORA-20101: Error in counselors_bef_del_row_2 - ORA-04092: cannot COMMIT in a trigger.
The trigger is on table dms.counselors, it has a primary key on counselor_id. The edsc_prd.users and dms.users have foreigh keys which reference the dms.counselors's column counselor_id.
The purpose of this trigger is trying to do:
before deleting a row in the parent table, update the rows in child tables which reference the to-delete counselor_id to null instead of deleting cascade. but getting the error:
ORA-20101: Error in counselors_bef_del_row_2 - ORA-20101: Error in
counselors_bef_del_row_2 - ORA-00060: deadlock detected while waiting for resource.
Have you tried updating the child table in the trigger and leave out the commits? That way either the whole transaction works or it doesn't. Also check to see if you have an update trigger on the child table. Maybe that is looking to see if the new child value has a parent, thus creating a deadlock.
-
There is only one trigger over there.
If I put the update and commit in the trigger body directly,
got the same error like cannot commit in a trigger.
if i put only the update without the commit, no error.
but it is not committed. from another session, I cannot see the changes.
-
You should have to commit on the statement where delete command is issued (Here after the delete command on dms.counselors which is the triggering statement), then only it will reflect to other users.
You cannot give commit inside the trigger.
-
Have you tried changing this to an after trigger? You only want this to work if the underlying transaction works. Even if you could get your code to work, there might be a case where the delete fails but the updates succeed.
-
yes, I agree with that the current design got problems.
It should be committed or rollbacked together.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|