外键无索引造成锁问题的详细说明

1. 概述

前面我碰到个问题,外键无索引导致了严重的锁问题,参考:外键无索引导致的锁问题,这几天又碰到几次这个问题造成了死锁。现在详细叙述一下,为什么外键无索引时子表的锁问题会比较严重。

2. 准备测试环境

这里就使用Oracle的demo用户scott中的两张表,dept和emp,在emp的deptno字段上创建外键,并且确认deptno上无索引。
这里dept是父表,emp是子表。

alter table dept add constraint pk_dept primary key(deptno);
alter table emp add constraint pk_emp primary key(empno);
alter table emp add constraint fk_dept_deptno foreign key(deptno) references dept(deptno);

两张表的object_id如下:

SQL> column object_name format a30
SQL> column table_name format a25
SQL> SELECT object_id,
  2    object_name
  3  FROM dba_objects
  4  WHERE object_name IN ('EMP','DEPT')
  5    and owner = 'SYS';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
   9308319 DEPT
   9308318 EMP

3. 测试一:外键无索引情况

测试过程如下:

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.
SQL> delete from dept where deptno = 50;

 1 row deleted.

SQL> oradebug tracefile_name;
/oracle/admin/ydtest/udump/ydtest_ora_13396.trc
SQL> oradebug event 10704 trace name context off
Statement processed.

测试过程比较简单,在父表删除数据之前,设一下Event 10704,可以转储SQL执行过程中enqueue的获取情况。

#cat /oracle/admin/ydtest/udump/ydtest_ora_13396.trc | grep TM-
ksqgtl *** TM-008e089e-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-008e089f-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-008e089e-00000000 mode=4 flags=0x401 timeout=21474836 ***

这里008e089e转成十进制为9308318,就是EMP表为object_id。

SQL> SELECT sid,  type,  id1,  id2,
  2    lmode,  request,  block
  3  FROM v$lock
  4  WHERE sid IN
  5    (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID')
  6    );

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       460 TX      65576   12543899          6          0          0
       460 TM    9308319          0          3          0          0

当SQL执行完还没COMMIT前,EMP表的TM锁已经释放了。所以子表的TM锁持有过程是在父表DELETE发生时,
当DELETE完成后,会话持有子表的TM锁释放,因为事务还没有提交,主表的TM锁是不会释放的。
所以当DELETE完成后,我们只能看到会话持有主表的TM锁,而看不到子表的TM锁。
并且一次删除,请求了两次子表的TM锁,锁模式是4(share (S))

4. 测试二:外键有索引的情况

另起一个会话,给外键创建索引,观察子表的锁情况

SQL> create index idx_emp_deptno on emp(deptno);

Index created.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.
SQL> delete from dept where deptno = 50;

 1 row deleted.

SQL> oradebug tracefile_name;
/oracle/admin/ydtest/udump/ydtest_ora_25156.trc
SQL> oradebug event 10704 trace name context off
Statement processed.

可以看到,emp表的deptno字段有索引的情况下,父表删除数据行时,只需要获取一次子表的TM锁,并且锁模式是2(row-S (SS)),
锁要求低很多。

#cat /oracle/admin/ydtest/udump/ydtest_ora_25156.trc | grep TM-
ksqgtl *** TM-008e089e-00000000 mode=2 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-008e089f-00000000 mode=3 flags=0x401 timeout=21474836 ***

这里大家可以在跟踪时设置Event 10046,可以观察到DELETE父表时,如果外键无索引,会对子表进行全表扫描。
如果子表很大,子表TM锁持有的时间就会更长,可以自行测试。

5. 外键无索引的检查SQL

with con1 as
 (select /*+rule*/
   c.owner, c.constraint_name, cc.table_name, cc.column_name, cc.position
    from dba_constraints c, dba_cons_columns cc
   where c.constraint_type = 'R'
     and c.owner = cc.owner
     and c.constraint_name = cc.constraint_name),
ind1 as
 (select /*+rule*/
   i.owner, i.table_name, i.index_name, ic.column_name, ic.column_position
    from dba_indexes i, dba_ind_columns ic
   where i.owner = ic.index_owner
     and i.index_name = ic.index_name)
select c1.owner,
       c1.constraint_name,
       c1.table_name,
       c1.column_name,
       c1.position,
       i1.owner,
       i1.table_name,
       i1.index_name,
       i1.column_name,
       i1.column_position
  from con1 c1, ind1 i1
 where c1.owner = i1.owner(+)
   and c1.table_name = i1.table_name(+)
   and c1.column_name = i1.column_name(+)
   and c1.position = i1.column_position(+)
   and i1.column_name is null;

6. 完整的外键无索引导致死锁的例子

借用网上的例子Oracle外键不加索引引起死锁示例

6.1 环境准备

  • 创建子表
create table fk_t as select *from user_objects;
delete from fk_t where object_id is null;
commit;
  • 创建父表
