现象
数据库等待事件出现大量TM表锁，造成数据库严重等待。经过查询引起的SQL为简单的DELETE语句
DELETE FROM INSP.WF_XXX_XXX WHERE PROCESS_ID='8a80841663fb6dc00164d051ddd3240c';
分析
相关表上存在两个外键且无索引，当子表的外键列上无索引时在主表执行DML会在外键上请求4级锁，造成等待；如果存在索引则请求3级锁，这种情况下不会产生阻塞。
准备测试环境
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的数据
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
[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主表做变更将会产生死锁。
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级锁请求了
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
解决方案
在外键列上加上索引，可以通过下列语句查找未建索引的外键
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;
)