外键字段上索引对锁的影响

子表的外键上如果不创建索引，就有可能导致死锁问题

scenario 1: 更新主表主键，子表外键无索引

create table t_pri (id number,name varchar2(10));

alter table t_pri add constraint pk_t primary key (id);

create table t_ref (ref_id number not null primary key,ref_name varchar2(10),pri_id number,constraint fk_ref foreign key (pri_id) references t_pri (id));

insert into t_pri values(10,'xb');

commit;

session 1:

XB@xb> select sid from v$mystat where rownum=1;

       SID
----------
    62

XB@xb> insert into t_ref values(1,'xb',10);

1 row created.

session 2:

XB@xb> select sid from v$mystat where rownum=1;

       SID
----------
    41

XB@xb> update t_pri set id=10,name='xb2' where id=10;

>>>>====hang 住

session 3:

XB@xb> select sid from v$mystat where rownum=1;

       SID
----------
    56

XB@xb> insert into t_ref values(2,'lves',20);

>>>>====hang 住

查看锁信息：

SYS@xb> @lock "sid in (41,56,62)"
old  30: where &1
new  30: where sid in (41,56,62)

    SID TYPE        LMODE MODE_HELD      REQUEST MODE_REQUESTED  LOCK_ID1   LOCK_ID2    CTIME      BLOCK
------- ------ ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
     41 AE      4 Share            0 None        100    0         913          0
     62 AE      4 Share            0 None        100    0       13847          0
     56 AE      4 Share            0 None        100    0         157          0
     62 TO      3 Row-X (SX)           0 None        79833  1         726          0
     62 TX      6 Exclusive        0 None        458785 959       311          0
     41 TM      3 Row-X (SX)           0 None        88080  0         272          0
     62 TM      3 Row-X (SX)           0 None        88080  0         311          0
     56 TM      3 Row-X (SX)           0 None        88080  0         135          0
     41 TM      0 None             4 Share       88082  0         272          0
     62 TM      3 Row-X (SX)           0 None        88082  0         311          1
     56 TM      0 None             3 Row-X (SX)      88082  0         135          0

11 rows selected.


SYS@xb> select object_id,object_name from dba_objects where object_id in(88080,88082);

 OBJECT_ID OBJECT_NAME
---------- ------------------------------------------------------------
     88080 T_PRI
     88082 T_REF

通过上面的结果可以看到，session2在更新主键的时候，子表外键字段上面没有索引，所以会对t_ref表请求一个TM级别的share锁，而优先的session1会话正在对t_ref做insert的操作，已经在t_ref上加了一个TM级别的Row-X (SX)锁，与share锁是互相排斥，所以session2 hang住。session3对 t_ref做dml操作的时候，同样会请求TM级别的Row-X (SX)锁，则被session2所阻塞。

scenario 2: 删除主表行，子表on delete cascade

XB@xb> alter table t_ref drop constraint fk_ref;

Table altered.

XB@xb> alter table t_ref add constraint fk_ref foreign key (pri_id) references t_pri (id) on delete cascade;

Table altered.

session 1(sid:62):

1
2
3

XB@xb> insert into t_ref values(1,'xb',10);

1 row created.

session 2(sid:41):

1
2
3

XB@xb> delete from t_pri where id=10;

>>>>====hang 住

session 3(sid:56):

1
2
3

XB@xb> insert into t_ref values(2,'lves',20);

>>>>====hang 住

查看锁信息：

SYS@xb> @lock "sid in (41,56,62)"
old  30: where &1
new  30: where sid in (41,56,62)

    SID TYPE        LMODE MODE_HELD      REQUEST MODE_REQUESTED  LOCK_ID1   LOCK_ID2    CTIME      BLOCK
------- ------ ---------- --------------- ---------- --------------- ---------- ---------- ---------- ----------
     62 TX      6 Exclusive        0 None        262161 1035          794          0
     41 AE      4 Share            0 None        100    0        4490          0
     62 AE      4 Share            0 None        100    0       17424          0
     56 AE      4 Share            0 None        100    0        3734          0
     62 TO      3 Row-X (SX)           0 None        79833  1        4303          0
     41 TM      0 None             5 S/Row-X (SSX)   88082  0          99          0
     62 TM      3 Row-X (SX)           0 None        88082  0         794          1
     56 TM      0 None             3 Row-X (SX)      88082  0          42          0
     41 TM      3 Row-X (SX)           0 None        88080  0          99          0
     62 TM      3 Row-X (SX)           0 None        88080  0         794          0
     56 TM      3 Row-X (SX)           0 None        88080  0          42          0

11 rows selected.

这里看到session2请求的锁变成了TM级别的S/Row-X (SSX)锁，表示要先读取t_ref全表(share)，然后更改部分行数据(SX)。因为外键上有’on delete cascade’,所以回滚session1以后才能继续执行

session 1(sid:62):

1
2
3

XB@xb> rollback;

Rollback complete.

session 2(sid:41):

1
2
3

XB@xb> delete from t_pri where id=10;

1 row deleted.

session 3(sid:56):

XB@xb> insert into t_ref values(2,'lves',20);
insert into t_ref values(2,'lves',20)
*
ERROR at line 1:
ORA-02291: integrity constraint (XB.FK_REF) violated - parent key not found

scenario 3: 更新主表主键，子表外键有索引

1
2
3

XB@xb> create index idx_ref on t_ref(pri_id);

Index created.

session 1(sid:62):

1
2
3

XB@xb> insert into t_ref values(1,'xb',10);

1 row created.

session 2(sid:41):

1
2
3

XB@xb> update t_pri set id=10,name='xb2' where id=10;

1 row updated.

此时session2 并未被堵塞。

Summary:

需要在所有的外键上建立索引，可以用以下语句查询

select owner,
       table_name,
       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 owner,
                       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'
           and a.owner = b.owner
         group by b.owner, b.table_name, b.constraint_name) 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);

更新主表尽量不要更新主键

Oracle

外键字段上索引对锁的影响

https://www.xbdba.com/2018/11/14/foreign-key-noindex-lock/

作者

xbdba

发布于

2018年11月14日

许可协议

parallel不起作用上一篇

查询v$lock慢下一篇