奇异的enq: TX - row lock contention

 在客户生产系统中，不定时发生enq: TX - row lock contention，其执行语句为
    Select
         MER_ID, TRADE_DATE, BALANCE, PAY_FLAG
    from XXXXX
    where MER_ID = :1
    for update
  定为源头even为'SQL*Net message from client' ，通过v$active_session_history中并没有找出对应的语句。从diag trace文件中，获取如下信息：
   Verified Hangs in the Sytem
    Hang ID | HangType|status|.........|Hang Resolution Action
       2           Hang      UNRSLVBL      Unresolveable:User
  Hang Ignored Reason: This hang is probably the result of an application problem.
  External invention is required. The hang will be ignored
 
   inst#  SessID  Ser# OSPID   PrcNum  Event
     1       54      9   20330   FG  enq:TX - row lock contention
     1       889   81  19318   FG  SQL*Net Message from client
     从数据库日志中，能分析出来主要是应用层问题。但无法定位为什么造成该问题，每隔5到10几天都会突然出现，造成业务大量的堵塞。需要进一步具体分析进程19318。
    SQL>oradebug setospid  19318
    SQL>oradebug dump processstate 10
    SQL>oradebug tracefile_name

从trace文件中，我们能看到如下信息：
SO: 0x1881cba690, type: 4, owner: 0x18915629c8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
    ...............................
   client details:
      O/S info: user: xyd, term: unknown, ospid: 1234
      machine: xydzhzf2 program: JDBC Thin Client
      application name: JDBC Thin Client, hash value=2546894660
    Current Wait Stack:
     0: waiting for 'SQL*Net message from client'
        driver id=0x28444553, #bytes=0x1, =0x0
        wait_id=11 seq_num=12 snap_id=1
        wait times: snap=13 min 53 sec, exc=13 min 53 sec, total=13 min 53 sec
        wait times: max=infinite, heur=13 min 53 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 92 sessions blocked by this session.
SO: 0x187f22b7f0, type: 56, owner: 0x1881cba690, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   ...........................
(enqueue) TX-00180019-00028545    DID: 0001-00A4-0000003F
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x7
      mode: X, lock_flag: 0x0, lock: 0x187f22b868, res: 0x18a1fd6548
SO: 0x18b6a4c2b8, type: 54, owner: 0x187f22b7f0, flag: INIT/-/-/0x00 if: 0x1 c: 0x1
(enqueue) TM-00003B59-00000000    DID: 0001-00A4-0000003F
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x7
        mode: SX, lock_flag: 0x0, lock: 0x18b6a4c318, res: 0x18a2070948
..............................................
SO: 0x190e3ad320, type: 78, owner: 0x1881cba690, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
..............................
ObjectName:  Name=select
     MER_ID, TRADE_DATE, BALANCE, PAY_FLAG
   from xxxxxx
    where MER_ID = :1
    for update
ChildTable:  size='32'
            Child:  id='0' Table=0x17dff1b460 Reference=0x17dff1aea8 Handle=0x194f4667b8
            Child:  id='1' Table=0x17dff1b460 Reference=0x17dff1b1f0 Handle=0x194f1f0bd0
            .............................. 
            ..............................
            Child:  id='16' Table=0x17dff1b308 Reference=0x17de7f9e88 Handle=0x194ef6d7b8
 NamespaceDump:  
          Parent Cursor:  sql_id=6hdhk1kdar683 parent=0x17dff1a650 maxchild=17 plk=y ppn=n
CursorDiagnosticsNodes:  
ChildNode:  ChildNumber=14 ID=34 reason= Rolling Invalidate Window Exceeded(2) size=0x0 details=already_processed
ChildNode:  ChildNumber=15 ID=34 reason= Rolling Invalidate Window Exceeded(2) size=0x0details=already_processed  
ChildNode:  ChildNumber=14 ID=34 reason= Rolling Invalidate Window Exceeded(3) size=2x4
                              invalidation_window=1525272419 ksugctm=1525274402
     
从显示信息中可以推测可能是JDBC Driver使用11.2.0.1触发了bug 9445675，检查应用jdbc版本果然为11.2.0.1。

解决办法是升级jdbc driver为11.2.0.2版本以上。进到数据库$ORACLE_HOME/jdbc/lib下载ojdbc6.jar替换应用jdbc driver。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/354732/viewspace-2153838/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-2153838/