外键上有无索引的影响

2025-04-09 技术教程

原文链接：https://www.modb.pro/db/22800

摘要：今天在摩天轮上看到一个问题，《oracle外键无索引为什么会导致死锁》，为什么呢？现在通过一些简单的案例来测试一下，外键索引和锁的关系。一、环境模拟1、创建父表dept，主键deptno

SQL>createtabledept(deptnonumber,dnamevarchar2(20),2constraintpk_deptprimarykey(deptno)3);Tablecreated.SQL>2、创建子表emp，主键empno，外键deptno

SQL>createtableemp(empnonumber,enamevarchar2(20),deptnonumber,2constraintpk_empprimarykey(empno),3constraintfk_deptnoforeignkey(deptno)referencesdept(deptno)4);Tablecreated.SQL>3、插入数据

SQL>insertintodeptselectdeptno,dnamefromscott.dept;4rowscreated.SQL>insertintoempselectempno,ename,deptnofromscott.emp;14rowscreated.SQL>commit;Commitcomplete.SQL>select*fromdept;DEPTNODNAME--------------------------------------------------10ACCOUNTING20RESEARCH30SALES40OPERATIONSSQL>select*fromemp;EMPNOENAMEDEPTNO------------------------------------------------------------7369SMITH207499ALLEN307521WARD307566JONES207654MARTIN307698BLAKE307782CLARK107788SCOTT207839KING107844TURNER307876ADAMS207900JAMES307902FORD207934MILLER1014rowsselected.SQL>二、模拟测试–外键无索引session 1：在子表上插入一条记录，不提交

SQL>selectuserenv('sid')fromdual;USERENV('SID')--------------170

SQL> insert into emp values(3000,‘xiaoli’,10);
1 row created.
SQL>

session 2：在父表上变更一条记录，将会被挂起

SQL>selectuserenv('sid')fromdual;USERENV('SID')--------------191SQL>updatedeptsetdeptno=10,dname='AAAAA'wheredeptno=10;查询锁情况：

selectmm.addr,mm.kaddr,mm.sid,row_number()over(partitionbymm.type,mm.id1,mm.id2orderbymm.lmodedesc,mm.ctimedesc)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,casewhenmm.block=1andmm.lmode!=0then'holder'whenmm.block=0andmm.request!=0then'waiter'elsenullendrole,casewhenee.blocking_sessionisnotnullthen'waitingforSID'||ee.blocking_sessionelsenullendblocking_session,dd.sql_textsql_text,cc.eventwait_eventfromv$lockmm,v$sessionee,v$sqlareadd,v$session_waitccwheremm.sidin(selectnn.sidfrom(selecttt.*,count(1)over(partitionbytt.type,tt.id1,tt.id2)cnt,max(tt.lmode)over(partitionbytt.type,tt.id1,tt.id2)lmod_flag,max(tt.request)over(partitionbytt.type,tt.id1,tt.id2)request_flagfromv$locktt)nnwherenn.cnt>1andnn.lmod_flag!=0andnn.request_flag!=0)andmm.sid=ee.sid(+)andee.sql_id=dd.sql_id(+)andmm.sid=cc.sid(+)orderbymm.type,mm.id1,mm.id2,mm.lmodedesc,mm.ctimedesc;

这里我们可以看到：
session 1 正在做DML处理，对于DML处理会在表级锁™上加上SX模式的锁。
session 2 在更新主键deptno的时候，因为在子表EMP对应的外键字段上没有锁，因此需要在表级（TM）追加了一个S模式的锁。
session 2 请求追加S模式的锁在了TM上，因为SX与S模式的锁是互斥的，因此session 2 被阻塞而挂起。

session 3：在子表上插入一条记录，同样将会被挂起

SQL>selectuserenv('sid')fromdual;USERENV('SID')--------------213SQL>insertintoempvalues(3001,'xiaozhang',20);查询锁情况：

这里我们可以看到：
session 3 需要做DML处理，同样需要请求SX模式的锁在TM上，因此它被session 2 在TM上S模式锁的请求阻塞。

三、模拟测试–外键有索引session 1:

SQL>insertintoempvalues(3000,'xiaoli',10);1rowcreated.SQL>session 2:

SQL>updatedeptsetdeptno=10,dname='AAAAA'wheredeptno=10;1rowupdated.SQL>

这里发现session 2 就没有被 session 1 所阻塞。

四、结论

1、所有的外键上创建索引，避免不必要的死锁产生。
2、update 父表的语句，尽量避免更新主键。