ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

公司用户反馈一系统在14:00~15:00(2016-08-16)这个时间段反应比较慢,于是生成了这个时间段的AWR报告,

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

如上所示,通过Elapsed Time和DB Time对比分析,可以看出在这段时间内服务器并不繁忙。分析Top 5 Timed Events,我们可以看到前五的等待事件

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

可以看到等待事件enq: TX - row lock contention占了所有等待事件17.3%的比例,猜测有可能是锁等待(enqueue等待)引起的阻塞导致,但是这个还不能下定论,因为毕竟CPU Time和db file sequential read等待事件所占的比例要大。于是我就使用awrddrpt.sql生成了15号与16号同一时段的AWR对比报告。

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

如上所示,16号14:00-15:00的DB Time反而比15号的DB Time小,从Top 5 Timed Events来看, 15号没有enq: TX - row lock contention等待事件,那么很有可能是这个引起的,那么我接下来看看16号14:00-15:00时间段的AWR报告的Wait Events,如下所示,enq: TX - row lock contention的Total Waits Tims(s)为1022秒,Avg Waits(ms)为2848毫秒,说明锁等待(enqueue等待)还是蛮严重的。

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

那么我们去检查Segments by Row Lock Waits,看看是那些对象产生了等待事件““enq: TX - row lock contention”,如下所示,主要是表INV_NEXT_NO,以及索引IDX_INV_SRN_HD_N1

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

另外在bdump的trace文件中发现在14:30左右出现了TNS-12535 & TNS-1260错误,那么我就来生成14:25:00~14:35:00这个时间段的ASH报告来分析一下

Client Address = (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.xxx.xxx)(PORT=44913))

*** 2016-08-16 14:32:36.012

  NS Primary Error: TNS-12535: TNS:operation timed out

  NS Secondary Error: TNS-12606: TNS: Application timeout occurred

kmduicxd: 0x7f381c4bc770, kmduiflg: 1, circuit: 0x3778688f0

  (circuit) dispatcher process id = (0x37f799ef8, 1)

            parent process id = (17, 1)

            serial # = 416

            connection context = 0x7f381c4bc770

            user session = ((nil)), flag = (100c0), queue = (9)

            current buffer = (0), status = (4, 0)

Client Address = (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.xxx.xx)(PORT=60069))

*** 2016-08-16 14:32:58.679

  NS Primary Error: TNS-12535: TNS:operation timed out

  NS Secondary Error: TNS-12606: TNS: Application timeout occurred

kmduicxd: 0x7f381c4bd960, kmduiflg: 1, circuit: 0x377942fd0

  (circuit) dispatcher process id = (0x37f799ef8, 1)

            parent process id = (17, 1)

            serial # = 798

            connection context = 0x7f381c4bd960

            user session = ((nil)), flag = (100c0), queue = (9)

            current buffer = (0), status = (4, 0)

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

ORACLE AWR结合ASH诊断分析enq: TX - row lock contention

可以看出主要是因为下面语句造成的

SELECT NEXT_NO FROM INV_NEXT_NO WHERE PREFIX_CODE = :B1 FOR UPDATE

 

select next_no from inv_next_no where prefix_code = 'SRN-YMG-201608-' for update

想必你一看到FOR UPDATE语句,心里就已经知道了七七八八了,当两个会话或多个会话同时更新某一行时,就会出现enq: TX - row lock contention等待事件,如果其中一个会话迟迟不提交事务或是由于网络等其其它故障出现,那么其它会话就会一直等待这个资源,并发高的情况下,就会出现大量阻塞。这个还真是因为不合理的设计所造成的。因为系统要生成唯一并且连续的单号(前缀+数字),为了获取唯一并且连续的单号,所以使用SELECT FOR UPDATE这种设计来实现,没有使用SEQUENCE(因为SEQUENCE可能会跳号,造成单号不连续),也不能使用其它方式了。如果能修改生成单号的业务逻辑,这个问题就好解决了。

上一篇:2017北京国庆刷题Day4 afternoon


下一篇:[Oracle] enq: TX - row lock contention 优化案例