enq: TM - contention锁争用的解决

淡淡的烟草味﹌ 2021-12-09 01:21 369阅读 0赞

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

![640?wx\_fmt=png][640_wx_fmt_png]

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

![640?wx\_fmt=png][640_wx_fmt_png 1]

经过应用确认，该语句正是应用处理慢的事务逻辑中开始的一个操作，这个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()函数的操作

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

《[外键为何要建索引？][Link 1]》

《[探究外键为何要建索引？][Link 2]》

《》

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

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阻塞，

![640?wx\_fmt=png][640_wx_fmt_png 2]

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

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

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

![640?wx\_fmt=png][640_wx_fmt_png 3]

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

![640?wx\_fmt=png][640_wx_fmt_png 4]

通过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，此时主表的操作，不需要对子表加任何锁，

![640?wx\_fmt=png][640_wx_fmt_png 5]

但是，从应用使用的上线脚本看，他是对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$列，

![640?wx\_fmt=png][640_wx_fmt_png 6]

通过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字段没索引，

![640?wx\_fmt=png][640_wx_fmt_png 7]

可以用下面语句，

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，

![640?wx\_fmt=png][640_wx_fmt_png 8]

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

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

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

![640?wx\_fmt=png][640_wx_fmt_png 9]

[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PalHjYfY67RxNPBRfq6ziapcZIo02ISpljib5meRiba8ez1nbIQSmwtCIS1iaib32suTjfwLfPd8BGQhLsA/640?wx_fmt=png
[640_wx_fmt_png 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PalHjYfY67RxNPBRfq6ziapcZjBkOEM9R1Dr32MsHBCmkuzweZmTbX1BeDWgtR4sVsOV59TbgYV5j6Q/640?wx_fmt=png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247483678&idx=1&sn=e7ef704e21ceba5cb149c7ebae90d0bf&chksm=ea8144edddf6cdfb32454d8398b24dd0826bf8dc23afbe92ce548a58a18eebc7710497e88ae5&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247486296&idx=1&sn=c06860bf9333d1e0a56f5caa5ee2433f&chksm=ea814eabddf6c7bdada45c4b1030a360d1c24a9a9f437d15d286a02af4855d4ab41901650ca8&scene=21#wechat_redirect
[640_wx_fmt_png 2]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PalHjYfY67RxNPBRfq6ziapcZUU72micye1FK2AiamBWccLYqtnaxXbDy4d0mYnbqySYUOCWjpSPt8Fng/640?wx_fmt=png
[640_wx_fmt_png 3]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PalHjYfY67RxNPBRfq6ziapcZNFaURcUTGWRZKr5ZE6Rt3xsPHWv1Dh5zjgpXzHYjcDufGhBvoKbQdg/640?wx_fmt=png
[640_wx_fmt_png 4]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYa5jmXygMzvQhQ5AKGVGGicfDzWGpu43ehOiaBHxYZb4c5lbzyjxLjfBw/640?wx_fmt=png
[640_wx_fmt_png 5]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYg3r4n29hpERy4cuaZViaFahpndrIb5Y2ddmJwianI2yRqPIAdR4t0CPw/640?wx_fmt=png
[640_wx_fmt_png 6]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYcJFXtWbKESW4coRenfChefZ13ibBE6qCFFJLfnn4crgsuHk12W8DHRg/640?wx_fmt=png
[640_wx_fmt_png 7]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYGibmIkiceqEicXQqu3Aibk48Hzy6QM2nvcgKic9Duexau6Y3hq9c4PgGl1w/640?wx_fmt=png
[640_wx_fmt_png 8]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYc0J1zqnfj2R9p8OGC4wRdTELluqWHxuBg1f1Q5FU0JNBuyczDz9JkA/640?wx_fmt=png
[640_wx_fmt_png 9]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86PanJvwEH3E2aQoiae0HLkOgKYiaFNozHtq4gRpdicsicDmtvibu8IYBIDQ5MTrLZ255vqxKOia7cXacDg9BA/640?wx_fmt=png