这两天生产上碰见个表锁争用的问题，现象就是04:00夜维一启动，应用就开始处理缓慢，AWR看，enq: TM - contention等待事件占比超过了97%，

enq: TM - contention锁争用的解决_外键


从“SQL ordered by Elapsed Time”得到，第一条SQL，“DELETE FROM TABLE(因为信息安全，此处为示意表名) WHERE KEY=:1”，平均每次执行需要317秒，总耗时排名榜首，

enq: TM - contention锁争用的解决_外键约束_02


经过应用确认，该语句正是应用处理慢的事务逻辑中开始的一个操作，这个TABLE表是另一张主表的子表，且是通过ON DELETE CASCADE级联来定义的外键约束，夜维程序则是直接删除主表，级联自动删除子表的数据，主表一次删除150条左右的记录，对应到子表300条，不存在删除数据量较大导致执行缓慢的可能，有理由怀疑，夜维程序DELETE主表和这条DELETE子表之间存在某些关联。


我们暂时抛开这个问题，看下等待事件enq: TM - contention，一般是执行DML期间，为防止对与DML相关的对象进行修改，执行DML的进程必须对该表获得TM锁，就可能产生enq: TM - contention等待事件，若在获得TM锁的过程中发生争用，则等待enq: HW - contention事件，从AWR可以看到这两个等待事件。



从资料上显示，TM锁在下列场景中被申请：

1.在OPS(早期的RAC)中LGWR会以ID1=0 & ID2=0去申请该队列锁来检查 DML_LOCKS 在所有实例中是全0还是全非0。

2. 当一个单表或分区需要做不同的表/分区操作时，ORACLE需要协调这些操作，所以需要申请该队列锁。

3. 启用参考约束referential constraints

4. 修改约束从DIASABLE NOVALIDATE 到DISABLE VALIDATE

5. 重建IOT

6. 创建视图或者修改ALTER视图时可能需要申请该队列锁

7. 分析表统计信息或validate structure时

8. 一些PDML并行DML操作

9. 所有可能调用kkdllk()函数的操作


说了这么多原因，其实最常见的，就是外键未建索引导致的，

《外键为何要建索引？》

《探究外键为何要建索引？》

《》

这个网站的帖子，给出了非常清晰的测试过程，

https://logicalread.com/solving-oracle-enq-tm-contention-waits-dr01/#.XShTNiO77_9


创建测试表，主表supplier，子表product，其中product子表以ON DELETE CASCADE定义了外键约束，注意，外键无索引，

SQL> CREATE TABLE supplier     ( supplier_id number(10) not null,       supplier_name varchar2(50) not null,       contact_name varchar2(50),       CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)     );Table created.SQL> INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');1 row created.SQL> INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');1 row created.SQL> COMMIT;Commit complete.SQL> CREATE TABLE product     ( product_id number(10) not null,       product_name varchar2(50) not null,       supplier_id number(10) not null,       CONSTRAINT fk_supplier       FOREIGN KEY (supplier_id)       REFERENCES supplier(supplier_id)       ON DELETE CASCADE );  Table created.SQL> INSERT INTO product VALUES (1, 'Product 1', 1);1 row created.SQL> INSERT INTO product VALUES (2, 'Product 2', 1);1 row created.SQL> INSERT INTO product VALUES (3, 'Product 3', 2);1 row created.SQL> COMMIT;Commit complete.
Table created.

SQL> INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');
1 row created.

SQL> INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TABLE product
     ( product_id number(10) not null,
       product_name varchar2(50) not null,
       supplier_id number(10) not null,
       CONSTRAINT fk_supplier
       FOREIGN KEY (supplier_id)
       REFERENCES supplier(supplier_id)
       ON DELETE CASCADE );  
Table created.

SQL> INSERT INTO product VALUES (1, 'Product 1', 1);
1 row created.

SQL> INSERT INTO product VALUES (2, 'Product 2', 1);
1 row created.

SQL> INSERT INTO product VALUES (3, 'Product 3', 2);
1 row created.

SQL> COMMIT;
Commit complete.


第一个会话，删除主表第一条记录，第二个会话，删除主表第二条记录，第三个会话，向主表增加一条记录，可以看出，除了会话1，其他两个会话的操作，均处于hung，




session 1SQL> DELETE supplier WHERE supplier_id = 1;1 row deleted.session 2SQL> DELETE supplier WHERE supplier_id = 2;hungsession 3SQL> INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');hungSQL> DELETE supplier WHERE supplier_id = 1;
1 row deleted.

session 2
SQL> DELETE supplier WHERE supplier_id = 2;
hung

session 3
SQL> INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');
hung


hung表示正在等待，我们看下等的是什么，




