oracle 锁 源头,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;