知识分享__Oracle数据库发生一连串的锁时，怎么找到锁的源头

数据库并发量大的时候如果先后更新相同的资源就比较容易引起阻塞，会话A阻塞会话B，会话B又阻塞了会话C，会话C又阻塞了会话D……
一连串的阻塞，像是没完没了，如何去找到源头来释放资源呢？

这个问题让人很抓狂，很多人感叹很难找到锁定的源头。
其实Oracle自己已经提供了一个脚本来查询这些阻塞的会话，而且是用结构来显示出来:
SQL> @?/rdbms/admin/utllockt.sql

就这样轻松知道是谁阻塞了谁了！以下输出例子：

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- ---------------------------- ---------------------------- ----------------- -----------------
72 None
132 Transaction Exclusive Exclusive 65562 1091
199 Transaction Exclusive Exclusive 131075 1234

如果没法用sysdba登录到数据库服务器怎么办？ 可以直接使用connect by的方式查到源头：
set linesize 200
column root_sid format 9999999999
column serial# format 9999999999
column avg_wait_seconds format 9999999999
column username format a10
column event format a30
column MACHINE format a15
column PROGRAM format a12
column status format a8
column sql_id format a18
column prev_sql_id format a18
select r.root_sid, s.serial#,
r.blocked_num,trunc( r.avg_wait_seconds) as avg_wait_seconds ,
s.username,s.status,s.event,s.MACHINE,
s.PROGRAM,s.sql_id,s.prev_sql_id
from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,
count(*) - 1 as blocked_num
from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
from v$session
start with blocking_session is null
connect by prior sid = blocking_session)
group by root_sid
having count(*) > 1) r,
v$session s
where r.root_sid = s.sid;

ROOT_SID SERIAL# BLOCKED_NUM AVG_WAIT_SECONDS USERNAME STATUS EVENT MACHINE PROGRAM SQL_ID PREV_SQL_ID
---------- ----------- ----------- ---------------- ---------- -------- ------------------------------ --------------- ------------ ------------------ ---------------
72 16 2 5842 LUO INACTIVE SQL*Net message from client WORKGROUP\ZTXD sqlplus.exe 0kpw0yqdurkk7

为了大家的方便，以下语句增加了杀会话的脚本，运行结果可以拿来执行，杀掉阻塞的会话:
select r.root_sid, s.serial#,
r.blocked_num,trunc( r.avg_wait_seconds) as avg_wait_seconds ,
s.username,s.status,s.event,s.MACHINE,
s.PROGRAM,s.sql_id,s.prev_sql_id,
'alter system kill session '||''''|| r.root_sid||','||s.serial#||''''||';'
from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,
count(*) - 1 as blocked_num
from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
from v$session
start with blocking_session is null
connect by prior sid = blocking_session)
group by root_sid
having count(*) > 1) r,
v$session s
where r.root_sid = s.sid;