0

I tried to model inheritance relationship in SQL. Patron is the parent class of Faculty and these are their definitions.

CREATE TABLE Patron(
fname varchar2(25) NOT NULL,
lname varchar2(25) NOT NULL,
id number(10) NOT NULL,
status varchar2(25) NOT NULL,
country_name varchar2(50) NOT NULL,

CONSTRAINT fk_patron_nationality FOREIGN KEY (country_name) REFERENCES Nationality (country_name),
CONSTRAINT pk_patron PRIMARY KEY (id),
CONSTRAINT chk_status CHECK(status IN ('GOOD','BAD'))

);

CREATE TABLE Faculty (
category varchar2(25) NOT NULL,
id number(10) NOT NULL,

CONSTRAINT pk_faculty PRIMARY KEY (id),
CONSTRAINT fk_faculty FOREIGN KEY (id) REFERENCES Patron (id),
CONSTRAINT fk_faculty_category FOREIGN KEY (category) REFERENCES Faculty_Category (category)

);

I use the following function call to insert data into these tables.

CREATE or replace FUNCTION insert_faculty (fname in varchar2,lname in varchar2,id in number,
status in varchar2,country_name in varchar2,category in varchar2) return  integer 
is

pragma autonomous_transaction;
begin

insert into patron values (fname,lname,id,status,country_name);
commit;
insert into faculty values (category,id);
commit;

return 1;
end;

Executing the above procedure sometimes gives me the following error.

ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SMNATARA.INSERT_FACULTY", line 8
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
       involved. Retry if necessary.

This error doesn't happen always. What is the reason for this error?

Sekharan Natarajan
  • 41
  • 5
  • 1
    see this http://stackoverflow.com/questions/3074827/ora-00060-deadlock-detected-while-waiting-for-resource – Utsav Oct 23 '15 at 04:41
  • Can you post the block where you called the function? – brenners1302 Oct 23 '15 at 05:50
  • You really should "Look at the trace file to see the transactions and resources involved." Every deadlock error generates a file that includes the deadlocked objects and (usually) the queries that created the deadlock. Without that information we can only guess. – Jon Heller Oct 23 '15 at 23:00
  • This is the block I Used : select insertFaculty(args...) from dual; – Sekharan Natarajan Oct 24 '15 at 15:33
  • "If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur." Depending on what you did before calling the sproc, this might be the cause. Would also explain the "happens only sometimes" part. – randooom Oct 27 '15 at 13:46

1 Answers1

0

We had a similar problem. It turned out that a PRIMARY KEY is more a constraint with an UNIQUE INDEX, see http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566. So we created the indices on our own:

CREATE INDEX noDeadlock1 ON Patron (id);
CREATE INDEX noDeadlock2 ON Faculty (id);

and got rid of the error message.

mistapink
  • 1,926
  • 1
  • 26
  • 37
  • This doesn't make sense to me. I don't understand how this would help prevent a deadlock. Creating a primary key implicitly creates an index, why would it matter how the index was created? – Jon Heller Oct 23 '15 at 22:58
  • It doesn't need make sense to you, neither it does for me. It works for us, despite the fact we got primary keys. Actually I am wondering why the doc suggests to create an index though. – mistapink Oct 23 '15 at 23:23
  • 1
    If you don't know why it works then you shouldn't advise other people to do it. Maybe it never really worked in your system and the problem went away for some other reason. Can you create a test case to demonstrate this behavior? – Jon Heller Oct 24 '15 at 02:42
  • I took me a while to find this article again: http://www.oratechinfo.co.uk/deadlocks.html#unindex_fk. And our FKs are with a primary key. – mistapink Oct 30 '15 at 11:20