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

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

总结了一下,OracleTM锁类型:

1024-LOCK

 

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

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

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

lock-1-2

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

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

lock 1-3

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

lock 1-4

lock 1-6

可以看到,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主表。

lock 1-5

 

总结:

无索引,无delete cascade情况 :

insert 主表:主表,子表各需要申请MODE=3 TM LOCK

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

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