深入剖析: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 条评论
登录 后参与评论

相关文章

来自专栏Java成神之路

Saiku_学习_02_Schema Workbench 开发mdx和模式文件

saiku的查询都是通过cube来进行的。因此每当我们要进行一次多维度查询时,都要先修改xml、上传、重启才能生效,不仅效率低,还不利于学习和理解MDX和模式文...

874
来自专栏轮子工厂

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

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

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

记录容易忘记的方法

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

23110
来自专栏蓝天

Oracle 系统表大全

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

692
来自专栏码神联盟

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

1682
来自专栏Java技术

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

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

843
来自专栏互扯程序

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

794
来自专栏杨建荣的学习笔记

undo retention的思考(一)

最近有个网友咨询我一个问题,是关于undo_retention的,对于这个参数没有过多关注,只是知道需要设置undo_retention搭配使用undotabl...

3245
来自专栏逸鹏说道

程序猿是如何解决SQLServer占CPU100%的

文章目录 遇到的问题 使用SQLServer Profiler监控数据库 SQL1:查找最新的30条告警事件 SQL2:获取当前的总报警记录数 有哪些SQL语句...

3268
来自专栏唐郑望的专栏

Django数据处理的一些实践

提到 Django 肯定避不开 MVC 模式,即模型(Model)-视图(View)-控制器(Controller),通过将业务逻辑、数据、界面显示分离的方法组...

2421

扫码关注云+社区