DEADLOCKDETECTED(ORA-00060)

今天是2014-03-25，收到朋友求助，解决DEADLOCK DETECTED ( ORA-00060 )错误。如下是今天分析的过程有点乱，但没有完成（期待后续这周完成吧），再次记录一下。 Deadlock graph: ———Blocker(s)——– ———Waiter(s)——— Resource Name pro

今天是2014-03-25，收到朋友求助，解决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<<16 | slot sequence

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 State本文来源gao.dai.ma.com搞@代*码#网ment:
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

喜欢 (0)赏

[搞代码]

分享 (0)