奇異的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/，如需轉載，請註明出處，否則將追究法律責任。