【转载】TX - row lock contention 的一些场景

【转载】TX - row lock contention 的一些场景

TX - row lock contention 的一些场景


【转载】TX - row lock contention 的一些场景

本文整理来自7月7日周四晚云和恩墨大讲堂嘉宾易欣分享的主题:TX - row lock contention 的一些场景,供大家参考。

 

概述

 

在数据库运维过程中,enq: TX - row lock contention 是一个常见的等待事件,特别是 RAC 环境下。对于 enq: TX - row lock contention 等待事件,Oracle 将其归类为 Application类型等待,多数场景下都是由于应用逻辑设计不合理,申请和释放 TXEnqueue 锁出现交叉竞争,影响事务的并发性,造成应用处理效率低下。

 

注:本文档主要讨论 enq: TX - row lock contention 等待事件,TX Enqueue 还包括 index contention, itl contention,other contention 则是由其它原因造成。

 

检查方法

 

1. 参考 SQL 语句:

 

【转载】TX - row lock contention 的一些场景

注:子查询中 request>0 即是存在锁请求的会话,通过 id1, id2, type 关联 gv$lock,即可将阻塞者和被阻塞者全部列出;

 

【转载】TX - row lock contention 的一些场景

注:blocking_session 字段即是阻塞者的会话 id,可以结合 dbms_rowid.rowid_create (1, (select data_object_idfrom dba_objects where object_id=row_wait_obj#), row_wait_file#,row_wait_block#, row_wait_row#) 获取发生锁竞争的 rowid。

 

2. hanganalyze

间隔执行几次 oradebug-g all hanganalyze 3 的方式,可以迅速找到发生阻塞的会话;

 

3. ASH 和 AWR

enq: TX - row lock contention 等待是发生在会话级别的,更适合使用 ASH 方式进行分析,可以看到对应的 SQL,等待事件的 P1、P2,阻塞者等信息(阻塞者有可能看不到,因为它 ACTIVE 的时间很短,或者通常不是 ACTIVE 会话);AWR 报告是对数据库整体负载的报告,可以辅助进行分析,重点关注等待事件的平均等待时间、segmentorder by row wait 部分和 Enqueue 统计部分。

        

一些注意事项:

1)rac 环境下,如果要杀阻塞者会话,需要准确识别 instance,否则可能误杀其他重要进程

2)在查询发生产生锁竞争的 SQL 语句时或具体行时,通常是查询被阻塞者执行的 SQL 语句或等待的行,因为阻塞者,未提交事务,可以未有动作处于 INACTIVE 状态,如果是 ACTIVE 状态,也是去执行别的 SQL 语句了,真正引发锁竞争的 SQL 可能被挤出库缓存,要查到 SQL 语句需要通过像 logmnr 这样的工具去挖日志。

 

测试用例

 

在 MOS 文章 62354.1 中对 enq: TX - row lock contention 等待事件进行了总结,对于单一事务,出现该等待事件,锁的请求模式mode通常有4(share)和 6(exclusive)两种:

 

1. 模式为4(share),通常属于事务约束级别的冲突,比如

 

1)存在主键唯一键,会话1插入数据还未完成 commit,会话2插入相同值,此时进入队列等待;

 

【转载】TX - row lock contention 的一些场景

 

会话状态:

【转载】TX - row lock contention 的一些场景

 

 

2)存在主外建约束(主外建可存在2张表上,也可以是1张表自引用外键),会话1插入父键还未提交,会话2就开始插入相关的外键,此时会话2将进入队列等待。

 

【转载】TX - row lock contention 的一些场景

会话状态:

【转载】TX - row lock contention 的一些场景

 

2. 模式为6(exclusive),通常为会话1在某行上执行 update/delete 未提交,会话2对同一行数据进行 update/delete,或其它原因造成的锁释放速度缓慢,都会造成后续的会话进入队列等待。

 

【转载】TX - row lock contention 的一些场景

会话状态:

【转载】TX - row lock contention 的一些场景

 

