我吃西红柿,辰东全部小说

--創(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è)刪除語句等待

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;

您可能感興趣的文章:

Oracle 查詢死鎖并解鎖的終極處理方法
講解Oracle數(shù)據(jù)庫中結(jié)束死鎖進(jìn)程的一般方法
Oracle數(shù)據(jù)表中的死鎖情況解決方法
簡單說明Oracle數(shù)據(jù)庫中對(duì)死鎖的查詢及解決方法
oracle 會(huì)話死鎖執(zhí)行sql 執(zhí)行job的方法
Oracle刪除死鎖進(jìn)程的方法
Oracle對(duì)于死鎖的處理方法
Oracle 死鎖的檢測(cè)查詢及處理

標(biāo)簽：贛州蘇州來賓大興安嶺遼寧玉樹棗莊長沙

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle外鍵不加索引引起死鎖示例》，本文關(guān)鍵詞 Oracle,外鍵,不加,索引,引起,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。

濮阳杆衣贸易有限公司

Oracle外鍵不加索引引起死鎖示例