[20151216]DID表示什么.txt
--我前几天问的问题:
http://www.itpub.net/thread-2048566-1-1.html
--得到刘工的指点,链接如下:
http://www.askmaclean.com/archives/identify-ksusetxn-didan-deadlock-id.html
--自己验证看看。
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t ( id number,name varchar2(20));
Table created.
SCOTT@book> insert into t values (1,'a');
1 row created.
SCOTT@book> insert into t values (2,'b');
1 row created.
SCOTT@book> commit ;
Commit complete.
2.测试:
--session 1:
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
232 547 49914 21 198 alter system kill session '232,547' immediate;
SCOTT@book> update t set name=name where id=1;
1 row updated.
--session 2:
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
68 427 49982 30 193 alter system kill session '68,427' immediate;
SCOTT@book> update t set name=name where id=2;
1 row updated.
--session 1:
SCOTT@book>update t set name=name where id=2;
--挂起!
--session 2:
SCOTT@book> update t set name=name where id=1;
--挂起!
--等1小会,出现在session 1:
SCOTT@book> update t set name=name where id=2;
update t set name=name where id=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
--检查跟踪文件出现:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00050018-000003ac 21 232 X 30 68 X
TX-0006001e-00000462 30 68 X 21 232 X
session 232: DID 0001-0015-000001F8 session 68: DID 0001-001E-00000154
session 68: DID 0001-001E-00000154 session 232: DID 0001-0015-000001F8
Rows waited on:
Session 232: obj - rowid = 00015ABA - AAAVq6AAEAAAAIMAAB
(dictionary objn - 88762, file - 4, block - 524, slot - 1)
Session 68: obj - rowid = 00015ABA - AAAVq6AAEAAAAIMAAA
(dictionary objn - 88762, file - 4, block - 524, slot - 0)
--按照刘工http://www.askmaclean.com/archives/identify-ksusetxn-didan-deadlock-id.html:
DID即Deadlock id,在Oracle死锁检测时充当一个进程的identifier.可以看到lmd RAC后台进程的global enqueue deadlock detected日
志中的DID与单实例死锁检测日志或10704事件跟踪日志中的DID在格式上存在区别,但他们的内容是一样的,我们以单实例中的DID为例:
002-001E-00000026
002:instance number,实例号
001E: 十进制的30,ORACLE PID,ORACLE进程号
0026:十进制的38,类似于v$session.serial#,ORACLE PID的一个串行值,复用ORACLE PID后会递增
global enqueue deadlock detected日志中的DID也是类似的,仅仅是把instance number和oracle pid的顺序颠倒了,同时pid要从高位
读起。
----------------------------------
DID 0001-0015-000001F8
0001 --表示实例号
0015 -- v$process.pid 0x15=21,就是v$process.pid .
000001F8 -- 1f8 = 504 ???? 还是不知道表示什么,做一个记录。
$ cat spid.sql
SELECT s.sid, s.serial#, p.spid, p.pid, p.serial# p_serial#,'alter system kill session '''||s.sid||','||s.serial#||''''||' immediate;' c50
FROM v$session s, v$process p
WHERE s.sid in (
SELECT sid
FROM v$mystat
WHERE rownum = 1
)
AND s.paddr = p.addr;