oracle 外键无索引导致锁
摘自TOM大师的语句, 外键不加索引
SELECT TABLE_NAME,
owner CONSTRAINT_NAME,
CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
NVL2(CNAME3, ',' || CNAME3, NULL) ||
NVL2(CNAME4, ',' || CNAME4, NULL) ||
NVL2(CNAME5, ',' || CNAME5, NULL) ||
NVL2(CNAME6, ',' || CNAME6, NULL) ||
NVL2(CNAME7, ',' || CNAME7, NULL) ||
NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT B.owner,
B.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
COUNT(*) COL_CNT
FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
POSITION
FROM DBA_CONS_COLUMNS) A,
DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME, B.owner) CONS
WHERE COL_CNT > ALL (SELECT COUNT(*)
FROM DBA_IND_COLUMNS I
WHERE I.TABLE_NAME = CONS.TABLE_NAME
AND I.COLUMN_NAME IN (CNAME1,
CNAME2,
CNAME3,
CNAME4,
CNAME5,
CNAME6,
CNAME7,
CNAME8)
AND I.COLUMN_POSITION < = CONS.COL_CNT
GROUP BY I.INDEX_NAME);
--等待事件---P1 P2 P3
select ash .SAMPLE_TIME ,
ash .EVENT ,
ash .SESSION_ID ,
ash .BLOCKING_SESSION ,
ash .P1TEXT ,
ash .P1 ,
ash .P2TEXT ,
ash .p2 ,
ash .p3text ,
ash .p3 ,
ash .SESSION_STATE ,
ash .SQL_OPNAME ,
ash .SQL_ID
--ash.*
from v$active_session_history ash
where ash .SAMPLE_TIME >
to_date ( '20200519 17:50:00' , 'yyyymmdd HH24:MI:SS' )
and ash .SAMPLE_TIME <
to_date ( '20200519 18:10:00' , 'yyyymmdd HH24:MI:SS' )
and ash .WAIT_CLASS < > 'Idle'
-- and ash .EVENT like 'enq: TM - contention'
order by sample_time desc;
--结论
- 外键无索引锁无cascade时,update/delete父表,会在语句级别级联一个mode为4的S锁到子表,其中delete多少行就会级联多少次
- 外键无索引有cascade时,update父表仍会在语句级别级联mode为4的S锁到子表,delete时会先获取mode为5的SSX锁,在将其转换成mode为3的SX锁,而且删除多少行就会涉及到多少次转换
- 外键有索引无cascade时,update/delete不会在语句级级联锁到子表,最终会持有父表和子表上的mode为3的SX锁(无索引时只有有cascade的delete时最终会持有子表上的SX锁)
- 外键有索引有cascade时,与无cascade表现相同
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2019-05-20 oracle 监控索引的使用状况