一、问题现象:
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
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);
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
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,如需转载请自行联系原作者