外键上有无索引的影响

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

创新互联建站专注于企业全网营销推广、网站重做改版、市中网站定制设计、自适应品牌网站建设、H5网站设计、商城网站建设、集团公司官网建设、外贸网站制作、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为市中等各大城市提供网站开发制作服务。

摘要：今天在摩天轮上看到一个问题，《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 OPERATIONS
SQL> 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                                           10
14 rows selected.
SQL>

二、模拟测试–外键无索引

session 1：在子表上插入一条记录，不提交

SQL> select userenv('sid') from dual;
USERENV('SID')--------------
           170

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

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

SQL> select userenv('sid') from dual;
USERENV('SID')--------------
           191
SQL> 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')--------------
           213
SQL> 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 父表的语句，尽量避免更新主键。

网页标题：外键上有无索引的影响
浏览地址：http://cdxtjz.cn/article/pjeohi.html

外键上有无索引的影响

一、环境模拟

1、创建父表dept，主键deptno

2、创建子表emp，主键empno，外键deptno

3、插入数据

二、模拟测试–外键无索引

session 1：在子表上插入一条记录，不提交

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

查询锁情况：

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

查询锁情况：

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

session 1:

session 2:

四、结论

其他资讯