的片段现象亚洲城ca88手机版下载地址

TX – row lock contention 的一对情形

原创 2016-07-11 易欣 云和恩墨

亚洲城ca88手机版下载地址 1
易欣(Eson)

云和恩墨技术专家

 

本文整理来自一月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 语句:

 

亚洲城ca88手机版下载地址 2

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

 

亚洲城ca88手机版下载地址 3

注: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插入相同值,此时进入队列等待;

 

亚洲城ca88手机版下载地址 4

 

对话状态:

亚洲城ca88手机版下载地址 5

 

 

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

 

亚洲城ca88手机版下载地址 6

对话状态:

亚洲城ca88手机版下载地址 7

 

  1. 形式为6(exclusive),常常为会话1在某行上实施 update/delete
    未提交,会话2对同一行数据举行update/delete,或任何原因导致的锁释放速度缓慢,都会造成后续的对话进入队列等待。

 

亚洲城ca88手机版下载地址 8

对话状态:

亚洲城ca88手机版下载地址 9

 

小结:从以上测试过程可以见见,请求模式为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左右:

 

亚洲城ca88手机版下载地址 10

亚洲城ca88手机版下载地址 11

 

题材由来:

入院挂号的各类用户,身份证是唯一键,刚最先登记 ABC
病人的时候,表上已经插入了一条数据,程序崩溃后,数据库服务器上的用户进程没有正常销毁,导致再度报了名
ABC 病人的时候,进入队列等待;

 

处理模式:

刺探精通问题原因后,将阻塞者会话进行kill,随后应用程序上得逞注册了在此以前登记失利的患儿音信。

2. 实施计划问题

 

现象:

某省电力集团生产数据库,日常出现较多 enq: TX – row lock contention
等待事件,应用反映工作操作分外缓慢,严重影响工作处理;

 

分析:

签到数据库举办检讨,v$lock 中显得,有五个阻塞者会话(id1 和 id2
值不同,有两个行上发出短路),被卡住会话请求锁情势 request
为6,阻塞者会暴发变化,可是相比缓慢。

更是检查,存在多个阻塞者会话,且没有被其他会话阻塞,等待事件为 read by
other session,执行的 SQL 语句为

 

亚洲城ca88手机版下载地址 12

 

实施计划:

亚洲城ca88手机版下载地址 13

 

推行计划呈现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 报告中等候事件的总结(重点关注活动会话数):

 

亚洲城ca88手机版下载地址 14

 

观望系统很是前后 AWR
相比报告,等待事件的变迁处境(重点比较平均等待时间和 DB time 占比):

 

亚洲城ca88手机版下载地址 15

亚洲城ca88手机版下载地址, 

分析:

长距离登录数据库举行反省,锁请求情势为6,阻塞者同样在不停变更,检查实施的
SQL 语句:

 

亚洲城ca88手机版下载地址 16

 

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

亚洲城ca88手机版下载地址 17

 

发现 PLAN_HASH_VALUE 未改变意味着执行计划没有变化,可是 SQL
单次响应时间发出了数额级的变动,从百分秒上升到了秒级,逻辑读也有上升,时间在
CPUtime 上消费较少,较多发生到了 Application 类型等待和 Cluster
类型等待上。其它 ROWS_PROC
即拍卖行数,逐渐呈上升趋势,看起来是应用端发生了堵塞,每一遍update的行数变大了。

 

问题由来:

即使尚无应用程序代码,从数据上看应该是应用逻辑上的题材。指出客户从使用范围开展了自我批评,发现中间有一台应用服务器,近似疯狂的发起呼吁,并发请求过高,应用规模处理不过来了;数据库层面由于负载均衡,分散到了数据库
RAC 六个节点上,集群竞争较显眼时,DML
操作受影响愈分明。当会话1从头作业操作后(未提交),会话2方始业务操作,假如爆发行锁竞争,需要等会话1事情完成后,利用
undo 构造一致性,会话2才起来事务操。如若那五个会话在不同节点上,必须要等
redo 从缓冲区刷到磁盘上,才能将有关的数量块传输到其他节点上:

 

亚洲城ca88手机版下载地址 18

 

于是重要缘由是行使请求相当,数据堆积,单次处理数据量变大,其次再添加 RAC
群间的竞争,导致 enqueue 锁获取和自由的刻钟拉长,出现会话排队现象。

 

处理模式:

明朗问题后,客户对应用服务器举办了调整,处理了倡导至极伸手的功用模块后,数据库中
enq: TX – row lock contention 等待事件逐渐调减以至消失。

 

4. 其他题材

 

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

2)使用 forupdate 不够客观,导致 for update 的限量过大;

 

比如在多表关联后 forupdate:

亚洲城ca88手机版下载地址 19

 

无 where 限制条件的 for update:

亚洲城ca88手机版下载地址 20

 

总结

 

归结,在部分工作往往,并发较高的条件下,为了尽可能减弱 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 可以转移。

 

  1. 在 RAC
    环境下,易欣讲到的第3个案例,由于锁竞争带来的扑朔迷离会极具放大。Cache
    Fusion
    的机制更扑朔迷离。我把一个特别精美的文档分享给我们呢,读懂这些文档,我们对此
    RAC 和 LOCK 的掌握一定会立异。易欣引用的一个图就是发源这一个文档。

 

Understanding Oracle RAC Internals – The Cache Fusion
Edition,那个文档非凡可观,马克us 是 RAC
的制品首席营业官,我们肯定有时光认真读一下:http://pan.baidu.com/s/1i4SW4XR