oracle enq: tm,子表未创建正确索引导致enq: TM – contention的案例-分析锁机制(主表操作)...

近日，接到应用人员的反馈，一套数据库夜维程序上线后出现了应用性能缓慢，希望帮忙定位一下根因。数据库在此期间段经历了大量enq: TM – contention等待，在经过一番分析后最终解决了问题，具体的案例bisal进行相关分析及测试详情请见bisal的个人杂货铺(https://mp.weixin.qq.com/s/N2XhQhUl2MVYLkLW_iDXbQ)我们知道，主子表关系的应用环境中，子表外键列需要建立索引，否者会出现大量Lock等待，产生阻塞，而且重要的是，需要建立合适的索引。但是，本篇文章要讨论的是，如果不建立索引，为什么会产生等待，产生了什么类型的LOCK导致了会话间的阻塞？

总结了一下，OracleTM锁类型：

P.S Ora11g后，select …for update 为mode=3 的RX(SX)锁

下面我们通过event 10704来探寻一下，主表，子表没有建立索引前各个DML操作时会产生什么类型的锁，

(1)创建测试表，无delete cascade:

(2)场景1 主表进行insert操作：

通过10704 Event会打印出会话对于Enqueue锁的申请与释放详细信息，我们从如下Trace中可以看到，在子表未建立索引时，对主表插入数据会造成主表，子表分别申请MODE=3的RX锁。此时，子表如果存在已申请到不兼容的MODE=4,5,6事务时，会被阻塞。

(3)场景2 ：主表进行DELETE操作：

可以看到，DELETE主表时，与INSERT主表不同的是，子表会申请MODE=4的TM LOCK并进行LOCK转换，并会进行释放(测试时注意到，MODE=4申请释放操作与删除数据行数有关，基本为删除1条数据需要申请并释放删除的记录数+1次，本例删除了1条数据)。子表存在MODE=3,5,6 lock的事务时，会被阻塞

(4)场景3：UPDATE 主表操作：

同DELETE主表一致，同样对子表申请MODE=4的TM LOCK，并且在事务过程中很快释放。阻塞信息同DELETE主表。

总结：

无索引，无delete cascade情况 ：

insert 主表：主表，子表各需要申请MODE=3 TM LOCK

update主表：主表，申请MODE=3 LOCK,子表会申请MODE=4的 LOCK，并且马上释放

DELETE主表：主表，申请MODE=3 LOCK,子表会申请MODE=4的 LOCK，并且马上释放(申请MODE=4的次数与待删除的行数相关，为删除的行数+1次)