关于enq: TX - row lock contention行锁的总结

最新推荐文章于 2024-05-08 09:25:04 发布

cpda53349199

最新推荐文章于 2024-05-08 09:25:04 发布

阅读量124

点赞数

常用分析sql语句：
--子查询中 request>0 即是存在锁请求的会话，通过 id1, id2, type 关联 gv$lock，即可将阻塞者和被阻塞者全部列出；
select /* +rule*/
t.INST_ID,
decode(request,0,'holder','waiter') role,
t.SID,
t.TYPE,
t.REQUEST,t.LMODE,t.BLOCK,t.CTIME,t.ID1,t.ID2
from gv$lock t
where (t.ID1,t.ID2,t.TYPE)
in (select t.ID1,t.ID2,t.TYPE from gv$lock where t.REQUEST>0)
order by ctime desc,role;
--wrh$sql_stat 观察过去几小时 SQL 的执行频率、执行计划、单次响应时间、逻辑读等执行统计信息发现 PLAN_HASH_VALUE 未改变意味着执行计划未曾变化，但是 SQL 单次响应时间发生了数量级的变化，从百分秒上升到了秒级，逻辑读也有上升，时间在 CPUtime 上花费较少，较多发生到了 Application 类型等待和 Cluster 类型等待上。
-- 另外 ROWS_PROC 即处理行数，逐步呈上升趋势，看起来是应用端发生了堵塞，每次update的行数变大了
select a.snap_id,a.instance_number inst,to_char(b.end_interval_time,'yyyymmdd hh24:mi:ss') end_time,
a.version_count v_cnt,a.plan_hash_value plan_hash,
a.executions_delta exec,
round(a.elapsed_time_delta/decode(a.elapsed_time_delta,0,1,a.elapsed_time_delta)/1000000,6) elap,
round(a.cpu_time_delta/decode(a.executions_delta,0,1,a.elapsed_time_delta)/1000000,6) cput,
round(a.buffer_gets_delta/decode(a.executions_delta,0,1,a.executions_delta)) bget,
round(a.rows_processed_delta/decode(a.executions_delta,0,1,a.executions_delta),3)rows_proc,
round(a.apwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) apwait,
round(a.clwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) clwait,
round(a.ccwait_delta/decode(a.executions_delta,0,1,a.executions_delta)) ccwait
from sys.wrh$_sqlstat a,sys.wrm$_snapshot b
where a.dbid=b.dbid
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and a.dbid=(select dbid from v$database)
and a.sql_id='2qrkr5gkgd37m'
order by 4,1,3 desc;

在一些事务频繁，并发较高的环境下，为了尽可能减少 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

killdb关于 enq: TX - row lock contention的总结

1. 其原因一般有如下几种:
1) 表上存在主键或唯一性约束,多个会话操作同一条记录

2) 表存在主外键读情况,主表不提交,子表那么必须进行等待.

3) 表上存在位图Index,这跟uniqeue index中存在重复值是一样的道理,其中一个会话操作，其他会话必须等待.

4) 表进行自我外键关联,前面的事务不提交，那么会导致后面的会话一直等待.

2. 对于网上说的enq: TX – row lock contention也有可能是在等待index block分裂的情况，我没有进行测试，从理论上来讲，如果是在等待index block分裂，那么应该还伴有enq: TX – index contention等待事件产生.

3. 对于enq: TX – row lock contention,通过v$session视图查询时，等待会话带lock mode通常为4，而blocker 会话带lock mode通常为6，并且一般查询blocker会话的sql_id都为空。这是正常现象，v$session显示是当前状态, 而非历史数据.

参考文献：
http://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650603515&idx=1&sn=275956ad38d26168e44027336644e5a0&scene=23&srcid=0711qxhIykeqO278x7VZFx5k#rd
http://www.killdb.com/2015/07/13/%e5%85%b3%e4%ba%8eenq-tx-row-lock-contention%e7%9a%84%e6%b5%8b%e8%af%95%e5%92%8c%e6%a1%88%e4%be%8b%e5%88%86%e6%9e%90.html

http://blog.csdn.net/songyang_oracle/article/details/6433753

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25462274/viewspace-2122231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25462274/viewspace-2122231/