话说,技术人生系列文章已经来到了第三十一期,小编我跟着一路走来,也算是小小的oracle技术达人了;
这天主编大人兴致冲冲的过来问我道,考你一个小问题,说“ ORACLE中 两个表T1,T2,对于语句select * from t1,t2 和select * from t2,t1有什么区别吗? ”
对于这个问题,小编我虽然二十几年没搞过ORACLE数据库,但是编辑/阅读了三十期的技术人生系列,我也是知道oracle有个东西叫CBO,对于SQL文本中这种简单的调换位置对语义和执行计划是完全没有影响的,于是我的回答也很坚决果断:“ 没有区别! ”
主编大人露出诡异的笑容道: “知道死锁吗?这个问题跟死锁有关哟。你先编辑这期的文章吧,编辑完你就知道有没有区别了。 ”
什么?这么神奇吗?小编我学点ORACLE容易吗我?你们不要动不动就毁我三观好吗?我倒要看看今天的CASE又是遇到了什么样的奇葩问题。
问题来了
一大早,老K还没有睡醒,电话就来了,看来数据中心又有问题了!接起电话,
“不好了,XX系统的批量到现在还跑完,现在业务慢慢多起来了,这个要快速帮我们解决,不然过一会儿就要扛不住了!”
“不着急,先把等待事件的情况发一张过来看一眼吧。”
如此严重的library cache lock等待,倒是很少见,不过如果只是为了解决问题,倒是也没有那么麻烦,快速启用我们的应急方案即可;应急方案之后,系统也就恢复了正常,接下来要做的,便是找到问题的根因;
小小知识点
什么是library cache lock等待(本文后续就简称为LCL)?
当一个会话对library cache中的对象(主要是TABLE /INDEX/CLUSTER/PROCEDURE等)进行修改(通常是指DDL操作)时,会以X模式持有该对象的library cache lock;当一个会话在解析sql需要用到某个对象时,会以S模式请求该对象的library cache lock;
我们的应急方案是什么?
应急方案是我们针对客户的不同故障场景为客户现场人员提供的傻瓜式的操作流程,最终目的是帮助客户尽快恢复业务,同时保留足够的信息来帮助定位问题的根本原因;针对这一问题的应急方案是,通过x$kgllk视图查到所有持有library cache lock的会话,并kill 掉,在此之前先收集各节点266 level的systemstate dump(SSD);
接下来如何分析问题的根本原因?
接下来要做的就是分析systemstate dump内容,找到根因;有很多小伙伴说,systemstate dump不用自己去读,使用ass109.awk来帮助我们分析dump会更快,但是针对这个CASE,我们会发现,ass109.awk对我们理解问题,以及更深层次的分析该问题帮助极其有限,毕竟它只是一个固定的脚本。
SSD分析从来都不困难
对于不了解数据库systemstate dump如何收集与分析的朋友,可以再次阅读技术人生系列文章第七、八期 《 》 及第二十七期 《 》 来了解systemstate dump的分析方法。
同时,我们确认这里的case环境:
版本:ORACLE 11.2.0.3 PSU7
操作系统:AIX 6.1
那些LCL等待
我们这里分析SSD来就简单了,首先来看看library cache lock等待事件的整体情况具体是什么样。我们通过搜索关键字 “ waiting for 'librarycache lock'\n handle address= ” 分别在一二节点产生的dump文件中查找;查找后的结果去掉大量的重复后显示如下 :
除了上面情况外,我们还发现,节点二上有点特殊情况,有两个会话分别等在了3模式的library cache lock上了 :
综合上述情况,我们总结一下:
总体情况
1) 在节点一等待的主要是两个handle address 下的对象:0x7000000 fcc06268 和0x7000001b7c442c0
2) 请求的模式均为002 模式,即共享模式
3) 在节点二等待的主要是两个handle address下的对象:0x700000 1b2d027e0 和0x700000 1b77d4440
4) 请求的模式均为002模式,即共享模式
5) 在二节点上还有对上述两个handle address下对象 003模式 的library cache lock等待,即独占模式的请求等待
找到LCL涉及的对象
在两个节点上等待的对象的handle address共计有四个,那么这四个分别是什么具体的对象呢,我们直接使用handle address在各自dump文件中搜索即可找出:
可以清楚的看到,两个节点上等待的library cache对象只涉及两个表,分别是XXTLOG和XXTT01,只不过因为是不同的实例, handle address 不一致, 不过,同一对象在不同实例上的 hash 值是一致的 ;
找到它们的阻塞关系
看起来没有那么麻烦,虽然产生的library cache lock等待有500多个,但是实际上只涉及两个表而已;所谓锁资源,总归是有持有者,持有者没有及时释放,新的请求模式如果与原来的持有模式相冲突,这个时候,等待事件就出现了。
会话那么多,我们应该怎么下手呢?我们的方法是, 找特殊的那些位 !接下来我们把目光投向了二节点上,以独占模式请求library cache lock的那两个特殊的会话,对其进行全方位的分析;
在二节点的dump文件中还是通过刚才的关键字”handle address=0x7000001b77d4440, lock address=0x700000045728758,100*mode+namespace=0x4d66500010003”搜索的以X模式librarycache lock的会话信息:
特殊会话
1) 实例 2 上会话 926比较特殊
2) 它正在以 X 模式请求 XXTT01 上的 librarycache lock ;
3) 它当前执行的 SQL 为 0x7000001b4a072a8 ;
4) 它已经等待 librarycache lock 210 分钟了!
5) 它的直接阻塞者是 1 节点的 266 会话
6) 它真正执行的语句为收集表 XXTT01 的统计信息(可以注意到,统计信息按分区收集)
同样的搜索方法,我们还能看到另一个以X模式请求library cache lock的会话:
特殊会话
1) 实例 2 上会话 1190
2) 它正在以 X 模式请求 XXTLOG 上的 librarycache lock ;
3) 它当前执行的 SQL 为 0x700000046b12f50 ;
4) 它等待 librarycache lock 也是 210 分钟!
5) 它的直接阻塞者是 1 节点的 662 会话
6) 它真正执行的语句为收集表 XXTLOG 的统计信息(同样是按分区收集)
以上会话有直接的阻塞者,那么同样,阻塞者们在干什么呢?以一节点的266会话为例:
阻塞者会话1
1) 实例 1 上会话 266
2) 它正在以 S 模式请求 XXTLOG 上的 librarycache lock ;
3) 它当前执行的 SQL 为 0x7000001b6382510 ;
4) 它等待 librarycache lock 也是 210 分钟!
5) 它没有直接阻塞者,但是我们可以看到它阻塞了 2 节点的 926 会话
6) 它真正执行的语句类似“ select…. from XXT49,XXTLOG,XXTT01 where … ”
7) 它为什么会阻塞 2 节点的 926 会话呢,因为它以 S 模式持有了 XXTT01 表上的 librarycache lock ,与 926 请求的 X 模式 library cache lock 不兼容啊
用同样的方法,我们再找到另一个阻塞者会话662:
阻塞者会话2
1) 实例 1 上会话 662
2) 它正在以 S 模式请求 XXTT01 上的 librarycache lock ;
3) 它当前执行的 SQL 为 0x700000117cd9cc0 ;
4) 它等待 librarycache lock 也是 210 分钟!
5) 它没有直接阻塞者,但是我们可以看到它阻塞了 2 节点的 1190 会话
6) 它真正执行的语句类似“ select…. from XXTT01,XXTLOG where … ”
7) 它为什么会阻塞 2 节点的 1190 会话呢,因为它以 S 模式持有了 XXTT01 表上的 librarycache lock
是不是已经晕掉了,这么多怎么搞清楚呢?没有关系,我们简单的画一幅图,来看看现在的等待关系;
看起来,这两个阻塞链是很简单的,inst_1上的会话正在解析语句,以 S模式持有 了表的LCL,收集统计信息时需要 以X模式请求 LCL,模式冲突,然后发生了阻塞!
但是,这里有个问题没有解释:
inst_1上的会话(如266和662)自己也在等待librarycache lock,它们又是被谁给阻塞了呢?
inst_1/inst_2上还有那么多以共享模式请求librarycache lock的会话又是被谁给阻塞了呢?
查找两个实例的dump文件,我们都没有发现有进程以X模式持有两个表的library cache lock,也就是说按理,应该不会产生阻塞才对啊?
看到这里,我们不妨来想一想,想一想,想一想,难道哪里出错了吗,为什么会请求不到S模式的library cache lock呢?
…………..
…………..
…………..
…………..
…………..
…………..
…………..
…………..
真相从来都很简单
思考过后,大家不妨看看我们往期的文章《 》,是不是就豁然开朗了,这里我们再简单的复述一下:
1.LCL虽然不是事务锁,但其终究是一种类似enqueue的机制,这种机制的最大特点就是先进先出的队列的特征;
2. 假设当前会话1以S模式持有了某对象的LCL,随后会话2以X模式请求该对象的LCL,那么显然会话2会被会话1阻塞;此后会话3再以S模式请求该对象的LCL,以队列先进先出的特性,oracle认为会话2将会先于会话3以X模式持有LCL,会话3也就被会话2阻塞了;
我们重新画一下图如下:
再调整一下,摆正一下位置:
当我们看到一个环形的等待时,我们就能清楚的意识到,这就是一个死锁,那我们来看看我们到底是遇到了什么情况;
啥子情况哟
1) inst_1,sid_266 正在解析语句,语句中将先后获取 XXTT01 , XXTLOG 上的 library cache lock ,在获取 XXTT01 上的 LCL 后, 接下来它将请求 XXTLOG 上的 LCL ;
2) inst_2,sid_926 正在收集表 XXTT01 的统计信息,过程中将会请 XXTT01 上独占模式的 LCL ,被 inst_1,sid_266 阻塞;
3) inst_1,sid_662 正在解析语句,语句中将先后获取 XXTLOG, XXTT01 上的 LCL ,此前它已经拿到了 XXTLOG 上的 LCL ,此时请求 XXTT01 上的 LCL ,被 install_2,sid_926 阻塞;
4) inst_2,sid_1190 正在收集表 XXTLOG 的统计信息,过程中将会请求 XXTLOG 上独占模式的 LCL ,被 inst_1,sid_662 阻塞;
5) 回到 inst_1,sid_266 ,它此时请求 XXTLOG 上的 LCL ,正好被 inst_2,sid_1190 阻塞,一个闭环 / 死锁形成;
6) 其他外围的会话,解析相关语句时,请求 XXTT01 和 XXTLOG 表上共享模式的 LCL ,将分别被 inst_2,sid_926 和 inst_2,sid_1190 阻塞;
由此,我们再次看到,统计信息收集带来的问题;根据进一步分析问题前后的语句执行情况和与开发的沟通情况,可以了解到,死锁环中的查询语句和统计信息收集语句都是其批量任务中的语句,而批量任务是并发执行的,也就是说批量任务是按照省份/机构同时启动多达数十个会话,每个会话独立执行查询/DML语句,在对相关数据的大量修改后,再对上文中提到的表(XXTLOG/XXTT01)的指定分区进行统计信息收集工作,以避免后续SQL执行计划出现偏差;这也就意味着,不论是上述的的查询语句还是统计信息收集的语句,并发度都是非常高的,这就增大了LCL死锁的可能性!那么对于这样的问题的解决方案在哪里呢?
解决方案之神来之笔
我们看到,死锁环中关键的一环便是收集统计信息!如果没有统计信息收集过程中要以X模式来请求LCL插入这一脚,其他的语句解析过程也不过就是大家都在以S模式来请求LCL而已,互相兼容,完全不会有死锁这回事。那么,我们这里能不能取消掉统计信息收集的步骤呢?答案是否定的!通过对该系统的问题回顾,我们很快就发现,统计信息收集的动作,正是因为之前遇到了执行计划的问题,无法通过加hint/固定执行计划/拷贝统计信息等其他方式来帮助解决,必须使用及时的收集统计信息的方式来解决。此路不通,我们则需要再想它法了。
还有什么方法呢?停下来想一想......
......
......
......
......
......
......
这里,我们不妨回归死锁的本质,关于死锁,最简单的模型就是会话1持有A资源,请求B资源,会话2持有B资源,请求A资源,这个时候死锁就形成了(当然,在这个过程中请求与持有要是互斥的);来看看我们这里的案例,我们抛开统计信息收集的部分,实际上可以看到,类似的死锁条件是:
1) inst_1,sid_266持有XXTT01的LCL资源,请求XXTLOG的library cache lock资源
2) inst_1,sid_662持有XXTLOG的LCL资源,请求XXTT01的library cache lock资源
那我们来想一想,如果我们让所有要同时请求两个资源的会话都以同样的顺序来请求这些资源,是不是就不会形成死锁了呢:
1) inst_1,sid_266持有XXTT01的LCL资源,请求XXTLOG的library cache lock资源
2) inst_1,sid_662持有XXTT01的LCL资源,请求XXTLOG的library cache lock资源
想一想,是不是这个道理呢?
启发原理
事实上,oracle的机制中就有这样的实现方法;oracle在实现latch的请求是,给不同的latch分了不同的level,在进程持有高级别的latch的时候是不能请求低级别的latch的,必须释放高级别的latch之后,重新从低级别的latch开始申请,从而从根本上避免了latch的死锁。
那么,新的问题就来了,在解析一个涉及多表的语句是,必然最终会需要同时持有多个表的LCL,那么会话是以什么样的顺序来请求各个表的LCL的呢?我们来看看两个会话解析的语句的不同之处:
1) inst_1,sid_266解析的是”select … from XXTLOG,XXTT01 where ..”语句,先持有了XXTT01,而后请求XXTLOG不得;
2)inst_1,sid_662解析的是”select … from XXTT01,XXTLOG where ..”语句,先持有了XXTLOG,而后请求XXTT01不得;
总之,我们可以了解到,语句解析过程中对对象的library cache lock的请求是按SQL文本中从后到前的顺序来完成的(此处老K也是经过了严格测试确认的),那么我们这里避免出现这种死锁情况的方法就简单了!
不一样的解决方案
针对这个CASE,我们调整批量过程中类似的语句,保证文本中
”from”
后
XXTT01
和
XXTLOG
出现的先后顺序是一致的即可!所以,我们最后给客户的建议是,不用调整应用逻辑,不用改变统计信息收集的现状,只需要对批量任务SQL的文本中表出现的先后顺序进行调整即可,并不影响批量的结果和执行过程中的执行计划等。同时,我们也建议大家在开发的过程中,对于同一系统中,
SQL
涉及多表时,如果没有特殊的条件(如
left join/exists
等)时,可以尽量保证表名在语句中出现的顺序一致,避免出现类似的死锁。
不思考是没有前途的
到这里,问题算是基本解决了,这里的解决方案利用了批量语句中SQL调整代价较小的特点,可以得以方便的解决;那么,如果我们遇到的其它情况(如因为有exists条件等),语句中表出现的顺序无法调整,这里的问题就不可避免,需要人工干预来解决吗?看官们不妨也来思考思考!这里还隐藏的几个问题是什么?
………..
………..
………..
………..
………..
………..
………..
………..
………..
好了,思考过后,看看老K对这个问题进一步的思考吧。
问题 1: 真的要等那么长的时间吗?
本case中,我们可以看到,问题的发生是在非工作时间/业务低峰期,但是因为其迟迟没有解决,一直持续了210分钟来到了业务相对高峰的时段,导致问题扩大化,系统出现联机业务受影响的情况;如果没有人工干预,我们是不是要一直等下去,等到地老天荒呢?显然不是!如果我们再次仔细读过《 》这一期,我们会发现oracle实际上有一个错误叫ORA-4021,错误的意义即为"timeout occurred while waiting to lock object %s%s%s%s%s",而在第二十二期中,librarycache lock的等待超时时间是15分钟,那这里为什么我们过了210分钟还是没有出现这个报错呢?看起来这里是有bug存在的!通过MOS搜索,我们很快就能找到bug的相关信息。
原来,library cache lock/pin等待正常是有等待超时时间的,该超时时间由参数”_kgl_time_to_wait_for_locks”决定,通常这个值默认是15分钟,这里因为11.2.0.3上的bug导致RAC跨节点的librarycache lock不会出现自动超时的情况;
所以,针对我们的问题1,打上相应的补丁,问题就能解决。
问题2:死锁为什么不能解决呢?
通常来说,对于事务锁(TX/TM等),当会话在等待队列中时,oracle会自动检测是否存在死锁的情况,如果出现了死锁,oracle会通过杀掉死锁闭环中的某个会话来解决整个死锁,并抛出ORA-60或者全局死锁的错误,那么针对library cache lock呢?出现了死锁oracle是怎么解决的呢?我们先来看下面的图:
我们可以看到,对于事务锁,会有死锁的情况,被发现后会被抛出ORA-60的错误;同样对于library cache lock,死锁被发现后会抛出ORA-4020的错误;当然,这里的前提是,死锁被发现了;那么这里的死锁为什么没有被发现呢?也跟上面的bug一样是因为RAC的原因吗?我们先到alert日志中搜索相关的报错,很快看到了下面的信息:
可以看到,在批量的执行过程中,oracle能发现有全局的deadlock的,那么这些deadlock是不是library cache lock的deadlock呢,我们截取小部分信息看到如下:
没错,就是library cache lock的deadlock!而且涉及的语句和死锁情况与我们遇到的情况看起来是极其相似的,那么为什么library cache lock的deadlock有的时候是可以被发现的?有的时候又不能被发现呢?
显然,这里bug又出现了。只不过,这一次我们通过MOS搜索并没有找到匹配的bug;这里,老K做了大量的测试,在测试过程中可以完全重现library cache lock跨节点死锁而又不会被发现的情况;对于两节点RAC,触发这种bug的前提是:
1) 共享锁的持有者在该锁资源的非master节点上(如本case中的inst_1,sid_266)
2) 独占锁的请求者在该锁资源的master节点上(如本case中的inst_2,sid_1190)
3) 同时在锁资源的master节点上还有其他的共享锁的请求者被阻塞(如本case中的“其它会话”)
而且可以判断,整个过程是因为LMD进程的原因无法发现deadlock,根据上述触发前提, 我们还可以在 RAC 模拟上重现出 TM 锁的死锁无法被发现的情况 ,整个测试过程可以在oracle 11.2.0.4的最新PSU中重现, 可以初步断定为 ORACLE 的新 bug ; 因为微信文章篇幅的原因,这里省略3万字以上,不详述整个CASE的模拟过程了。
总结一下
好了,CASE分享到这里,我们告一段落,回想整个CASE,涉及的东西太多,无法在文章中一一描述到那么细致,甚觉可惜。不过,我们还是能看到,在分析问题过程中,对systemstate dump的阅读,以及以往经验的积累的重要性;到这里,我们再来看看抛给小编的问题?如果有人问你同样的问题,你会有什么样的思考呢?
“ ORACLE中 两个表T1,T2,对于语句select * from t1,t2 和select * from t2,t1有什么区别吗? ”
再看一眼,我们这里的死锁是怎么发生的吧...
本文转载于中亦安图
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31547066/viewspace-2222492/,如需转载,请注明出处,否则将追究法律责任。