DEADLOCK DETECTED ( ORA-00060 )

解决DEADLOCK DETECTED ( ORA-00060 )错误。如下是今天分析的过程有点乱，但没有完成（期待后续这周完成吧），再次记录一下。

今天是2014-03-26，收到朋友求助，解决DEADLOCK DETECTED ( ORA-00060 )错误。如下是今天分析的过程有点乱，但没有完成（期待后续这周完成吧），再次记录一下。
 
Deadlock graph:
                        ---------Blocker(s)--------  ---------Waiter(s)---------
 Resource Name          process session holds waits  process session holds waits
 TX-00060008-0006c82d      520    1313    X            527    691          X
 TX-0056002b-00033647      527    691    X            520    1313          X
 session 1313: DID 0001-0208-00024147 session 691: DID 0001-020F-00020F2D
 session 691: DID 0001-020F-00020F2D session 1313: DID 0001-0208-00024147
 
可以知道会话1313持有x锁堵塞了691使其等待获得x锁，其持有的资源事务信息为00060008-0006c82d
 会话691持有x锁堵塞了1313使其等待获得x锁，其持有的资源事务信息为：0056002b-00033647
 对于tx锁代表transaction enqueue。00060008和0006c82d 分别对应v$lock id1和id2
 那么v$lock的id1和id2对不同类型有不同含义，那么对TX的内容如下：
 20:23:46 SYS # htdb >select type,name,id1_tag,id2_tag from v$lock_type where type='TX'
 20:23:57  2  ;
 
TYPE                          NAME                          ID1_TAG                        ID2_TAG
 ------------------------------ ------------------------------ ------------------------------ ----------------------------------------------------------------
 TX                            Transaction                    usn 
Elapsed: 00:00:00.00
 20:23:59 SYS # htdb >
 那么可以知道，tx锁id1代表usn和slot，id2正是v$transaction中的xidsqn号。那么如何转换id1为usn和slot呢？
 其实在v$lock中显示的是decimal rbs @slot，00060008前4为usn回滚段号，后4为slot，不足则补零，换算结果为：第6个回滚段的8槽位，0006c82d代表的是xidsqn转换后为：444461
 同样TX-0056002b-00033647换算结果为：86回滚段的43槽位，sequence为：210503
 另外DID代表什么呢？前4位代表实例号，在4位代表Oracle 进程号pid，剩余为类似于v$session.serial#的一个值。那么 0001-0208-00024147代表实例号1，oracle pid:520 串行号为：147783
 DID 0001-020F-00020F2D ，实例1，pid：527，134957
 这是从上面获得的信息。继续看
 获得了对象信息：
 
Rows waited on:
 Session 691: obj - rowid = 0002B340 - AAArNAAAmAACOiAAAP
  (dictionary objn - 176960, file - 38, block - 583808, slot - 15)
 Session 1313: obj - rowid = 0002B259 - AAArJZABBAAFWhVAAC
  (dictionary objn - 176729, file - 65, block - 1402965, slot - 2)
 Information on the OTHER waiting sessions:
 Session 691:
  sid: 691 ser: 37732 audsid: 41410659 user: 191/CUECM
    flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8)
  pid: 527 O/S info: user: oracle, term: UNKNOWN, ospid: 913852
    image: oracle@ECMora01
  O/S info: user: , term: , ospid: 1234, machine: ECM_APP3_02
            program:
获得了对应堵塞sql信息
  Current SQL Statement:
  UPDATE    T_CONTRACT_ARRAIR    SET    ContractStatus=:1  WHERE    ID = :2 
