Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: question about triggers causing deadlock

    I have created row level BEFORE triggers for table1 that lock a record in another semaphore table2

    create trigger table1_ins
    before insert on table1
    for each row
    declare
    begin
    select * from table2 where id = new.id for update
    end;

    create trigger table1_upd
    before update on table1
    for each row
    declare
    begin
    select * from table2 where id = old.id for update
    end;

    create trigger table1_del
    before delete on table1
    for each row
    declare
    begin
    select * from table2 where id = old.id for update
    end;

    So after insert/update/delete, both changed row and the semaphore will be locked until commit.

    however, if there are a lot concurrent transaction like:
    begin TX
    delete from table1 where id = 1
    insert into table2 (id) values (1)
    commit TX
    deadlock will happen.

    So I wonder whether the semaphore lock is always be acquired BEFORE row lock, if so, that deadlock could not happen in theory.

    Any suggestion or ideas to explain the problem? Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: question about triggers causing deadlock

    I don't quite follow your reasoning - I don't see any deadlock issues here. A deadlock is where user1 has locked A and is waiting to lock B, and user2 has locked B and is waiting to lock A.

    With your triggers, if 2 people try to update the same row in table1 at the same time, then one will be blocked by the other until the other commits or rolls back. This is true even without your triggers - in fact your triggers appear to be duplicating the functionality Oracle already provides.

  3. #3
    Join Date
    Nov 2003
    Posts
    16

    Re: question about triggers causing deadlock

    Originally posted by andrewst
    I don't quite follow your reasoning - I don't see any deadlock issues here. A deadlock is where user1 has locked A and is waiting to lock B, and user2 has locked B and is waiting to lock A.

    With your triggers, if 2 people try to update the same row in table1 at the same time, then one will be blocked by the other until the other commits or rolls back. This is true even without your triggers - in fact your triggers appear to be duplicating the functionality Oracle already provides.
    Thanks for look into this issue.
    you are right - from the source i also do not see any cause for deadlock. but it happened beyond expectation.
    The fact that i added extra lock to a semaphore record in table2 is actually part of an algorism in an in-house java framwork that providing transparent cache service to java applications.

    I spent some time to code a demo so that if you interest, you can open 2-3 sqlplus session and run SP "runTX" to observe the strange deadlock that i can not explain. Since I am still a junior oracle developer (may be semi senior , I do expect someone can help me to work out the reason.

    create table t1 (
    id varchar2(10),
    value varchar2(10) );

    create table t2 (
    id varchar2(10)
    );

    create trigger t1_ins
    before insert on t1
    for each row
    declare
    locked varchar2(10);
    begin
    select id into locked from t2 where id = :new.id for update;
    end;
    /
    create trigger t1_upd
    before update on t1
    for each row
    declare
    locked varchar2(10);
    begin
    select id into locked from t2 where id = :new.id for update;
    end;
    /
    create trigger t1_del
    before delete on t1
    for each row
    declare
    locked varchar2(10);
    begin
    select id into locked from t2 where id = ld.id for update;
    end;
    /

    insert into t2 (id) values ('100');
    insert into t1 (id, value) values ('100', 'boo');
    commit;

    drop procedure tx;
    create procedure tx
    IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    delete from t1 where id = '100';
    insert into t1 (id, value) values ('100', 'boo');
    commit;
    EXCEPTION
    when others then
    dbms_output.put_line(SQLERRM);
    END tx;

    /
    drop procedure runTx;
    create procedure runTx
    IS
    cnt number;
    BEGIN
    cnt := 10000;
    loop
    exit when cnt <= 0;
    cnt := cnt - 1;
    tx;
    end loop;
    END runTx;
    /

  4. #4
    Join Date
    Nov 2003
    Posts
    16

    Re: question about triggers causing deadlock

    I found sometimes we need more concurrent sessions to reproduce the problem, i used 4 sessions and each session run the Tx 10000 times to reproduce it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •