Oracle死锁分析过程详解精要.doc

Oracle 死锁分析过程详解 Oracle 死锁分析 关于死锁一般3种处理方式 1、事前预测 2、资源分级 3、事后检测释放 我知道的ORACLE MYSQL都是采用第三种在行锁级别上的话。 这里分析一个ORACLE死锁，首先一个死锁肯定会生成一个TRACE文件，这里会记录很多信息如： Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-0058000f-0000b473 649 1204 X 651 1252 X TX-0019001c-0004e0b0 651 1252 X 649 1204 X 这里给出了进程和会话id Rows waited on: Session 1204: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAI (dictionary objn - 252226, file - 4, block - 919181, slot - 8) Session 1252: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAa (dictionary objn - 252226, file - 4, block - 919181, slot - 26) 这里给出导致死锁的行 同时给出了最后触发死锁会话 1252的语句 ----- Information for the OTHER waiting sessions ----- Session 1252: sid: 1252 ser: 35883 audsid: 7170593 user: 235/FEECORESV flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 651 O/S info: user: oracle, term: UNKNOWN, ospid: 13035 image: oracle@oratest11 client details: O/S info: user: sky, term: unknown, ospid: 1234 machine: autobots program: JDBC Thin Client application name: JDBC Thin Client, hash value=2546894660 current SQL: UPDATE ******* ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=3vh5sc7pgtrjy) ----- UPDATE ******* 那么到这里我们大概能够分析出 A:1204拿到AAA9lCAAEAADgaNAAa 行锁 B:1252 拿到 AAA9lCAAEAADgaNAAI 行锁 C:1204需要AAA9lCAAEAADgaNAAI 则等待 D:1252 需要 AAA9lCAAEAADgaNAAa 则触发死锁1204回滚 那么随后trace给出1204 C这一步等待时间和事物信息 SO: 0xee1fcd10, type: 4, owner: 0xf031e750, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0xf031e750, name=session, file=ksu.h LINE:12624, pg=0 (session) sid: 1204 ser: 2443 trans: 0xe9221180, creator: 0xf031e750 flags: (0x100045) USR/- flags_idl: (0x1 ) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC DID: , short-term DID: txn branch: (nil) oct: 6, prv: 0, sql: 0xf25d2278, psql: 0xc4346788, user: 235/