End of information on OTHER waiting sessions.
 Current SQL statement for this session:
 update T_CONTRACT_CONTENT set  classify=:1,classifyName=:2,  TemplateID=:3, NAME=:4,bidType=:5,  CONJUNCTION=:6,FrameWorkMode=:7,  DEGREE=:8,  PRIORITY=:9,PRECONDITION=:10, Cnt=:11,  Comments=:12, Mobile=:13,UpdateTime=sysdate,  professionalType=:14, REQUIRENAME=:15,  phone=:16,fixedAmount=:17,SIGNDEPTNAME=:18,  BIDQUALIFITIONEXP = :19, ISREADY = :20,  signer=:21,overView=:22, SelectModel =  :23,PaymentType = :24, IsBudget =  :25,IsBeforehand = :26, PerformProperty  =:27,PerformTimeLimit=:28,  AccountRate = :29, DRAFTOUTTAB = :30,  PERFORMTAB = :31, degree_ext = :32,  performaccount=:33,checkVendor=:34,hasPrebill= :35          ,globalSn=null              ,explain=null      where id=:36
 
这是从trace文件开始获得的信息。
 另外查看一下在数据文件之后就是1313这个会话信息：
    SO: 700000cd047d060, type: 4, owner: 700000cd0334588, flag: INIT/-/-/0x00
    (session) sid: 1313 trans: 700000cb52fa9d0, creator: 700000cd0334588, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0208-00024147, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 6, prv: 0, sql: 700000cd6e943b8, psql: 700000cd67cfe28, user: 191/CUECM
    service name: SYS$USERS
    O/S info: user: , term: , ospid: 1234, machine: ECM_APP3_02
              program:
    last wait for 'enq: TX - row lock contention' wait_time=2.929926 sec, seconds since wait started=3
                name|mode=54580006, usn                blocking sess=0x700000ccf400390 seq=49119
 可知获得了很多信息啊，一看就懂了，不在说明。其中 blocking sess=0x700000ccf400390 seq=49119是关键信息，另外还有enq: TX - row lock contention，
 在谈enq: TX - row lock contention：
 简单介绍三种情况：
 Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
 The solution is to have the first session holding the lock perform a COMMIT or ROLLBACK.
 
Waits for TX in mode 6: occurs when a session is waiting for a row level lock that is held by another session. This occurs when one user is updating or deleting a row, which another session wants to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
 
The solution is to have the first session holding the lock perform a COMMIT or ROLLBACK.
 Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
 那具体属于那种呢？还行进一步分析。（如果对问题处理有经验了，目前已经知道了问题所在了。呵呵）
 继续看：
 在采样中如下：
    The history is displayed in reverse chronological order.
 
    sample interval: 1 sec, max history 120 sec
    ---------------------------------------------------
      [3 samples,                                          10:00:17 - 10:00:19]
        waited for 'enq: TX - row lock contention', seq_num: 49119
          p1: 'name|mode'=0x54580006
          p2: 'usn          p3: 'sequence'=0x33647
          time_waited: >= 2 sec (still in wait)
      [4 samples,                                          10:00:13 - 10:00:16]
        waited for 'enq: TX - row lock contention', seq_num: 49116
          p1: 'name|mode'=0x54580006
          p2: 'usn          p3: 'sequence'=0x344b1
          time_waited: 0.778215 sec (sample interval: 3 sec)
 可以看到该会话依然在等待
          p2: 'usn          p3: 'sequence'=0x33647
          time_waited: >= 2 sec (still in wait)
 至此通过slot可以看到对应了开始的TX-0056002b-00033647 这是问题关键。那是什么导致的呢？
 继续看：
 下面就是library cache 的内容了。其pin有三种模式分别是N，s，X，那么我们关注后面两个：
 