小结:从以上测试过程可以看出,请求模式为4的,通常发生在事务级别,对象 ID(row_wait_obj#)通常是-1,而请求模式为6的,发生在行级,对象 ID(row_wait_obj#)对应 dba_objects 中的 object_id。

 

具体场景

 

大多数情况下,出现 enq: TX - rowlock contention 等待,都只能从应用层面调整应用逻辑进行优化,提高事务的并发性。但也有例外,个别场景下,由于 IO、网络等其他原因造成的 SQL 效率低下引发的 enq: TX - row lock contention,是可以从数据库层面进行优化的。

 

1. 网络问题

 

现象:

某医院入院登记系统(C/S 架构),在登记 ABC 病人入院的时候,应用程序夯住登记失败,换了几台机器尝试都不行,但是登记别的病人是没有问题的;

 

分析:

问题时段,并非入院登记高峰期,检查数据库发现,当应用程序登记夯住的时候,数据库中将出现相关会话进入 Enqueue 等待队列,出现 enq: TX - row lock contention 等待事件,查询数据库相关视图,v$lock 中显示被阻塞的会话请求锁模式 request 为4。经过了解,当天由于网络不稳定,17:00 左右应用程序出现问题,被异常关闭过,但是数据库上的进程依然还在,检查 v$transaction 阻塞者会话3322事务开始的时间可以吻合上就是17:00左右:

 

【转载】TX - row lock contention 的一些场景

【转载】TX - row lock contention 的一些场景

 

问题原因:

入院登记的每个用户,身份证是唯一键,刚开始登记 ABC 病人的时候,表上已经插入了一条数据,程序崩溃后,数据库服务器上的用户进程并未正常销毁,导致再次登记 ABC 病人的时候,进入队列等待;

 

处理方式:

了解清楚问题原因后,将阻塞者会话进行 kill,随后应用程序上成功登记了之前登记失败的病人信息。


2. 执行计划问题

 

现象:

某省电力公司生产数据库,经常出现较多 enq: TX - row lock contention 等待事件,应用反映业务操作非常缓慢,严重影响业务处理;

 

分析:

登录数据库进行检查,v$lock 中显示,有多个阻塞者会话(id1 和 id2 值不同,有多个行上发生阻塞),被阻塞会话请求锁模式 request 为6,阻塞者会发生变化,但是比较缓慢。

进一步检查,存在多个阻塞者会话,且并未被其他会话阻塞,等待事件为 read by other session,执行的 SQL 语句为

 

【转载】TX - row lock contention 的一些场景

 

执行计划:

【转载】TX - row lock contention 的一些场景

 

执行计划显示B表的访问方式为索引唯一扫描,A表为全表扫描,其中B表是一个小表,A表相对较大有好几个 GB 大小,A表上 resourceid 选择性非常好,但是却没有索引,SQL 单次响应时间达到了上百秒。

 

问题原因:

因为A表较大 Oracle 没有完全缓存到 buffer cache 中,每次全表扫描都要从磁盘上去读取一些块,业务高峰期,并发执行该 SQL 语句,产生多个全表扫描,IO 开销巨大,导致发生 read by other session 等待事件,由于 SQL 执行效率低下,TX 锁释放缓慢,造成后续会话进入队列等待;

 

处理方式:

优化 SQL 语句,在A表 resource_id 上创建索引,SQL 效率得到改善,enq: TX- row lock contention 等待事件消失。

 

3. 应用问题

 

现象:

某制造业客户,数据库中出现大量 enq: TX - row lock contention 等待事件,客户反映发生锁阻塞的会话杀不干净已接近上千个。

观察 ASH 报告中等待事件的统计(重点关注活动会话数):

 

【转载】TX - row lock contention 的一些场景

 

观察系统异常前后 AWR 对比报告,等待事件的变化情况(重点对比平均等待时间和 DB time 占比):

 

【转载】TX - row lock contention 的一些场景

 

分析:

远程登录数据库进行检查,锁请求模式为6,阻塞者同样在不断变化,检查执行的 SQL 语句:

 


 

SQL 的执行计划为索引范围扫描,索引为组合索引,从执行计划看不存在效率问题,通过wrh$sql_stat 观察过去几小时 SQL 的执行频率、执行计划、单次响应时间、逻辑读等执行统计信息。


【转载】TX - row lock contention 的一些场景

 

发现 PLAN_HASH_VALUE 未改变意味着执行计划未曾变化,但是 SQL 单次响应时间发生了数量级的变化,从百分秒上升到了秒级,逻辑读也有上升,时间在 CPUtime 上花费较少,较多发生到了 Application 类型等待和 Cluster 类型等待上。另外 ROWS_PROC 即处理行数,逐步呈上升趋势,看起来是应用端发生了堵塞,每次update的行数变大了。

 

问题原因:

虽然没有应用程序代码,从数据上看应该是应用逻辑上的问题。建议客户从应用层面进行了检查,发现其中有一台应用服务器,近似疯狂的发起请求,并发请求过高,应用层面处理不过来了;数据库层面由于负载均衡,分散到了数据库 RAC 两个节点上,集群竞争较明显时,DML 操作受影响愈明显。当会话1开始事务操作后(未提交),会话2开始事务操作,如果发生行锁竞争,需要等会话1事务完成后,利用 undo 构造一致性,会话2才开始事务操。如果这两个会话在不同节点上,必须要等 redo 从缓冲区刷到磁盘上,才能将相关的数据块传输到其他节点上:

 

【转载】TX - row lock contention 的一些场景

 

所以主要原因是应用请求异常,数据堆积,单次处理数据量变大,其次再加上 RAC 群间的竞争,导致 enqueue 锁获取和释放的时间拉长,出现会话排队现象。

 

处理方式:

明确问题后,客户对应用服务器进行了调整,处理了发起异常请求的功能模块后,数据库中 enq: TX - row lock contention 等待事件逐步减少直至消失。

 

4. 其他问题

 

1)错误的在 dml 频繁的表上建立位图索引;

