`
cloudtech
  • 浏览: 4814505 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
文章分类
社区版块
存档分类
最新评论

一次大量enq: TX - row lock contention锁等待的问题

 
阅读更多
今天下午接到业务报障，系统出现问题，可能是数据库的问题
1，登录系统,查看等待事件，大量row lock
6:12:58]
[16:12:58] SID SERIAL# OSUSER USERNAME SVRPROC SQL_HASH_VALUE EVENT P1 P2 P3
[16:12:58]------ ---------- -------- -------- ------------------------ -------------- ------------------------------ --------------------- ---------- ----------
[16:12:58] 245 4637 app SOi 41353726 3961605653 db file sequential read 226 253846 1
[16:12:58] 3645 58373 app SOi 61604288 1523472697 db file sequential read 178 3443786 1
[16:12:58] 5651 26047 app PAR 64291328 2455471834 db file sequential read 142 2712414 1
[16:12:58] 13207 5781 app SOi 62915316 3349830783 db file sequential read 211 1462130 1
[16:12:58] 10198 64603 app PAR 45679292 2510471152 db file sequential read 91 2141228 1
[16:12:58] 11528 8197 app PAR 51708698 2284416154 db file sequential read 126 1683178 1
[16:12:58] 8863 53455 baoyb AIM 16319376 1467486395 db file sequential read 115 2078933 1
[16:12:58] 1 59191 app SOi 46072062 2644787661 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 22 26111 app SOi 48694114 3874015681 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 39 38595 AIC SOi 7210748 208342339 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 62 56753 AIC SOi 29426814 1088173244 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58]
[16:12:58] SID SERIAL# OSUSER USERNAME SVRPROC SQL_HASH_VALUE EVENT P1 P2 P3
[16:12:58]------ ---------- -------- -------- ------------------------ -------------- ------------------------------ --------------------- ---------- ----------
[16:12:58] 66 42397 app SOi 48366550 1088173244 enq: TX - row lock contention 1415053318 32047109 25967
[16:12:58] 189 52823 AIC SOi 38404948 208342339 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 212 451 app SOi 42402934 673329617 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 232 55865 app SOi 21956094 673329617 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 240 701 AIC SOi 42991652 208342339 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 247 6333 app SOi 5375522 3538716681 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 388 17499 app SOi 42140914 1763411819 enq: TX - row lock contention 1415053318 21889041 50009
[16:12:58] 389 50591 app SOi 33292644 208342339 enq: TX - row lock contention

2，使用锁等待的脚本查看，holder比较多，难以判断根源的holder

[16:16:03]SESS                                                     ID1        ID2      LMODE    REQUEST TY
[16:16:03]------------------------------------------------- ---------- ---------- ---------- ---------- --
[16:16:03]Holder:  7205                                       21037088      51133          6          0 TX
[16:16:03]Waiter: 14692                                       21037088      51133          0          6 TX
[16:16:03]Waiter: 6246                                        21037088      51133          0          6 TX
[16:16:03]Waiter: 1190                                        21037088      51133          0          6 TX
[16:16:03]Holder:  12643                                      21299226      52395          6          0 TX
[16:16:03]Waiter: 3820                                        21299226      52395          0          6 TX
[16:16:03]Waiter: 10197                                       21299226      52395          0          6 TX
[16:16:03]Waiter: 1926                                        21299226      52395          0          6 TX
[16:16:03]Holder:  7169                                       21561365      54540          6          0 TX
[16:16:03]Waiter: 13929                                       21561365      54540          0          6 TX
[16:16:03]Waiter: 1562                                        21561365      54540          0          6 TX
[16:16:03]
[16:16:03]SESS                                                     ID1        ID2      LMODE    REQUEST TY
[16:16:03]------------------------------------------------- ---------- ---------- ---------- ---------- --
[16:16:03]Waiter: 9080                                        21561365      54540          0          6 TX
[16:16:03]Holder:  8863                                       21889041      50009          6          0 TX
[16:16:03]Waiter: 14914                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14911                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14902                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14882                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14872                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14865                                       21889041      50009          0          6 TX
[16:16:03]Waiter: 14721                                       21889041      50009          0          6 TX


3，由于是rac，做一个全局的HANGANALYZE
[16:16:57]SQL> ORADEBUG setmypid
[16:16:57]Statement processed.
[16:17:02]SQL>  ORADEBUG setinst all
[16:17:03]Statement processed.
[16:17:07]SQL> ORADEBUG -g def hanganalyze 3
[16:17:26]Hang Analysis in /oracle/diag/rdbms/yydb/yydb1/trace/yydb1_diag_4260840.trc
4，此时被阻塞的语句是大量update
16:17:35]SQL_ID        PCTLOAD  CPU OTHER   IO SQL_TEXT
[16:17:35]------------- ------- ---- ----- ---- ----------------------------------------------------------------------------------------------------
[16:17:35]9bxujnufu8hfd      17    0   100    0 update INS__100 set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
[16:17:35]8vcrwd10dsd5w      15    0   100    0 update INS__101 set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
[16:17:35]777xsjn66q3a3      10    0   100    0 update INS__102 set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
[16:17:35]3xng1922jhqdx      10    0   100    0 update INS__103 set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
[16:17:35]5g2gyxwn24cfj       8    0   100    0 update INS__100 set OP_ID = :1 ,STATE = :2 ,DONE_DATE = :3  where   ROWID = :4
[16:17:35]ac17casyksfka       5    0   100    0 update INS__100 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,STATE = :3 ,COUNTY_CODE = :4 ,DONE_DA
[16:17:35]7pdp5zfbc7bh5       4    0   100    0 update INS__104 set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
[16:17:35]024cfqtnjr1vb       3    0   100    0 update INS__103 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,OP_ID = :3 ,STATE = :4 ,COUNTY_CODE =
[16:17:35]24m4b9jfqn2zm       2    0   100    0 update INS__101 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,OP_ID = :3 ,STATE = :4 ,COUNTY_CODE =
[16:17:35]8vm3vv2t6ymv6       2    0   100    0 update INS__102 set STATE = :1 ,OP_ID = :2 ,DONE_DATE = :3  where   ROWID = :4
[16:17:35]cnq4y75v772af       2    0   100    0 update INS__103 set EXPIRE_DATE = :1 ,EFFECTIVE_DATE = :2 ,STATE = :3 ,COUNTY_CODE = :4 ,DONE_DA

5，查看HANGANALYZE log的内容，339 sessions被sid=8863的进程阻塞
.......
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'db file scattered read'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x13c8fc65
 [b] Chain 2 Signature: 'db file scattered read'<='enq: TX - row lock contention'
     Chain 2 Signature Hash: 0x13c8fc65
 [c] Chain 3 Signature: 'db file scattered read'<='enq: TX - row lock contention'
     Chain 3 Signature Hash: 0x13c8fc65

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (yydb.yydb1)
                   os id: 46072062
              process id: 320, oracle@scyydb1
              session id: 1
        session serial #: 59191
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x14e0011
                      p3: 'sequence'=0xc359
            time in wait: 73 min 18 sec
           timeout after: never
                 wait id: 5488
                blocking: 0 sessions
             current sql: update INS_ set STATE = :1 ,DONE_DATE = :2  where   ROWID = :3
             short stack: ksedsts()+644<-ksdxfstk()+44<-ksdxcb()+3384<-sspuser()+116<-47dc<-sskgpwwait()+32<-skgpwwait()+180<-ksliwat()+11032<-kslwaitctx()+1
80<-kjusuc()+3652<-ksipgetctxi()+1892<-ksqcmi()+21888<-ksqgtlctx()+2904<-ksqgelctx()+660<-ktuGetTxForXid()+184<-ktcwit1()+456<-IPRA.$kdddgb()+6860<-kdddgb()+
96<-kdusru()+8516<-updrowFastPath()+1192<-qerupFetch()+2596<-updaul()+1100<-updThreePhaseExe()+336<-updexe()+344<-opiexe()+14476<-kpoal8()+4616<-opiodr()+720
<-ttcpip()+1028<-opitsk()+1508<-opiino()+940<-opiodr()+720<-opidrv()+1132<
            wait history:
              * time between current wait and wait #1: 0.000401 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 0.000596 sec
                     wait id: 5487            p1: 'driver id'=0x28444553
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000008 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000002 sec
                     wait id: 5486            p1: 'driver id'=0x28444553
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000117 sec
              3.       event: 'db file sequential read'
                 time waited: 0.007607 sec
                     wait id: 5485            p1: 'file#'=0xa2
                                              p2: 'block#'=0x112360
                                              p3: 'blocks'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (yydb.yydb1)
                   os id: 16319376
              process id: 1567, oracle@scyydb1
              session id: 8863
        session serial #: 53455
    }
    which is waiting for 'db file scattered read' with wait info:
    {
                      p1: 'file#'=0xc4
                      p2: 'block#'=0x26d37c
                      p3: 'blocks'=0x10
            time in wait: 0.000000 sec
           timeout after: never
                 wait id: 6304859
                blocking: 339 sessions
             current sql: update so1.INS__ins t set t.expire_date=to_date('20121231 23:59:59','yyyymmdd hh24:mi:ss') where (t.offer_inst_id,t.use
r_id) in
(select a.offer_inst_id,a.user_id from wlanCPss2 a )
             short stack: ksedsts()+644<-ksdxfstk()+44<-ksdxcb()+3384<-sspuser()+116<-47dc<-poll()+12<-sntpoltsts()+884<-snttmoredata()+52<-nsmore2recv()+246
0<-nioqts()+6588<-ksuttctest()+128<-updrow()+16292<-qerupFetch()+856<-updaul()+1100<-updThreePhaseExe()+336<-updexe()+344<-opiexe()+14476<-kpoal8()+4616<-opi
odr()+720<-ttcpip()+1028<-opitsk()+1508<-opiino()+940<-opiodr()+720<-opidrv()+1132<-sou2o()+136<-opimai_real()+608<-ssthrdmain()+268<-main()+204<-__start()+1
12
6，通过sid定位用户信息，确定为有人用plsql在运行上述sql

SESSION_ID SAMPLE_TIME                                                                 PROGRAM                                          MACHINE
---------- --------------------------------------------------------------------------- ------------------------------------------------ ---------------

      8863 28-SEP-12 03.49.13.605 PM                                                   plsqldev.exe                                     XX-VM-RDS1



7，杀掉了问题sid，系统恢复。问题出在了开发人员在业务高峰期批量更新数据，高峰时间段批量更新数据是大忌！！！！
分享到：
| 直接用网线连接两台电脑，实现双网卡共享上 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    BLOG_【故障处理】Oracle_lhr_队列等待之TX - row lock contention

    ### 故障处理：Oracle_lhr_队列等待之TX - row lock contention #### 一、概述 在Oracle数据库管理中，“enq:TX-rowlockcontention”是一种常见的队列等待事件，通常与行级别的锁定冲突有关。这种冲突可能会导致...

    orcale死锁查杀PB版

    - 使用`v$session_wait`视图查找等待类型的`ENQ: TX - row lock contention`或`ENQ: TX - deadlock`。 - 查询`v$deadlock`和`v$deadlock_monitor`视图获取死锁信息。 - 执行`DBMS_LOCK.MONITOR`过程来监控死锁...

    Oracle10g数据库最佳新特性：等待界面 （1）

    例如，对于"enq:TX - row lock contention"这类等待事件，可能需要考虑事务的并发控制策略或锁的粒度。 总之，Oracle 10g的等待界面改进极大地增强了DBAs在性能监控和问题诊断方面的能力。结合ADDM的自动化分析和...

    ORACLE_查看锁和解锁

    event = 'enq: TX - row lock contention' order by blocking_session; ``` 该语句可以查看当前阻塞的Session，包括Session ID、用户名、程序、状态、SQL ID、阻塞的实例和阻塞的Session等信息。 Oracle提供了...

    Oracle管理员手册3.

    SELECT * FROM v$session_wait WHERE event = 'enq: TX - row lock contention'; ``` - **输出结果解读**: - **p2** 和 **p3** 列包含了 **id1** 和 **id2** 的值，这两个值可以从 **V$lock** 视图中获得。 - *...

    oracledb-exporter.0.2.9-ora18.5.linux-amd64.tar.gz

    - 锁等待情况（如ENQ: TX - ROW LOCK contention） Prometheus会定期抓取oracledb-exporter提供的这些指标，并根据预设的规则进行报警和可视化展示。你可以利用Prometheus的Alertmanager设置告警规则，当特定指标...

    中国银行ORACLE优化培训方案

    - **enq:TX-rowlock contention**：行级锁争用，表示两个或多个会话尝试在同一行上执行不兼容的操作。 - **latch free**：闩锁等待，表示进程正在等待一个当前被其他进程占用的闩锁。 #### 三、使用与查看数据库...

    EBS服务器升级PPT课件.pptx

    数据库性能分析显示，主要的性能瓶颈在于用户I/O，尤其是db file sequential read和db file scattered read事件，以及enq: TX - row lock contention，这些都可能导致新Session无法启动或需要更多内存来处理更多的...

    Oracle10g数据库最佳新特性：等待界面 （2）

    以SID为269的会话为例，如果它正等待enq:TX — row lock contention事件，意味着它在等待另一个会话释放锁。在Oracle 10g之前，找出阻塞会话可能需要编写资源密集型查询，但在10g中，只需简单查询v$session就能找到...

    oraclev$sessionv$session_wait用途详解

    1. **等待事件类型**：`EVENT`字段列出了具体的等待事件名称，如“db file sequential read”表示顺序读取数据库文件，“enq: TX - row lock contention”表示行级锁竞争。 2. **等待时长**：`TIME_WAITED`字段提供...

    oracle DBA 例行维护内容

    WHERE event LIKE 'enq: TX - row lock contention' OR event LIKE '%wait%'; -- 杀死会话 ALTER SYSTEM KILL SESSION 'sid,serial#'; ``` ##### 2.4 检查系统空间占用 - **工具选择**：使用操作系统自带的...

    People soft on exadata

    - **行锁争用（enq:TX – row lock contention）**：在并发事务处理中，行锁争用是常见的问题之一。当多个事务尝试同时修改同一数据行时，就可能发生此类等待事件。 - **用户I/O**： - 单块读取（single block read...

Magicbox
Global site tag (gtag.js) - Google Analytics