一则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

应用程序框架中提交

 

 

原文地址：http://yunlongzheng.blog.51cto.com/788996/411205