Oracle新建或重建索引过程中的锁信息

0    303    2

Tags:

👉 本文共约4480个字,系统预计阅读时间或需17分钟。

简介

可以利用10704和10046事件跟踪新建或重建索引过程中的锁信息,命令为:

新建或重建索引的锁信息如下图所示:

Oracle新建或重建索引过程中的锁信息

不带ONLINE的新建或重建索引的SQL语句获取的是4级TM锁,它会阻塞任何DML操作。

在Oracle 10g中,带ONLINE的新建或重建索引的SQL语句在开始和结束的时候获取的是4级TM锁,而在读取表数据的过程中获取的是2级TM锁,所以,在Oracle 10g中,即使加上ONLINE也会阻塞其它会话的DML操作。

在Oracle 11g中,带ONLINE的新建或重建索引的SQL语句在整个执行过程中获取的是2级TM锁,并不会阻塞其它会话的DML操作,但是在创建或重建索引的过程中,其它的会话产生的事务会阻塞索引的创建或重建操作,所以必须结束其它会话的事务才能让创建或重建索引的操作完成。所以应该避免在业务高峰期创建索引。

在Oracle 11g带ONLINE的新建或重建索引的情况下:

① 过程中会持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)两种类型的锁,在Oracle 10g下只有DL锁没有OD锁。

② 表级锁TM的持有模式为2级RS(Row Share)与3级RX(Row Exclusive)类型的锁互相兼容,因此不会在表级发生阻塞。

③ 阻塞发生在行级锁申请阶段,即请求的4级S(Share)类型的锁与执行DML的会话已经持有的6级X(Exclusive)锁之间存在不兼容的情况;相比非ONLINE方式的表级锁,锁的粒度上更加细化,副作用更小。

④ 新增以“SYSJOURNAL”为前缀的IOT表,记录与索引创建动作同时进行的其它DML操作修改过的记录,等到索引创建完成前将IOT表里的记录合并至索引中并删除IOT表。

实验

创建或重建索引会阻塞DML操作

版本:11.2.0.3

可以发现在会话1中,在创建索引的过程中会生成2个TM锁,锁类别分别为4和3,根据查询结果发现lmode=4的object_id为77629的对象对应的是T_INDEX_161113这个表,对应的是TM的S锁。另一个lmode=3的锁对象是系统基表OBJ$表,允许其它会话对该表执行DML操作。可以得出这样一个结论:当对表进行创建索引操作时,会伴随出现LMODE=4的S锁。根据锁的兼容模式可以发现S锁和任何DML操作都是冲突的!所以,尤其是在生产上,当在一个很大的表上进行索引创建的时候,任何对该表的DML操作都会被夯住!!!

从DBA_DDL_LOCKS视图可以看到,建索引的同时有6级排它DDL锁。

Oracle 11g下ONLINE选项不会堵塞DML操作

版本:11.2.0.3

接着上面的实验,重建索引的时候加上ONLINE,由于会话断开了,重新开2个会话,会话1为22,会话2为142:

Oracle新建或重建索引过程中的锁信息

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle新建或重建索引过程中的锁信息后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部