深入剖析:insert 的enq: TM – contention

张大朋(Lunar)Oracle 资深技术专家

Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 ORACLE Sales Consultant 部门,负责的产品主要是 Exadata,Golden Gate,Database 等。

编辑手记:此文通过分场景环环紧扣的测试,深入剖析了enq: TM – contention等待事件的原理,今日拣选与大家共享。

结论:当外键无索引时 1,对子表的insert操作所在的事务没有完成前,对于父表的DML操作(INSERT/UPDATE/DELETE)都会因为不能获得对子表的TM锁而出现enq: TM – contention。 2,在1的基础上,如果又有了对子表的insert,那么这个对子表的insert同样被阻塞,等待事件也是 enq: TM – contention。 3,对父表的insert会阻塞对父表的delete。同样的道理,此时对父表的insert也阻塞对父表的update pk操作。

当没有索引的时候

测试1, 在子表发生 Insert,然后在父表上有update操作

Session 1: 对子表进行insert,不commit时:

这是,我们看到,该回话在主表(1062788 DEPT)和子表(1062790 EMP)上都分别持有了 exclusive lock(LMODE=3)锁

同时,该回话还持有一个tx锁(在子表 emp上)

另开一个会话,观察锁:

Session 2 : 然后,我们在父表做DML操作update:

Session 3: 检查锁的变化

这是我们看到,Session 2 在子表(1062790 EMP)上请求LMODE=4,并在主表(1062788 DEPT)上持有等待:enq: TM – contention

这是很清晰,有blocker了,也就是Session 1(SID 321),此时该回话正在对子表(1062790 EMP)进行插入操作

换句话说,当外键无索引时,对子表的insert操作,会造成对父表的更新操作的阻塞。

Session 4 更新父表:

这是我们看到,session 4(sid 116)遵循了跟session 220同样的所规律

这是我们在Session 1中执行commit,在Session 3中观察:

可以看到Session 2和Session 4都完成了update: Session 2:

这里我们看到,当外键无索引时,对子表的insert操作,会造成对父表的更新操作的阻塞,该阻塞直到子表的insert事务结束,才自动释放。

测试2, 在子表发生 Insert,然后在父表上有update操作,然后再有一个会话对子表做insert

重复上面的操作,不对session 1进行commit时,再开一个窗口(Session 5)对子表做insert

这是我们看到,对于子表的insert语句被阻塞了。

在Session 3中观察:

这里我们看到: 1,当外键无索引时,对子表的insert操作,会造成对父表的更新操作的阻塞,该阻塞直到子表的insert事务结束,才自动释放。 2,如果对子表有insert操作,在这个insert所在事务没有commit之前,有对父表的update,那么接下来的对子表的inser会被阻塞

测试3: 在测试2的场景上,将session 2和session 4中的对父表的update执行Ctrl+C,然后分别改为INSERT 和delete 父表(dept)的操作,观察一下

Session 4:

Session 5 随之自动解锁,并报了正常的违反约束的错误:

在session 2中执行delete 父表的操作:

注意: 1,delete父表的操作被阻塞,依然是正在请求以LMODE=4的模式对子表(1062790 EMP)的TM锁 2,这里的sid 222中为非活动会话,当该回话执行了commit或者rollback后,这个Insert的“SQL*Net message from client”等待就会消失了。

测试4: 在测试3的场景上,继续session 4中的对父表做insert 的操作,观察一下 Session 4:

这里我们看到,虽然Session 4执行的是对父表的insert,session 2执行的是对父表的 delete,他们的表现跟update的表现都是相同的。也就是说,对于子表的insert操作所在的事务没有完成前,对于父表的DML操作(INSERT/UPDATE/DELETE)都会因为不能获得对子表的TM锁而出现enq: TM – contention

测试5: 在测试4的基础上,我们在Session 1对做commit,完成对子表的insert操作,然后观察其他session:

Session 1:

Session 4:对父表的insert插入操作随着session 1的commit 自动解锁:

Session 2(SID 220):对父表的delete操作依然是hang的状态:

也就是说,这里我们看到了,对父表的insert,阻塞了对父表的delete

----the end

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-09-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏chenssy

在一个千万级的数据库查寻中,如何提高查询效率?

1、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 orderby 涉及的列上建立索引;

15420
来自专栏更流畅、简洁的软件开发方式

记录容易忘记的方法

提取每个分类前n条记录 SELECT ID, Name, CategoryID FROM TableName AS a WHERE (ID IN ...

246100
来自专栏idba

MySQL 5.7 新特性之三

本系列文章基于5.7.20 版本讲述MySQL的新特性,从安装,文件结构,SQL,优化,复制等几个方面展开介绍5.7 的新特性和功能,同时也建议大家跟踪官方bl...

11020
来自专栏Java进阶干货

MySql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

12110
来自专栏轮子工厂

深入理解MySQL---数据库知识最全整理,这些你都知道了吗?

对于后端开发人员来说,经常会和数据打交道,今天总结下数据库相关的知识。包括MySQL,JDBC基础,JDBC进阶,MongoDB,性能优化等知识点。

11430
来自专栏吴伟祥

百万级数据库优化方案 转

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

12320
来自专栏芋道源码1024

数据库中间件 MyCAT源码分析:【单库单表】插入

本文主要基于 MyCAT 1.6.5 正式版 1. 概述 2. 接收请求,解析 SQL 3. 获得路由结果 4. 获得 MySQL 连接,执行 SQL 5. 响...

534120
来自专栏乐沙弥的世界

MySQL 状态变量(Server Status Variables)

    MySQL状态变量是当前服务器自启动后累计的一些系统状态信息,主要用于评估当前系统资源的使用情况以进一步分析系统性能而做出相应的调整决策。这些状态变量我...

14810
来自专栏蓝天

Oracle 系统表大全

数据字典dict总是属于Oracle用户sys的。   1、用户:    select username from dba_users;   改口令    al...

11120
来自专栏码神联盟

珍藏 | Java 岗位 【数据库】 面试题及答案详解

32320

扫码关注云+社区

领取腾讯云代金券