enq: TM - contention锁争用的解决
enq: TM - contention锁争用的解决
这两天生产上碰见个表锁争用的问题，现象就是04:00夜维一启动，应用就开始处理缓慢，AWR看，enq: TM - contention等待事件占比超过了97%，
从“SQL ordered by Elapsed Time”得到，第一条SQL，“DELETE FROM TABLE(因为信息安全，此处为示意表名) WHERE KEY=:1”，平均每次执行需要317秒，总耗时排名榜首，
经过应用确认，该语句正是应用处理慢的事务逻辑中开始的一个操作，这个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');
hunghung表示正在等待，我们看下等的是什么，
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阻塞，
上面的测试，告诉我们外键无索引，对主表的操作，无论删除，还是插入，都会对子表加锁，可能请求mode=3的锁，或者mode=5的锁，都是有影响的，但是，如果我们对外键创建索引，
SQL> CREATE INDEX fk_supplier ON product (supplier_id);Index created.ON product (supplier_id);
Index created.这三个会话操作，都可以正常执行，而且不会出现任何争用，因此，存在主外键约束，就需要为外键创建索引，否则在并发DML中就会出现锁争用，进而对应用产生影响，
从上面我们了解了外键无索引的测试过程，现在我们回来，刚才测试中外键字段，是按照默认升序，下面是SQL Developer中索引的定义，
通过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，此时主表的操作，不需要对子表加任何锁，
但是，从应用使用的上线脚本看，他是对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$列，
通过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字段没索引，
可以用下面语句，
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，
外键supplier_id没索引，因此，夜维删除主表的操作，就会对子表加锁，和应用中删除子表操作之间，就会存在TM锁争用。
为supplier_id这个外键字段，创建单键值索引，即可解决这问题。但是再进一步，和应用了解下，其实指定(supplier_id desc)是没有意义的，应用使用supplier_id=:1这种等值操作，索引升序和降序，只是定义了索引叶子节点键值从左到右的排列顺序是升序还是降序，等值检索的索引扫描成本则只和索引树的层级有关，除非是像(a asc, b desc)这种多键值索引，指定desc才会有用。
因此，一个问题的解决，除了从技术上看，还是要从他的原始需求了解，这么做是不是真的有必要，从根本解决，才是最重要的。