--創(chuàng)建一個(gè)表，此表作為子表 

create table fk_t as select *from user_objects; 

delete from fk_t where object_id is null; 

commit; 

--創(chuàng)建一個(gè)表，此表作為父表 

create table pk_t as select *from user_objects; 

delete from pk_t where object_id is null; 

commit; 

--創(chuàng)建父表的主鍵 

alter table PK_t add constraintpk_pktable primary key (OBJECT_ID); 

--創(chuàng)建子表的外鍵 

alter table FK_t addconstraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID); 

--session1:執(zhí)行一個(gè)刪除操作，這時(shí)候在子表和父表上都加了一個(gè)Row-S(SX)鎖 

delete from fk_t whereobject_id=100; 

delete from pk_t where object_id=100; 

--session2:執(zhí)行另一個(gè)刪除操作，發(fā)現(xiàn)這時(shí)候第二個(gè)刪除語(yǔ)句等待 

delete from fk_t whereobject_id=200; 

delete from pk_t whereobject_id=200; 

--回到session1:死鎖馬上發(fā)生 

delete from pk_t whereobject_id=100; 

session2中報(bào)錯(cuò)： 

SQL> delete from pk_table where object_id=200; 
delete from pk_table where object_id=200 
* 
第 1 行出現(xiàn)錯(cuò)誤: 

ORA-00060: 等待資源時(shí)檢測(cè)到死鎖 

當(dāng)對(duì)子表的外鍵列添加索引后，死鎖被消除，因?yàn)檫@時(shí)刪除父表記錄不需要對(duì)子表加表級(jí)鎖。 

--為外鍵建立索引 

create index ind_pk_object_id on fk_t(object_id) nologging; 

--重復(fù)上面的操作session1 

delete from fk_t whereobject_id=100; 

delete from pk_t whereobject_id=100; 

--session2 

delete from fk_t whereobject_id=200; 

delete from pk_t whereobject_id=200; 

--回到session1不會(huì)發(fā)生死鎖 

delete from pk_t whereobject_id=100;