0%

外键无索引引起的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;