先做一个简单事务锁等待：
窗口1：
窗口2：修改同样的数据
这时候我们会发现窗口2 的语句被卡住了。下面做个oradebug：
窗口3：
根据提示，打开所生成的trace文件，寻找有用的信息。
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 4768
process id: 21, ORACLE.EXE (SHAD)
session id: 155
session serial #: 28
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x7000b
p3: 'sequence'=0x9fd
time in wait: 2 min 2 sec
timeout after: never
wait id: 24
blocking: 0 sessions
current sql: update test set id=1111 where id=2
short stack: <-_ksedsts()+286<-_ksdxfstk()+14<-_ksdxcb()+1780<-_ssthreadsrgruncallback()+432<[email protected]()+825<-7C80B726<-00000000<-7C92E514<-7C80253D<-_skgpwwait()+128<-_ksliwat()+1440<-_kslwaitctx()+146<-_ksqcmi()+10381<-_ksqgtlctx()+1980<-_ksqgelctx()+502<-_ktcwit1()+316<-_kdddgb()+2378<-_kdusru()+407<-_updrowFastPath()+2375<-__VInfreq__qerupFetch()+1247<-_updaul()+1314<-_updThreePhaseExe()+272<-_updexe()+373<-_opiexe()+14214<-_kpoal8()+2292<-_opiodr()+1299<-_ttcpip()+2790<-_opitsk()+1278<-_opiino()+1067<-
wait history:
1. event: 'SQL*Net message from client'
wait id: 23 p1: 'driver id'=0x42455100
time waited: 15.706029 sec p2: '#bytes'=0x1
2. event: 'SQL*Net message to client'
wait id: 22 p1: 'driver id'=0x42455100
time waited: 0.000002 sec p2: '#bytes'=0x1
3. event: 'SQL*Net message from client'
wait id: 21 p1: 'driver id'=0x42455100
time waited: 0.000026 sec p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (orcl.orcl)
os id: 4812
process id: 18, ORACLE.EXE (SHAD)
session id: 170
session serial #: 9
}
which is waiting for 'SQL*Net message from client' with wait info:
{
p1: 'driver id'=0x42455100
p2: '#bytes'=0x1
time in wait: 2 min 29 sec
timeout after: never
wait id: 38
blocking: 1 session
current sql: <none>
short stack: <-_ksedsts()+286<-_ksdxfstk()+14<-_ksdxcb()+1780<-_ssthreadsrgruncallback()+432<[email protected]()+825<-7C80B726<-00000000<-7C92E514<-07F9535B<-07F93160<-06A31A0C<-06A321E1<-0699FF8F<-__PGOSF35__opikndf2()+989<-_opitsk()+584<-_opiino()+1067<-_opiodr()+1299<-_opidrv()+1319<-_sou2o()+45<-_opimai_real()+130<-_opimai()+92<[email protected]()+792<-7C80B726<-00000000
wait history:
1. event: 'SQL*Net message to client'
wait id: 37 p1: 'driver id'=0x42455100
time waited: 0.000005 sec p2: '#bytes'=0x1
2. event: 'SQL*Net message from client'
wait id: 36 p1: 'driver id'=0x42455100
time waited: 6.980115 sec p2: '#bytes'=0x1
3. event: 'SQL*Net message to client'
wait id: 35 p1: 'driver id'=0x42455100
time waited: 0.000003 sec p2: '#bytes'=0x1
}
在trace文件里面可以清晰的看到
sid 155的会话在等待：
enq: TX - row lock contention
该会话被sid 170阻塞，sid 170的会话在等待
SQL*Net message from client
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
其实在实际生产环境中，不像我实验的那么简单，多是数据库内部发生了死锁(争夺内核资源级别，比如pins和latch)，而不是我实验中这种简单的dml锁。
其中hanganalyze也是具有级别的：
1-2:只有hanganalyze输出，不dump任何进程
3：Level2+Dump出在IN_HANG状态的进程
4：Level3+Dump出在等待链里面的blockers(状态为LEAF/LEAF_NW/IGN_DMP)
5：Level4+Dump出所有在等待链中的进程（状态为NLEAF）
oracle官方建议不超过level 3，超过level 3会对系统带来额外的负担，一般level 3即可解决问题，定位到阻塞源头。
对于集群范围的debug，要使用如下命令：
和单实例的区别就是多了两条命令：
oradebug setinst all
oradebug -g def hanganalyze 3;
由于我是单实例环境，报错了。。。。。
---------------------------------------------------------------------------------------------------------------------------------------------------------------
将hanganalyze知识简单整理下，以备后用。