2)使用 forupdate 不够合理,导致 for update 的范围过大;

 

比如在多表关联后 forupdate:

【转载】TX - row lock contention 的一些场景

 

无 where 限制条件的 for update:

【转载】TX - row lock contention 的一些场景

 

总结

 

综上所述,在一些事务频繁,并发较高的环境下,为了尽可能减少 TX - row lock contention 等待事件的发生,应当从应用设计到数据库多个层面进行考虑。

 

应用层面:

1、约束通常是为了保证数据完整性,在并发场景下,应充分考虑事务进行的逻辑顺序,避免多个会话事务交叉进行,触发约束冲突在事务级别发生竞争;

2、要提高并发效率,应当尽可能拆分大事务为小事务,提高 tx enqueue 锁的获取释放速度;

3、如果要使用悲观锁(for update),应尽可能减少锁定的行范围;

 

数据库层面:

1、在 dml 频繁的表上建立适当的索引,提高 SQL 执行的效率,减少 tx enqueue 锁持有的时间;避免全表扫描这种,容易造成 IO 开销巨大,热块竞争,会话堆积的访问方式。

2、在 dml 频繁的表上不应使用位图索引;

3、对于 dml 频繁的表,不应使用 IOT 表,物化视图等;

4、RAC 环境下,对于批量的 dml 操作,尽可能固定在单一节点,尽量降低网络开销、集群竞争、一致性块获取和日志刷盘等带来的影响。

 

Eygle 最后补充两点:

 

1. 易欣在分享的内容中用到了 AWR 对比报告,这个报告非常实用,大家如果没有用过,可以认真研究一下。用 $ORACLE_HOME/rdbms/admin/awrddrpt.sql 可以生成。

 

2. 在 RAC 环境下,易欣讲到的第3个案例,由于锁竞争带来的复杂性会极具放大。Cache Fusion 的机制更复杂。我把一个非常精彩的文档分享给大家吧,读懂这个文档,大家对于 RAC 和 LOCK 的理解一定会更上一层楼。易欣引用的一个图就是来自这个文档。

 

Understanding Oracle RAC Internals - The Cache Fusion Edition,这个文档非常精彩,Markus 是 RAC 的产品经理,大家一定有时间认真读一下:http://pan.baidu.com/s/1i4SW4XR

posted @  2016-09-07 00:29  ^_^小麦苗^_^ 阅读(  ...) 评论(  ...)  编辑  收藏

上一篇:【转载】 机器学习数据可视化 (t-SNE 使用指南)—— Why You Are Using t-SNE Wrong


下一篇:Assignment One for CS5223 June 2021