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