ORA-04020: deadlock detected while trying to lock object ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-04020: deadlock detected while trying to lock object ...

Hybrid View

  1. #1
    Join Date
    Jul 2001
    Location
    Milano
    Posts
    22

    ORA-04020: deadlock detected while trying to lock object ...

    Hi all,
    I'm working with a JSP/Java application that have a Database Oracle 8i as back-end.

    I encountered the "ORA-04020: deadlock detected while trying to lock object ..." error when I call my Stored Procedures the first time that I accede in my JSP application when it's a few time that I don't work with him. Then, if I retry all work fine! Not only for the procedure that gives me the error, but for every Stored Procedure of my schema. It isn't important which stored Procedure I call as first, the first gives me the error, the other ones work fine....
    This is the situation of my DB Schema:


    I have two table:

    --------------------------------------------
    Account table
    --------------------------------------------
    CREATE TABLE ACCOUNT (
    ID_ACCOUNT NUMBER(12) NOT NULL,
    USERNAME VARCHAR2(24),
    PASSWORD VARCHAR2(24),
    NAME VARCHAR2(64) NOT NULL,
    CONSTRAINT UK_ACCOUNT_USERNAME
    UNIQUE (USERNAME) USING INDEX
    TABLESPACE USERS PCTFREE 10
    STORAGE ( INITIAL 12K NEXT 12K PCTINCREASE 0 ),
    CONSTRAINT PK_ACCOUNT
    PRIMARY KEY ( ID_ACCOUNT )
    USING INDEX
    TABLESPACE USERS PCTFREE 10
    STORAGE ( INITIAL 12K NEXT 12K PCTINCREASE 0 ));

    --------------------------------------------
    Address ip_range
    --------------------------------------------

    CREATE TABLE IP_RANGE (
    ID_IPRANGE NUMBER(12) NOT NULL,
    IP_ADDRESS VARCHAR2(16) NOT NULL,
    ID_ACCOUNT NUMBER(12) NOT NULL,
    CONSTRAINT UK_IPRANGE_IDACC_IPADDR
    UNIQUE (ID_ACCOUNT, IP_ADDRESS) USING INDEX
    TABLESPACE USERS PCTFREE 10
    STORAGE ( INITIAL 12K NEXT 12K PCTINCREASE 0 ),
    CONSTRAINT PK_IPRANGE
    PRIMARY KEY ( ID_IPRANGE )
    USING INDEX
    TABLESPACE USERS PCTFREE 10
    STORAGE ( INITIAL 12K NEXT 12K PCTINCREASE 0 ));

    ALTER TABLE IP_RANGE ADD CONSTRAINT FK_IPRANGE_ACCOUNT
    FOREIGN KEY (ID_ACCOUNT)
    REFERENCES GIUSTI.ACCOUNT (ID_ACCOUNT) ;

    CREATE OR REPLACE TRIGGER iprange_bifer BEFORE INSERT ON ip_range FOR EACH ROW
    DECLARE
    newid INTEGER;
    BEGIN
    IF :new.id_iprange IS NULL THEN
    SELECT iprange_seq.nextVal INTO newid FROM DUAL;
    :new.id_iprange := newid;
    END IF;
    END;


    Then I have several procedure as, ins_account, mod_account, del_account ecc. This is an example of a procedure that give me the problem.

    PROCEDURE del_account (id_account_num IN ACCOUNT.ID_ACCOUNT%TYPE)
    IS
    procName varchar2(50) := pckName||'Del_Account';
    BEGIN
    -- Delete corresponding record in the table IP_RANGE --
    DELETE FROM ip_range ir WHERE ir.id_account = id_account_num;
    DBMS_OUTPUT.PUT_LINE ('Delete in table IP_RANGE occurs succesfully. Not committed.');

    -- Delete corresponding record in the table ACCOUNT --
    DELETE FROM account a WHERE a.id_account = id_account_num;
    DBMS_OUTPUT.PUT_LINE ('Delete in table ACCOUNT occurs succesfully. Not committed.');

    COMMIT;
    DBMS_OUTPUT.PUT_LINE ('Commit ok!');
    EXCEPTION WHEN others THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, 'Error on: '||procName||' - SQLERRM: '||sqlerrm);
    END del_account;

    I call this procedure from a JSP that use a typical OracleJDBC Java class with a Connection = DriverManager.getConnection("jdbcracle:thin:...")

    Can any one help me, please,
    MaX

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you understand what a deadlock is?


    Also, not related to this subject, the trigger would be better as ...
    Code:
    CREATE OR REPLACE TRIGGER iprange_bifer BEFORE INSERT ON ip_range FOR EACH ROW
    WHEN (new.id_iprange IS NULL)
    BEGIN
    SELECT iprange_seq.nextVal INTO :new.id_iprange FROM DUAL;
    END;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2001
    Location
    Milano
    Posts
    22
    Thanks for the improvements of the code of my trigger!

    About deadlock, I think that I understood what is a deadlock, but I cannot understand why I have a deadlock with the simply code that I had written...

    Do you have any idea?

    MaX

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