SQL> SELECT l.sid, s.blocking_session blocker, s.event, l.type,      l.lmode, l.request, o.object_name, o.object_type     FROM v$lock l, dba_objects o, v$session s     WHERE UPPER(s.username) = UPPER('&User')     AND l.id1 = o.object_id (+)     AND l.sid = s.sid     ORDER BY sid, type;event, l.type, 
     l.lmode, l.request, o.object_name, o.object_type
     FROM v$lock l, dba_objects o, v$session s
     WHERE UPPER(s.username) = UPPER('&User')
     AND l.id1 = o.object_id (+)
     AND l.sid = s.sid
     ORDER BY sid, type;


其中会话1的SID是26，会话2的SID是27，会话3的SID是29，可以看出，会话1是先执行，删除主表的操作，因为存在外键，所以对主表和子表都会持有MODE=3的TM锁，会话2对主表持有mode=3的TM锁，因为主外键约束，会请求子表mode=5的锁，此时被会话1阻塞，会话3插入操作，对主表持有mode=3的TM锁，同时请求子表mode=3的锁，此时被会话2阻塞，

enq: TM - contention锁争用的解决_SQL_03


上面的测试，告诉我们外键无索引，对主表的操作，无论删除，还是插入，都会对子表加锁，可能请求mode=3的锁，或者mode=5的锁，都是有影响的，但是，如果我们对外键创建索引，




SQL> CREATE INDEX fk_supplier ON product (supplier_id);Index created.ON product (supplier_id);
Index created.


这三个会话操作，都可以正常执行，而且不会出现任何争用，因此，存在主外键约束，就需要为外键创建索引，否则在并发DML中就会出现锁争用，进而对应用产生影响，

enq: TM - contention锁争用的解决_外键_04


从上面我们了解了外键无索引的测试过程，现在我们回来，刚才测试中外键字段，是按照默认升序，下面是SQL Developer中索引的定义，

enq: TM - contention锁争用的解决_外键_05


通过user_indexes和user_ind_columns视图，




SQL> select a.table_name, a.index_name, b.column_name, b.descendfrom user_indexes a, user_ind_columns bwhere a.table_name= b.table_name and a.table_name='PRODUCT';
from user_indexes a, user_ind_columns b
where a.table_name= b.table_name and a.table_name='PRODUCT';


可以看得出外键字段supplier_id存在索引，默认升序ASC，此时主表的操作，不需要对子表加任何锁，

enq: TM - contention锁争用的解决_SQL_06


但是，从应用使用的上线脚本看，他是对supplier_id设置了desc，




SQL> CREATE INDEX fk_supplier ON product (supplier_id desc);Index created.ON product (supplier_id desc);
Index created.


此时，通过user_indexes和user_ind_columns视图，

SQL> select a.table_name, a.index_name, b.column_name, b.descend
from user_indexes a, user_ind_columns b
where a.table_name= b.table_name and a.table_name='PRODUCT';


看到这个索引，不是对supplier_id字段建的，而是对SYS_NC00004$列，

enq: TM - contention锁争用的解决_外键_07


通过user_indexes和user_ind_expressions视图，




SQL> select a.table_name, a.index_name, a.index_type, b.column_expression from user_indexes a, user_ind_expressions bwhere a.table_name= b.table_name and a.table_name='PRODUCT';
from user_indexes a, user_ind_expressions b
where a.table_name= b.table_name and a.table_name='PRODUCT';


从结果可以看到，显示这个索引的类型，是基于函数的索引，并不是普通的单键值索引，换句话说，supplier_id字段没索引，

enq: TM - contention锁争用的解决_外键_08


可以用下面语句，




SQL> SELECT * FROM (SELECT c.table_name, cc.column_name, cc.position column_positionFROM   user_constraints c, user_cons_columns ccWHERE  c.constraint_name = cc.constraint_nameAND    c.constraint_type = 'R'MINUSSELECT i.table_name, ic.column_name, ic.column_positionFROM   user_indexes i, user_ind_columns icWHERE  i.index_name = ic.index_name)ORDER BY table_name, column_position;FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;


再次证明，"R"外键约束没索引的对象，包括了子表的supplier_id，

enq: TM - contention锁争用的解决_外键约束_09


外键supplier_id没索引，因此，夜维删除主表的操作，就会对子表加锁，和应用中删除子表操作之间，就会存在TM锁争用。



为supplier_id这个外键字段，创建单键值索引，即可解决这问题。但是再进一步，和应用了解下，其实指定(supplier_id desc)是没有意义的，应用使用supplier_id=:1这种等值操作，索引升序和降序，只是定义了索引叶子节点键值从左到右的排列顺序是升序还是降序，等值检索的索引扫描成本则只和索引树的层级有关，除非是像(a asc, b desc)这种多键值索引，指定desc才会有用。


因此，一个问题的解决，除了从技术上看，还是要从他的原始需求了解，这么做是不是真的有必要，从根本解决，才是最重要的。


enq: TM - contention锁争用的解决_SQL_10