外键上有无索引的影响
发表于：2025-01-20 作者：千家信息网编辑
千家信息网最后更新 2025年01月20日，原文链接： https://www.modb.pro/db/22800摘要：今天在摩天轮上看到一个问题，《oracle外键无索引为什么会导致死锁》，为什么呢？ 现在通过一些简单的案例来测试一下，外键索
千家信息网最后更新 2025年01月20日外键上有无索引的影响
原文链接： https://www.modb.pro/db/22800
摘要：今天在摩天轮上看到一个问题，《oracle外键无索引为什么会导致死锁》，为什么呢？ 现在通过一些简单的案例来测试一下，外键索引和锁的关系。一、环境模拟
1、创建父表dept，主键deptno
SQL> create table dept(deptno number,dname varchar2(20), 2 constraint pk_dept primary key (deptno) 3 );Table created.SQL>
2、创建子表emp，主键empno，外键deptno
SQL> create table emp(empno number,ename varchar2(20),deptno number, 2 constraint pk_emp primary key (empno), 3 constraint fk_deptno foreign key (deptno) references dept (deptno) 4 );Table created.SQL>
3、插入数据
SQL> insert into dept select deptno,dname from scott.dept;4 rows created.SQL> insert into emp select empno,ename,deptno from scott.emp;14 rows created.SQL> commit;Commit complete.SQL> select * from dept; DEPTNO DNAME---------- ---------------------------------------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONSSQL> select * from emp; EMPNO ENAME DEPTNO---------- ---------------------------------------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 1014 rows selected.SQL>
二、模拟测试-外键无索引
session 1：在子表上插入一条记录，不提交
SQL> select userenv('sid') from dual;USERENV('SID')-------------- 170SQL> insert into emp values(3000,'xiaoli',10);
1 row created.
SQL>
session 2：在父表上变更一条记录，将会被挂起
SQL> select userenv('sid') from dual;USERENV('SID')-------------- 191SQL> update dept set deptno=10,dname='AAAAA' where deptno=10;查询锁情况：
select mm.addr , mm.kaddr , mm.sid , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number , mm.type , mm.id1 , mm.id2 , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request-- , mm.ctime , lpad(trunc(mm.ctime/60/60),3) || ' Hour ' || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min ' || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime , case when mm.block = 1 and mm.lmode != 0 then 'holder' when mm.block = 0 and mm.request != 0 then 'waiter' else null end role , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session , dd.sql_text sql_text , cc.event wait_eventfrom v$lock mm , v$session ee , v$sqlarea dd , v$session_wait ccwhere mm.sid in ( select nn.sid from ( select tt.* , count(1) over (partition by tt.type,tt.id1,tt.id2) cnt , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag from v$lock tt ) nn where nn.cnt > 1 and nn.lmod_flag != 0 and nn.request_flag != 0) and mm.sid = ee.sid(+) and ee.sql_id = dd.sql_id(+) and mm.sid = cc.sid(+)order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
这里我们可以看到：
session 1 正在做DML处理，对于DML处理会在表级锁™上加上SX模式的锁。
session 2 在更新主键deptno的时候，因为在子表EMP对应的外键字段上没有锁，因此需要在表级（TM）追加了一个S模式的锁。
session 2 请求追加S模式的锁在了TM上，因为SX与S模式的锁是互斥的，因此session 2 被阻塞而挂起。
session 3：在子表上插入一条记录，同样将会被挂起
SQL> select userenv('sid') from dual;USERENV('SID')-------------- 213SQL> insert into emp values(3001,'xiaozhang',20);查询锁情况：
select mm.addr , mm.kaddr , mm.sid , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number , mm.type , mm.id1 , mm.id2 , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request-- , mm.ctime , lpad(trunc(mm.ctime/60/60),3) || ' Hour ' || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min ' || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime , case when mm.block = 1 and mm.lmode != 0 then 'holder' when mm.block = 0 and mm.request != 0 then 'waiter' else null end role , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session , dd.sql_text sql_text , cc.event wait_eventfrom v$lock mm , v$session ee , v$sqlarea dd , v$session_wait ccwhere mm.sid in ( select nn.sid from ( select tt.* , count(1) over (partition by tt.type,tt.id1,tt.id2) cnt , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag from v$lock tt ) nn where nn.cnt > 1 and nn.lmod_flag != 0 and nn.request_flag != 0) and mm.sid = ee.sid(+) and ee.sql_id = dd.sql_id(+) and mm.sid = cc.sid(+)order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;
这里我们可以看到：
session 3 需要做DML处理，同样需要请求SX模式的锁在TM上，因此它被session 2 在TM上S模式锁的请求阻塞。
三、模拟测试-外键有索引
session 1:
SQL> insert into emp values(3000,'xiaoli',10);1 row created.SQL>
session 2:
SQL> update dept set deptno=10,dname='AAAAA' where deptno=10;1 row updated.SQL>
这里发现session 2 就没有被 session 1 所阻塞。
四、结论
1、所有的外键上创建索引，避免不必要的死锁产生。
2、update 父表的语句，尽量避免更新主键。
模式
索引
测试
阻塞
情况
死锁
处理
更新
查询
原文
字段
摘要
摩天轮
数据
时候
案例
正在
环境
结论
语句
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
土地利用影像数据库
作为维护网络安全的义务
公安部网络安全保卫局副局长
广东软件开发外包多少钱
软件开发技术员工资组成
少年三国志服务器刷新时间
江苏运营软件开发价格表格
万方数据库属于三次文献
研究生论文数据库哪个好
玛爱网络技术有限公司怎么样
edge无法下载服务器问题
高级数据库工程师怎么考
计算机网络技术班徽素材
华为平板注册账号服务器繁忙
打印机web服务器在哪打开
vba哪个数据库好
网络安全科技馆河南巡展鹤壁站
安全网关是服务器吗
网络安全先进技术与应用发展
谁在维护区块链的服务器
数据库采购
手机软件开发学什么技术
进服务器超级管理密码是什么
腾讯云服务器安全系统
一个应用程序连接多个数据库
政府行业网络安全行业教程
公共资源交易平台服务器验签失败
海子塔服务器
数据库费用入办公费
江苏正规软件开发诚信服务