LIBRARY OBJECT LOCK: lock=700000c98ff6210 handle=700000cd6e943b8 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=700000c98ff6290[700000ae75279e8,700000c430397a0] htb=700000ae75279e8 ssga=700000ae7526aa0
      user=700000cd047d060 session=700000cd047d060 count=1 flags=[0000] savepoint=0x5330e331
      LIBRARY OBJECT HANDLE: handle=700000cd6e943b8 mtx=700000cd6e944e8(4) lct=379499 pct=1 cdp=4
      name=
 update T_CONTRACT_CONTENT set  classify=:1,classifyName=:2,  TemplateID=:3, NAME=:4,bidType=:5,  CONJUNCTION=:6,FrameWorkMode=:7,  DEGREE=:8,  PRIORITY=:9,PRECONDITION=:10, Cnt=:11,  Comments=:12, Mobile=:13,UpdateTime=sysdate,  professionalType=:14, REQUIRENAME=:15,  phone=:16,fixedAmount=:17,SIGNDEPTNAME=:18,  BIDQUALIFITIONEXP = :19, ISREADY = :20,  signer=:21,overView=:22, SelectModel =  :23,PaymentType = :24, IsBudget =  :25,IsBeforehand = :26, PerformProperty  =:27,PerformTimeLimit=:28,
      hash=39b51e91e5a0ae1f78856df46eab74e3 timestamp=02-13-2014 22:16:32
      namespace=CRSR flags=RON/KGHP/TIM/PN0/KST/DBN/MTX/[100100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=1 hpc=8cfe hlc=8cfe
      lwt=700000cd6e94460[700000cd6e94460,700000cd6e94460] ltm=700000cd6e94470[700000cd6e94470,700000cd6e94470]
      pwt=700000cd6e94428[700000cd6e94428,700000cd6e94428] ptm=700000cd6e94438[700000cd6e94438,700000cd6e94438]
      ref=700000cd6e94490[700000cd6e94490,700000cd6e94490] lnd=700000cd6e944a8[700000cd6e944a8,700000cd6e944a8]
        LIBRARY OBJECT: object=700000ca9df62b0
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=32
        child#    table reference  handle
        ------ -------- --------- --------
              0 700000ca9df6160 700000ca9df5cc0 700000cd6e94188
              1 700000ca9df6160 700000ca9dc1f80 700000cd6e7d8b0
              2 700000ca9df6160 700000ca9c46200 700000cd6e02da8
              3 700000ca9df6160 700000c89cda488 700000cd6a32fa0
              4 700000ca9df6160 700000c89cb50e8 700000cd6a2ab48
              5 700000ca9df6160 700000c89c3eec8 700000cd6a10bd8
              6 700000ca9df6160 700000c89bf29d0 700000cd6a16428
              7 700000ca9df6160 700000c89aec600 700000cd69afc98
              8 700000ca9df6160 700000c89a02750 700000cd696f638
              9 700000ca9df6160 700000c89939788 700000cd6933770
            10 700000ca9df6160 700000c8925a618 700000cd673f7c0
            11 700000ca9df6160 700000c4d74c530 700000cd644cb08
            12 700000ca9df6160 700000c4d19bf68 700000cd62b9110
            13 700000ca9df6160 700000c0eb3c030 700000cd612c1d8
            14 700000ca9df6160 700000bcc1cf3b0 700000bf39564f0
            15 700000ca9df6160 700000b14df18e0 700000b18f17e30
            16 700000b14df19f8 700000c4d475738 700000cd6ca9110
            17 700000b14df19f8 700000c893e41a0 700000bf32629b8
            18 700000b14df19f8 700000c8941e528 700000cd617bcc8
            19 700000b14df19f8 700000c4daa6ee0 700000bf37d38a0
            20 700000b14df19f8 700000c4d731b58 700000b18e5f288
            21 700000b14df19f8 700000c4dfff140 700000b18640318
            22 700000b14df19f8 700000b52bba898 700000b18314e78
        DATA BLOCKS:
        data#    heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 700000cd6e942f8 700000ca9df63c8 I/P/A/-/-    0 NONE  00
      ----------------------------------------
      SO: 700000c98c678b0, type: 53, owner: 700000cd047d060, flag: INIT/-/-/0x00
 通过该段内容我没有看出什么问题来，，但是我获得了很多有用的信息，如当前堵塞会话的sql就在其中，且我获得了object handel地址： handle=700000cd6e943b8，该用户在library cache中的信息
 user=700000cd047d060 session=700000cd047d060 count=1 flags=[0000] savepoint=0x5330e331
 LIBRARY OBJECT HANDLE: handle=700000cd6e943b8 mtx=700000cd6e944e8(4) lct=379499 pct=1 cdp=4
 htl=700000c98ff6290[700000ae75279e8,700000c430397a0] htb=700000ae75279e8 ssga=700000ae7526aa0
      user=700000cd047d060 session=700000cd047d060 count=1 flags=[0000] savepoint=0x5330e331
 LIBRARY OBJECT: object=700000ca9df62b0
 继续看啊，分析如下信息：
      LIBRARY OBJECT LOCK: lock=700000c981e53b0 handle=700000cd7ebcd08 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=700000c981e5430[700000ae75273e8,700000c9809c3a8] htb=700000ae75273e8 ssga=700000ae7526aa0
      user=700000cd047d060 session=700000cd047d060 count=1 flags=[0000] savepoint=0x5330e331
      LIBRARY OBJECT HANDLE: handle=700000cd7ebcd08 mtx=700000cd7ebce38(6) lct=1172176 pct=1 cdp=6
      name=INSERT INTO T_CONTRACT_CHANGED SELECT ID,SYSDATE FROM T_CONTRACT_CONTENT T WHERE T.AFFAIRID=:B1
      hash=ea90bb8989793260486f86d42172bf83 timestamp=02-13-2014 22:16:32
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=35 hpc=fc06 hlc=fc06
      lwt=700000cd7ebcdb0[700000cd7ebcdb0,700000cd7ebcdb0] ltm=700000cd7ebcdc0[700000cd7ebcdc0,700000cd7ebcdc0]
      pwt=700000cd7ebcd78[700000cd7ebcd78,700000cd7ebcd78] ptm=700000cd7ebcd88[700000cd7ebcd88,700000cd7ebcd88]
      ref=700000cd7ebcde0[700000cd7ebcde0,700000cd7ebcde0] lnd=700000cd7ebcdf8[700000cd7e039c0,700000cd7ec5318]
        LIBRARY OBJECT: object=700000cace600d8
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference  handle
        ------ -------- --------- --------
              0 700000cace5ff88 700000cace5fae8 700000cd7ebc460
              1 700000cace5ff88 700000cacacd478 700000cd7d6b9d8
              2 700000cace5ff88 700000cac7e9398 700000cd7c840e8
              3 700000cace5ff88 700000b9a865140 700000bceb1aca8
              4 700000cace5ff88 700000b9ad05c40 700000bcea501c8
              5 700000cace5ff88 700000be8c01768 700000cd77f72d8
              6 700000cace5ff88 700000b538eadb8 700000cd7270e60
              7 700000cace5ff88 700000cac149578 700000cd7d76320
              8 700000cace5ff88 700000cacbcdba8 700000cd7c79560
              9 700000cace5ff88 700000cac7c4be0 700000c494053b0
        DATA BLOCKS:
        data#    heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 700000cd7ed7b10 700000cace601f0 I/P/A/-/-    0 NONE  00
      ----------------------------------------
      SO: 700000cb52fa9d0, type: 40, owner: 700000cd047d060, flag: INIT/-/-/0x00
      (trans) flg = 0x1e03, flg2 = 0xc0000, prx = 0x0, ros = 2147483647 bsn = 0x1ae8cb bndsn = 0x1ae8ce spn = 0x1ae8fe
      efd = 4
      file:kta.c lineno:1443
      DID: 0001-0208-00024147
      parent xid: 0x0000.000.00000000
      env: (scn: 0x0c80.e5b63a1f  xid: 0x0006.008.0006c82d  uba: 0x00920c63.7d1b.18  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x0c80.e5b63a21 0sch: scn: 0x0000.00000000)
      cev: (spc = 5162  arsp = 700000cb83e5168  ubk tsn: 1 rdba: 0x00920c63  useg tsn: 1 rdba: 0x00800059
            hwm uba: 0x00920c63.7d1b.18  col uba: 0x00000000.0000.00
            num bl: 1 bk list: 0x700000cb410d118)
            cr opc: 0x0 spc: 5162 uba: 0x00920c63.7d1b.18
      (enqueue) TX-00060008-0006C82D DID: 0001-0208-00024147
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      mode: X, lock_flag: 0x0, lock: 0x700000cb52faa08, res: 0x700000cd05ee608
      own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cd05ee618
        xga: 0x0, heap: UGA
      Trans IMU st: 0 Pool index 65535, Redo pool 0x700000cb52fb0b8, Undo pool 0x700000cb52fb1a0
      Redo pool range [0x1103ca6d0 0x1103ca6d0 0x1103cced0]
      Undo pool range [0x1103c7ed0 0x1103c7ed0 0x1103ca6d0]
        ----------------------------------------
        SO: 700000cb50d1740, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=176729 flg=11 chi=0
                  his[0]: mod=3 spn=1763581
        (enqueue) TM-0002B259-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50d1768, res: 0x700000cd0625c40
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cb50cfc30
        ----------------------------------------
        SO: 700000cb50d1620, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=176711 flg=11 chi=0
                  his[0]: mod=3 spn=1763579
        (enqueue) TM-0002B247-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50d1648, res: 0x700000cd05ecd50
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cb50cf438
        ----------------------------------------
        SO: 700000cb50d14e8, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=150112 flg=11 chi=0
                  his[0]: mod=3 spn=1763564
        (enqueue) TM-00024A60-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50d1510, res: 0x700000cd0614368
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cb50d0f80
        ----------------------------------------
        SO: 700000cb50d1188, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=107548 flg=11 chi=0
                  his[0]: mod=3 spn=1763548
        (enqueue) TM-0001A41C-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50d11b0, res: 0x700000c7a175360
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cb50cf8b8
        ----------------------------------------
        SO: 700000cb410d118, type: 39, owner: 700000cb52fa9d0, flag: -/-/-/0x00
        (List of Blocks) next index = 14
        index  itli  buffer hint  rdba      savepoint
        -----------------------------------------------------------
            0      5  0x7000000cdfbe308    0x988e880    0x1ae8ce
            1    35  0x70000055efa6f28    0x31fb35b    0x1ae8d0
            2    129  0x70000039dff1b88    0x1007e251  0x1ae8d1
            3    75  0x700000150fa3188    0x989c53b    0x1ae8d3
            4    68  0x700000542fdefe8    0xf4436cd    0x1ae8d4
            5    82  0x700000312f95158    0x94dbae8    0x1ae8d6
            6    88  0x700000ad5fc9be8    0x105c009a  0x1ae8d7
            7    107  0x7000001e6fc99c8    0x3886858    0x1ae8dd
            8    87  0x700000341fa9238    0x7c0d3a7    0x1ae8e1
            9    20  0x700000a73f89ba8    0xf1d2a6e    0x1ae8e4
            10    71  0x700000990fc65b8    0x75bce76    0x1ae8e5
            11    127  0x700000063fda588    0x3fef45c    0x1ae8ed
            12    38  0x7000008d9fbd758    0xc85bca9    0x1ae8f1
            13    70  0x700000272fa4068    0x198579c    0x1ae8f2
        ----------------------------------------
        SO: 700000cb50cf2e0, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=176960 flg=11 chi=0
                  his[0]: mod=3 spn=1763533
        (enqueue) TM-0002B340-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50cf308, res: 0x700000cd05f2fa8
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cd05f2fb8
        ----------------------------------------
        SO: 700000cb50cf1c0, type: 36, owner: 700000cb52fa9d0, flag: INIT/-/-/0x00
        DML LOCK: tab=176942 flg=11 chi=0
                  his[0]: mod=3 spn=1763531
        (enqueue) TM-0002B32E-00000000 DID: 0001-0208-00024147
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
        mode: SX, lock_flag: 0x0, lock: 0x700000cb50cf1e8, res: 0x700000cd064b890
        own: 0x700000cd047d060, sess: 0x700000cd047d060, proc: 0x700000cd0334588, prv: 0x700000cd064b8a0
      ----------------------------------------
      SO: 700000c980eb6b0, type: 53, owner: 700000cd047d060, flag: INIT/-/-/0x00