oracle 外键无索引会导致阻塞 甚至死锁 的根本原因

通过10046事件分析外键无索引导致阻塞

--为什么外键没有索引会导致阻塞 甚至死锁

测试

create table t1

(id number,

name varchar2(1)

);

alter table t1 add constraint pk_t1 primary key (id);

create table t2

(id number,

t1_id number,

name varchar2(1)

);

alter table t2 add constraint pk_t2 primary key (id);

alter table t2 add constraint fk_t2 foreign key (t1_id) references t1(id);

insert into t1 values(1, 'a');

insert into t1 values(2, 'b');

insert into t2 values(1, 1, 'c');

--会话1 开启10046trace分析

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered

--子表行被锁住

SQL> delete from t2 where id=1;

1 row deleted

--先暂停 去执行会话2

SQL> commit;

Commit complete

--关闭10046

SQL> alter session set events '10046 trace name context off';

Session altered

--查看trc文件目录

SQL> select * from v$diag_info where name like 'Default%';

会话2

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered

--在会话1 中锁住了t2 ,会话2因为t2是子表，也必须占有t2表，就锁住了，表这个时候会阻塞

SQL> delete from t1 where id=1;

1 row deleted

SQL> commit;

Commit complete

SQL> alter session set events '10046 trace name context off';

Session altered

SQL> select * from v$diag_info where name like 'Default%';

分析：会话2阻塞，所以看一下会话2的10046

根据上面查到的路径 linux下执行

tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_833.trc session2.txt sys=no

会在当前目录下生成session2.txt，里面有详细信息，因为涉及明细，所以不需要用tkprof 命令转化成session2.txt，直接看orcl_ora_833.trc即可，结果如下，会话2虽然删除t1表的数据，但是还是会去占用子级表t2，图中是会话2执行计划，用全表扫描，全表就被锁了，如果有外键索引，执行计划走的是索引扫描，就不会导致全表锁定，而是只锁定修改行，这时候t2的该行被会话1持有，还没释放，所以会话2会一直阻塞，直到会话1提交或者回滚，这时候会话2对t2是想要全表独占，如果这时候有外键索引，会话2对t2就不是全表独占，而是只占某些要跟新的行。从而不影响其他会话使用表的其他行，

特别：如果先删除主表的数据，主表要删除的数据在子表没有，且子表没被锁定时，那么删除主表这条记录后，不会锁定子表了，因为这时候子表没有被锁定，（我子表都没你主表的记录，你凭什么锁定我！！哈哈）

如果先删除子表的数据，这时候再删除主表的数据，即使删除的主表数据在子表没有值，会话也会被阻塞。因为多了先删除子表的数据的操作，子表数据已被锁定了（建外键索引解决）

总之，外键加索引是常识，原理是避免全表被独占，如下图官方文档。

原文地址：

https://blog.csdn.net/xeseo/article/details/11138305

无外键索引时，子表全被锁定

Share lock acquired 是独占全表的锁

有外键索引时，只有锁定子表的一行

详细参考

https://blog.csdn.net/xeseo/article/details/11138305 https://blog.csdn.net/chen1511XIQIANG/article/details/70213584

https://www.2cto.com/database/201603/494737.html

Oracle编程艺术深入数据库体系结构（第二版）第六章

附件

查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）

找出在具体的外键字段是否有索引，脚本如下所示：

--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）

SELECT DC.OWNER AS "PARENT_TABLE_OWNER",

DC.TABLE_NAME AS "PARENT_TABLE_NAME",

DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",

DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",

DF.OWNER AS "CHILD_TABLE_OWNER",

DF.TABLE_NAME AS "CHILD_TABLE_NAME"

FROM DBA_CONSTRAINTS DC,

(SELECT C.OWNER,

C.CONSTRAINT_NAME,

C.R_CONSTRAINT_NAME,

C.TABLE_NAME

FROM DBA_CONSTRAINTS C

WHERE CONSTRAINT_TYPE = 'R') DF

WHERE DC.CONSTRAINT_NAME =DF.R_CONSTRAINT_NAME

AND DC.OWNER NOT IN ( 'SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS',

'ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS',

'SYSMAN' );

--找出在具体的外键字段是否有索引，脚本如下所示：

SELECT CON.OWNER ,

CON.TABLE_NAME,

CON.CONSTRAINT_NAME,

CON.COL_LIST,

'No Indexed' AS INDEX_STATUS

FROM

(SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,

MAX(DECODE(POSITION, 1, '"' ||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 2,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 3,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 4,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 5,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 6,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 7,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 8,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 9,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(POSITION, 10,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST

FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC

WHERE DC.OWNER = CC.OWNER

AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

AND DC.CONSTRAINT_TYPE = 'R'

AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')

GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME

) CON

WHERE NOT EXISTS (

SELECT 1 FROM

( SELECT TABLE_OWNER, TABLE_NAME,

MAX(DECODE(COLUMN_POSITION, 1, '"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||

SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST

FROM DBA_IND_COLUMNS

WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')

GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL

WHERE CON.OWNER = COL.TABLE_OWNER

AND CON.TABLE_NAME = COL.TABLE_NAME

AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) ) ;