---oracle version
SQL> select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production---session 241
SQL> select sid from v$mystat where rownum=1; SID---------- 241SQL> select pid,spid,addr from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); PID SPID ADDR---------- ------------------------------------------------ ---------------- 184 15936 00000000DD6589C0 SQL> create table t_lock(a int,b int);SQL> insert into t_lock select level,level from dual connect by level<=2;SQL> commit;SQL> select * from t_lock; A B---------- ---------- 1 1 2 2SQL> update t_lock set a=22 where a=2;1 row updated.----session 261
SQL> select sid from v$mystat where rownum=1; SID---------- 261SQL> update t_lock set a=11 where a=1;1 row updated.SQL> select pid,spid,addr from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1)); PID SPID ADDR---------- ------------------------------------------------ ---------------- 185 15937 00000000DD659A00---session 241及261分别执行如下
SQL> update t_lock set a=11 where a=1;update t_lock set a=11 where a=1*ERROR at line 1:ORA-00060: deadlock detected while waiting for resource---session 261SQL> update t_lock set a=22 where a=2; 查看告警日志，可见出现死锁会记录在告警日志[root@seconary ~]# su - oracle[oracle@seconary ~]$ cd /oracle/diag/rdbms/guowang/guowang/trace/[oracle@seconary trace]$ tail -f alert_guowang.log Sun Nov 08 19:06:04 2015ORA-00060: Deadlock detected. More info in file /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_15936.trc.我们分析下死锁的TRACE FILE,获取有价值的内容，直接在源文件直行分析，便于交插参考
*** 2015-11-08 19:06:01.260DEADLOCK DETECTED ( ORA-00060 )[Transaction Deadlock]The following deadlock is not an ORACLE error. It is a --标明死锁是由应用本面设计不合理或者SQL原因引发，而本ORACLE本身的原因导致deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following --并且下述信息直接给出产生死锁的相关信息及原因information may aid in determining the deadlock:Deadlock graph: ----可见分为阻塞者以及等待者竞争的资源 ---------Blocker(s)-------- ---------Waiter(s)---------Resource Name process session holds waits process session holds waitsTX-00b40017-00002e45 184 241 X 185 261 XTX-00c2001d-00002c68 185 261 X 184 241 X 由上可见，有2条记录，第一条记录表明261会话请求排它X锁，却发现当被241会话以X排它模式持有，所以只能等待第二条记录同理，但刚好相反小结下：
上述包括： 竞争的资源 ：它有3部分构成，第一部分很简单，表明锁的类型，第2和第3部分，请继续看我下面的分析 阻塞者信息：进程，会话，持锁模式，等待模式 等待者信息：进程，会话，持锁模式，等待模式先看下上述竞争的资源构成
SQL> select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where lower(type) like '%tx%';TYPE NAME ID1_TAG ID2_TAG IS_USER DESCRIPTION--------------- ------------------------- -------------------- -------------------- ---------- --------------------------------------------------TX Transaction usn<<16 | slot sequence YES Lock held by a transaction to allow other transact ions to wait for itSQL> select saddr,sid from v$session where sid in (241,261);SADDR SID---------------- ----------00000000DC90B030 24100000000DC8D0F70 261SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction where ses_addr in ('00000000DC90B030','00000000DC8D0F70'); XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC---------- ---------- ---------- ---------- ---------- ---------- ---------- 194 29 11368 7 755 2670 23 180 23 11845 7 531 2810 37SQL> select to_char(194,'xxxxxxx') xidusn,to_char(29,'xxxxxxx') xidslot,to_char(11368,'xxxxxxx') xidsqn from dual;XIDUSN XIDSLOT XIDSQN---------------- ---------------- ---------------- c2 1d 2c68可见竞争的资源第2部分表明xidusn,xidslot,xidsqn,对应于v$transaction
再看下did,参考前文：http://blog.itpub.net/9240380/viewspace-1819341/
session 241: DID 0001-00B8-00000003 session 261: DID 0001-00B9-00000001 session 261: DID 0001-00B9-00000001 session 241: DID 0001-00B8-00000003 可见死锁DID在2个会话就是交插的，很易理解，因为是死锁吗 继续看，会显示与死锁相关的表记录信息Rows waited on: Session 241: obj - rowid = 000125EF - AAASXvAAEAAAYJPAAA --相关的死锁会话，表记录对应的ROWID (dictionary objn - 75247, file - 4, block - 98895, slot - 0) --死锁的对象，所属文件及块号，以及槽编号 Session 261: obj - rowid = 000125EF - AAASXvAAEAAAYJPAAB (dictionary objn - 75247, file - 4, block - 98895, slot - 1)SQL> select object_id,object_name from dba_objects where object_id=75247;
OBJECT_ID OBJECT_NAME---------- ------------------------------ 75247 T_LOCKSQL> select a,b,rowid from scott.t_lock;
A B ROWID---------- ---------- ------------------ 1 1 AAASXvAAEAAAYJPAAA 2 2 AAASXvAAEAAAYJPAAB --通过死锁记录的ROWID可以定位到表对应的记录SQL> select a,b,rowid from scott.t_lock where rowid in ('AAASXvAAEAAAYJPAAA','AAASXvAAEAAAYJPAAB'); A B ROWID---------- ---------- ------------------ 1 1 AAASXvAAEAAAYJPAAA 2 2 AAASXvAAEAAAYJPAAB再看看上述的slot含义是什么呢？
SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block_no,dbms_rowid.rowid_row_number(rowid) record_no from scott.t_lock; FILE_NO BLOCK_NO RECORD_NO---------- ---------- ---------- 4 98895 0 4 98895 1结合BLOCK DUMP可知上述的slot代表record_no记录的标号
Block header dump: 0x0101824f Object id on Block? Y seg/obj: 0x125ef csc: 0x00.4b9039a itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1018248 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x00b4.017.00002e45 0x01c00213.0afa.25 ---- 1 fsc 0x0000.000000000x02 0x00c2.01d.00002c68 0x01c002f3.0a6e.17 ---- 1 fsc 0x0000.00000000 下面信息会显示导致死锁会话正在执行的SQL ----- Information for the OTHER waiting sessions -----Session 261: sid: 261 ser: 9 audsid: 31722060 user: 84/SCOTT flags: 0x45 pid: 185 O/S info: user: oracle, term: UNKNOWN, ospid: 15937 image: oracle@seconary (TNS V1-V3) client details: O/S info: user: oracle, term: pts/4, ospid: 15933 machine: seconary program: sqlplus@seconary (TNS V1-V3) application name: SQL*Plus, hash value=3669949024 current SQL: update t_lock set a=22 where a=2 --正在运行的SQL----- End of information for the OTHER waiting sessions -----死锁所属进程的信息包括了进程信息，所属会话的信息，会话运行SQL以及等待事件和历史等待事件列表相关信息