create table pk_t as select *from user_objects;
delete from pk_t where object_id is null;
commit;
  • 创建父表的主键
alter table PK_t add constraint pk_pktable primary key (OBJECT_ID);
  • 创建子表的外键
alter table FK_t add constraint fk_fktable foreign key (OBJECT_ID) references pk_t (OBJECT_ID);
  • 对象ID:
SQL> column object_name format a30
SQL> column table_name format a25
SQL> SELECT object_id,
  2    object_name
  3  FROM dba_objects
  4  WHERE object_name IN ('PK_T','FK_T')
  5    and owner = 'SYS';

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
   9308937 FK_T
   9308938 PK_T

6.2 操作过程

  • session1

执行一个删除操作

SQL> select distinct sid from v$mystat;

       SID
----------
       774
delete from fk_t where object_id=100;
delete from pk_t where object_id=100;

删除完在子表和父表上都加了一个Row-S(SX)锁, lmode=3

SQL> SELECT sid,  type,  id1,  id2,
  2    lmode,  request,  block
  3  FROM v$lock
  4  WHERE sid IN
  5    (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID')
  6    );

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       774 TM    9308938          0          3          0          0
       774 TX     655365    3055134          6          0          0
       774 TM    9308937          0          3          0          0

session2
执行另一个删除操作,发现这时候第二个删除语句等待

SQL> select distinct sid from v$mystat;

       SID
----------
      1078
delete from fk_t where object_id=200;
delete from pk_t where object_id=200;

解释一下为什么删除父表的记录时会等待:
因为session1删除子表记录时还未commit,持有子表的lmode=3的TM锁,从前面的测试可以看出,在子表的外键无索引的情况下,
删除父表记录需要请求子表的lmode=4的TM锁,从锁兼容性来看3级和4级是不兼容的,所以导致session2删除父表时挂住。
而实际测试时,发现请求的TM锁是更高的lmode=5,那是因为子表有DML,已经获取了子表的TM SX(lmode=3),当删除父表数据时,需要获取子表TM-SSX(lmode=5)。

SQL> column event format a30
SQL> column "Lock" format a10
SQL> column "Mode" format a10
SQL> set linesize 180
SQL> select sid,
  2         username,
  3         event,
  4         sql_id,
  5         blocking_instance,
  6         BLOCKING_SESSION,
  7         chr(to_char(bitand(p1,-16777216))/16777215)||
  8         chr(to_char(bitand(p1, 16711680))/65535) "Lock",
  9         to_char( bitand(p1, 65535) )  "Mode",
 10         p2
 11    from v$session
 12   where SID = 1078;

  SID USERNAME  EVENT                 SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION Lock       Mode               P2
----- --------- --------------------- ------------- ----------------- ---------------- ---------- ---------- ----------
 1078 SYS       enq: TM - contention  bt0td8zgr2mzt                 1              774 TM         5             9308937
  • 回到session1执行
SQL> delete from pk_t where object_id=201;

这时侯死锁告警发生,死锁图如下:

  • 整个过程转化为表的上锁行为为:
session1 lock table fk_t in ROW EXCLUSIVE mode ;
删除pk_t数据过程中,获取fk_t表的TM-SSX锁(由于是同一个会话,可以获取),pk_t数据delete完成,fk_t表的TM-SSX锁释放
lock table fk_t in SHARE ROW EXCLUSIVE MODE ;
session2 lock table fk_t in ROW EXCLUSIVE mode ;
删除pk_t数据过程中,获取fk_t表的TM-SSX锁(由于session1已获取TM-SX锁,与session2的SSX不兼容所以无法获取),会话挂起
lock table fk_t in SHARE ROW EXCLUSIVE MODE ;
session1 删除pk_t数据过程中,获取fk_t表的TM-SSX锁(由于session2已获取TM-SX锁,与session1的SSX不兼容所以无法获取),死锁发生
lock table fk_t in SHARE ROW EXCLUSIVE MODE ;

关于紫砂壶

感悟技术人生
此条目发表在Oracle性能问题分类目录,贴了标签。将固定链接加入收藏夹。

3 则回应给 外键无索引造成锁问题的详细说明

  1. 越Yan说:

    外键无索引,容易搞死锁,我记得Tom老人家说过,也有脚本检查。

  2. 紫砂壶说:

    需要了解为什么,切忌听…说
    从锁模式的冲突来看
    锁兼容性
    模式为2只与X(排它锁)冲突,TM为X模式,基本只有DDL了,所以外键有索引时基本不会有机会TM锁冲突
    而模式为4,锁冲突的机会就比较多了,SX、SSX、X。外键无索引时持有子表的TM锁模式为4,冲突概率大,并且外键无索引子表又是全表扫描,持有子表TM锁的时间又长,两者一叠加,死锁概率就大了。

  3. 越yan说:

    透彻,好茶,越评越香!!

评论已关闭。