外键无索引引起的TM表锁

现象

数据库等待事件出现大量TM表锁，造成数据库严重等待。经过查询引起的SQL为简单的DELETE语句

1

DELETE FROM INSP.WF_XXX_XXX WHERE PROCESS_ID='8a80841663fb6dc00164d051ddd3240c';

分析

相关表上存在两个外键且无索引，当子表的外键列上无索引时在主表执行DML会在外键上请求4级锁，造成等待；如果存在索引则请求3级锁，这种情况下不会产生阻塞。 oracle-lock

准备测试环境

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42


SQL> create table t1(id number primary key);

Table created.

SQL> begin
    2  for i in 1..100 loop
    3         insert into t1 values(i);
    4  end loop;
    5  end;
    6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create table t2(fid number, id number primary key);

Table created.

SQL> alter table t2 add constraint fk_fid foreign key(fid) references t1(id);

Table altered.

SQL>  begin
    2         for i in 1..100 loop
    3                 insert into t2 values(i,i+2);
    4         end loop;
    5  end;
    6  /

PL/SQL procedure successfully completed.

SQL> commit;

SQL> select object_name,object_id from user_objects where object_name in ('T1','T2');

OBJECT_NAME         OBJECT_ID
------------------- --------------- 
 T1                  90767
 T2                  90769

开启10704内部事件并删除表中fid=10的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.
SQL> delete from test.t2 where fid=10;

1 row deleted.

SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/ogg/ogg/trace/ogg_ora_5092.trc

跟踪文件中00016291转换成十进制就是子表T2的OBJECT_ID，LOCK_MODE都为3

1
2
3


[oracle@OGG ~]$ cat /u01/app/oracle/diag/rdbms/ogg/ogg/trace/ogg_ora_5092.trc | grep TM-
ksqgtl *** TM-0001628f-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl *** TM-00016291-00000000 mode=3 flags=0x401 timeout=21474836 ***

删除主表中id=15的数据，会话被阻塞处于等待中。查询表锁信息，ID1为对象的OBJECT_ID，第二行的REQUEST为4表示主表在请求4级锁，但是被会话阻塞，如果此时再对T1主表做变更将会产生死锁。

1
2
3
4
5
6
7
8


SQL> select * from v$lock where type='TM';

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007FEE9D3A2E28 00007FEE9D3A2E88         34 TM      90767          0          3          0        115          0
00007FEE9D3A2E28 00007FEE9D3A2E88         34 TM      90769          0          0          4        115          0
00007FEE9D3A2E28 00007FEE9D3A2E88         35 TM      90767          0          3          0        376          0
00007FEE9D3A2E28 00007FEE9D3A2E88         35 TM      90769          0          3          0        376          1

添加索引后重复上述测试，对T1做操作时不再阻塞，改为3级锁请求了

1
2
3
4
5
6
7
8


SQL> select * from v$lock where type='TM';

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007FEE9D3A62A8 00007FEE9D3A6308         34 TM      90767          0          3          0         50          0
00007FEE9D3A62A8 00007FEE9D3A6308         34 TM      90769          0          3          0         50          0
00007FEE9D3A62A8 00007FEE9D3A6308         35 TM      90767          0          3          0         62          0
00007FEE9D3A62A8 00007FEE9D3A6308         35 TM      90769          0          3          0         62          0

解决方案

在外键列上加上索引，可以通过下列语句查找未建索引的外键

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


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;