update in trigger causes deadlock
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: update in trigger causes deadlock

  1. #1
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    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

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.
    this space intentionally left blank

  4. #4
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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.

  7. #7
    Join Date
    Apr 2003
    Posts
    353
    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.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.
    this space intentionally left blank

  9. #9
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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
  •  


Click Here to Expand Forum to Full Width