-
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
-
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;
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|