一则TX - row lock contention处理详解

简介:

 一、问题现象:

ORACLE OEM巡检发现某时段存在较大的WAITTING事件(如图1的红色部分):

(图1:Database Activity)
其实发现这个问题还可以从v$locked_object、AWR中发现,但要数最直观的还是OEM的ACTIVESESSIONS视图。
 
二、问题分析:
1 、为了定位事件,采集该时段的ADDM 快照(如图2 :开始PM4 ,结束PM7 ):

(图2:Database Activity)
 
2 、直奔主题SQL statements were found waiting for row lock waits (如图3

(图3:SQL statements were found waiting for row lock waits)
通过“Wait class "Application" was consuming significant database time”我们分析由于应用程序的问题导致 enq: TX - row lock contention。再看SQL语句,基本判断是由于未COMMIT导致。
TX 锁通常被称为事务锁,当一个事务开始时,如执行INSERT/DELETE/UPDATE/MERGE 等操作或者使用SELECT ... FOR UPDATE 语句进行查询时,会首先获取事务锁,直到该事务结束。Oracle 的TX 锁定是在行级获得的,每个数据行上都存在一个锁定位(1b-Lock Byte ),用于判断该记录是否被锁定,同时在每个数据块的头部(Header )存在一个ITL 的数据结构,用于记录事务信息等,当需要修改数据时,首先需要获得回滚段空间用于存储前镜像信息,然后这个事务信息同样被记录在ITL 上,通过ITL 可以将回滚信息和数据块关联起来,所以说Oracle 的行级锁定是在数据块上获得的,行级锁只有排他锁没有共享模式。
 
3 、找到根源
为了区分程序调用的来源(存储过程还是直接调用),我们继续点击图2的“SQL statements consuming significant database time were found.”链接(一般WAIT事件这里也会露脸,且如果是存储过程调用的话这里会有存储过程的信息)

(图4:SQL statements consuming significant database time were found)
从图4 发现了可以的存储过程begin PR_WTCM_HOT_COUNT ( :1, :2 );end;
通过PLSQL 查看存储过程如下:
create or replace procedure PR_WTCM_HOT_COUNT(
       p_code_ts in varchar2,
       p_code_t in varchar2
) is
v_count number;
--统计热门商品
begin
 --判断当前的商品编号是否已经存在
 select count(*) into v_count from wtcm_hot_goods_count t where t.code_ts=p_code_ts;
 
 if v_count>0 then
     update wtcm_hot_goods_count t set t.v_count=t.v_count+1 where t.code_ts=p_code_ts;
 else
      insert into wtcm_hot_goods_count(code_ts,v_count,code_t) values(p_code_ts,1,p_code_t);
 end if;
end PR_WTCM_HOT_COUNT;
显然,上面存储过程代码中红色部分即是元凶,存储过程中未发现COMMIT ,如果框架未有提交动作,那么显然多个会话执行时有行锁的风险,为了证实这一想法进行了后续测试。
 
 
三、问题测试:
1 、测试数据:
   模拟测试用例:通过分析存储过程发现,存储过程有两个参数: p_code_ts、p_code_t。我们从测试库中随机抓出一条记录p_code_ts =29031100,p_code_t=29031100。

 

(图5:测试数据)
 
2 、启动一个PLSQL 模拟一个会话:

(图6:模拟会话1)
 
3 、启动另外一个PLSQL 模拟另一个会话:(看到左下角长久处于Excuting ……了么)

(图7:模拟会话2)
4 、查阻塞的会话:
查询语句:
Select osuser,machine,program, Module,sid,serial#,event,t2.logon_time
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid  order  by t2.logon_time
结果如下(图8):

(图8:阻塞会话查询)
显然是enq: TX - row lock contention导致了死锁,基本确定刚刚执行存储过程导致
 
5 提取SQL 语句:
根据sid用如下语句提取:
SELECT /*+ PUSH_SUBQ */ 
Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem,First_Load_Time,  SYSDATE Start_Time
FROM V$sqlarea 
WHERE Address = ( SELECT Sql_Address 
FROM V$session 
Where Sid = 992);
提取结果如下:
(图9:根据SID提取SQL语句)
很显然,语句是:UPDATE WTCM_HOT_GOODS_COUNT T SET T.V_COUNT=T.V_COUNT+1 WHERE T.CODE_TS=:B1这和我们执行的存储过程中的语句一致,证明了该存储过程如果执行后无提交,那么将会引起阻塞。
 
四、解决问题:
1 、应急措施
a.组装杀死锁的语句(dba权限):
select 'alter system kill session '||chr(39)||t2.sid||','||t2.serial#||chr(39)||';'
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid  order  by t2.logon_time

(图10:组装杀死锁的语句)
b.执行杀死锁语句(dba权限):

(图11:执行杀死锁语句)
 
c.确认会话存在否:

(图12:确认会话已经不存在)
 
2 、彻底解决方案
存储过程加COMMIT
应用程序框架中提交


本文转自zylhsy 51CTO博客,原文链接:http://blog.51cto.com/yunlongzheng/411205,如需转载请自行联系原作者
相关文章
|
12月前
|
关系型数据库 MySQL 数据库
lock_read
lock_read
44 2
|
SQL Oracle 关系型数据库
ORACLE等待事件:enq: TX - row lock contention
enq: TX - row lock contention等待事件,这个是数据库里面一个比较常见的等待事件。enq是enqueue的缩写,它是一种保护共享资源的锁定机制,一个排队机制,先进先出(FIFO)。
1704 0
|
OLTP 索引 关系型数据库
故障排除:"enq: TX - index contention" (文档 ID 2331575.1)
故障排除:"enq: TX - index contention" (文档 ID 2331575.1),等待事件
2503 0
|
缓存 Oracle 关系型数据库
|
SQL Oracle 关系型数据库
等待事件之Row Cache Lock
等待事件之Row Cache Lock 定位的办法: --查询row cache lock等待 select event,p1  from v$session where  event= 'row ca...
3677 0