ORA-00060:执行PLSQL函数时，在等待资源时检测到死锁

Question

我尝试在SQL中建模继承关系。Patron是Faculty的父类，下面是它们的定义:

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)

);

我使用下面的函数调用将数据插入到这些表中。

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;

执行上述过程有时会产生以下错误:

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.

这个错误并不总是发生。这个错误的原因是什么?

Answer 1

我们遇到了类似的问题。事实证明，PRIMARY KEY更多的是UNIQUE INDEX的约束，参见http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#i1006566。所以我们自己创建了索引:

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

并去掉了错误信息