《oracle 死锁分析过程详解》由会员分享，可在线阅读，更多相关《oracle 死锁分析过程详解（8页珍藏版）》请在金锄头文库上搜索。
1、 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 waitsTX-0058000f-0000b473 649 1204 X 651 1252 XTX-0
2、019001c-0004e0b0 651 1252 X 649 1204 X这里给出了进程和会话 idRows 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
3、- 26)这里给出导致死锁的行同时给出了最后触发死锁会话 1252 的语句- Information for the OTHER waiting sessions -Session 1252:sid: 1252 ser: 35883 audsid: 7170593 user: 235/FEECORESVflags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-flags2: (0x40009) -/-/INCpid: 651 O/S info: user: oracle, term: UNKNOWN, ospid: 13035image: or
4、acleoratest11client details:O/S info: user: sky, term: unknown, ospid: 1234machine: autobots program: JDBC Thin Clientapplication name: JDBC Thin Client, hash value=2546894660current SQL:UPDATE *- End of information for the OTHER waiting sessions -Information for THIS session:- Current SQL Statement
5、 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 i
6、f: 0x3 c: 0x3proc=0xf031e750, name=session, file=ksu.h LINE:12624, pg=0(session) sid: 1204 ser: 2443 trans: 0xe9221180, creator: 0xf031e750flags: (0x100045) USR/- flags_idl: (0x1 ) BSY/-/-/-/-/-flags2: (0x40009) -/-/INCDID: , short-term DID:txn branch: (nil)oct: 6, prv: 0, sql: 0xf25d2278, psql: 0xc
7、4346788, user: 235/FEECORESVksuxds FALSE at location: 0service name: SYS$USERSCurrent Wait Stack:0: waiting for enq: TX - row lock contentionname|mode=0x54580006, usn select * from v$version;BANNER-Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biPL/SQL Release 10.2.0.4.0 - Production
8、CORE 10.2.0.4.0 ProductionTNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - ProductioNLSRTL Version 10.2.0.4.0 - Production挂载参数(mount 命令查看)10.240.10.1 /top/data4/nfs /back1 nfs3 Aug 29 13:40 cio,rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600尝试创建 redoSQL alter database
9、 add logfile group 13 (/back/newxff/redo13.log) size 2048m; alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m*ERROR at line 1:ORA-00301: error in adding log file /back1/newxff/redo13.log - file cannot becreatedORA-27054: NFS file system where the file is created or resides is
10、 not mountedwith correct optionsAdditional information: 6根据 mos 文档ORA-27054 ERRORS WHEN RUNNING RMAN WITH NFS (文档 ID 387700.1)SQL Alter system set events 10298 trace name context forever,level 32;System altered.Mon Sep 5 10:10:18 2016Thread 1 advanced to log sequence 109 (LGWR switch)Current log# 1
11、seq# 109 mem# 0: +DATA/xff/onlinelog/group_1.257.921671023Mon Sep 5 10:12:19 2016OS Pid: 160710 executed alter system set events 10298 trace name context forever,level 32创建 redo 成功SQL alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m;System altered.Mon Sep 5 10:18:13 2016alte
12、r database add logfile group 13 (/back1/newxff/redo13.log) size 2048mMon Sep 5 10:18:43 2016Completed: alter database add logfile group 13 (/back1/newxff/redo13.log) size 2048m数据库 crashMon Sep 5 10:19:06 2016Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.trc:ORA-00313: open failed for m
13、embers of log group 13 of thread 1ORA-00312: online log 13 thread 1: /back1/newxff/redo13.logORA-27054: NFS file system where the file is created or resides is not mounted with correct optionsAdditional information: 6Mon Sep 5 10:19:06 2016Errors in file /opt/oracle/admin/xff/bdump/xff1_lgwr_246566.
14、trc:ORA-00313: open failed for members of log group 13 of thread 1ORA-00312: online log 13 thread 1: /back1/newxff/redo13.logORA-27054: NFS file system where the file is created or resides is not mounted with correct optionsAdditional information: 6Mon Sep 5 10:19:06 2016LGWR: terminating instance d
15、ue to error 313Mon Sep 5 10:19:06 2016System state dump is made for local instanceSystem State dumped to trace file /opt/oracle/admin/xff/bdump/xff1_diag_299654.trc通过报错很明显可以看出来数据库挂掉的原因和当时不能创建 redo 的原因一样，都是由于ORA-27054 导致数据库挂了，但是为什么创建 redo 成功,但是使用 redo 失败呢？这里需要注意使用的命令是 events,而这个命令是对当前会话和后续新建的会话生效，也就是说他不会对数据库已经存在的后台进程生效，那也就可以理解了，我创建 redo 是在执行events 的当前命令行窗口处理的，因此可以创建成功；但是 lgwr 进程是数据库一启动就存在的进程，现在设置的 events 对他没有影响，因此当 lgwr 去使用 redo的时候无法正常使用因此就导致数据库 crash 掉。如果希望 event 对已经存在的进程生效，可以考虑使
