快速查出Oracle数据库中锁等待的方法

飞奔的炮台

通常在大型数据库系统中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。
这些锁定中有"只读锁"、"排它锁"，"共享排它锁"等多种类型，而且每种类型又有"行级锁"（一次锁住一条记录），"页级锁"（一次锁住一页，即数据库中存储记录的最小可分配单元），"表级锁"（锁住整个表）。若为"行级排它锁"，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改(Update)或删除(delete)操作，若为"表级排它锁"，则所有其它用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。
但是在某些情况下，由于程序中的一些特殊原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现"死机"，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，最终出现影响到其它用户操作的情况。
因而，如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。
由于数据库应用系统越来越复杂， 一旦出现由于锁资源未及时释放的情况，便会引起对一相同表进行操作的大量用户无法进行操作，从而影响到系统的使用。此时，DBA应尽量快地解决问题。但是，由于在Oracle 8.0.x 中执行"获取正在等待锁资源的用户名"的查询语句

select a.username, a.sid, a.serial#, b.id1
 from v$session a, v$lock b
 where a.lockwait = b.kaddr

十分缓慢，（在 Oracle 7.3.4中执行很快)，而且，执行"查找阻塞其它用户的用户进程"的查询语句

select a.username, a.sid, a.serial#, b.id1
   from v$session a, v$lock b
   where b.id1 in
    (select distinct e.id1
     from v$session d, v$lock e
     where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request = 0

执行得也十分缓慢。因而，往往只好通过将 v$session 中状态为"inactive"（不活动）并且最后一次进行操作时间至当前已超过 20 分钟以上(last_call_et>20*60 秒)的用户进程清除，然后才使得问题得到解决。
但是，这种一刀切的方法实际上是"把婴儿与脏水一起泼掉"。因为，有些用户的进程尽管也为"inactive"，并且也已有较长时间未活动，但是，那是由于他们处于锁等待状态。
因而，笔者想到了一个解决办法。即通过将问题发生时的 v$lock，v$session视图中的相关记录保存于自己建立的表中，再对该表进行查询，则速度大大提高，可以迅速发现问题。经实际使用，效果非常好。在接到用户反映后，几秒钟即可查出由于锁住资源而影响其它用户的进程，并进行相应的处理。
首先，以 dba 身份（不一定为system）登录入数据库中，创建三个基本表:my_session，my_lock, my_sqltext，并在将会进行查询的列上建立相应的索引。语句如下：
rem 从 v$session 视图中取出关心的字段，创建 my_session 表,并在查询要用到的字段上创建索引，以加快查询速度

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where 1=2 ;
create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

rem 从 v$lock 视图中取出字段，创建 my_lock 表，并在查询要用到的字段上创建索引，以加快查询速度

drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
 from v$lock
 where 1=2;
create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

rem 从 v$sqltext 视图中取出字段，创建 my_sqltext 表，并在查询要用到的字段上创建索引，以加快查询速度

drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
 from v$sqltext
 where 1=2;
create index my_sqltext_n1 on my_sqltext ( hash_value);

然后，创建一个 SQL 脚本文件，以便需要时可从 SQL*Plus 中直接调用。其中，首先用 truncate table 表名命令将表中的记录删除。之所以用 truncate 命令，而不是用delete 命令，是因为delete 命令执行时，将会产生重演记录，速度较慢，而且索引所占的空间并未真正释放，若反复做 insert及delete,则索引所占的空间会不断增长，查询速度也会变慢。而 truncate命令不产生重演记录，速度执行较delete快，而且索引空间被相应地释放出来。删除记录后，再将三个视图中的相关记录插入自己创建的三个表中。最后，对其进行查询，由于有索引，同时由于在插入时条件过滤后，记录数相对来说较少，因而查询速度很快，马上可以看到其结果。
此时，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为未正常操作，即，其状态为"inactive"，且其last_call_et已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。

alter system kill session 'sid, serial#';

SQL 脚本如下：

set echo off
set feedback off
prompt '删除旧记录.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;
prompt '获取数据.....'
insert into my_session
select a.username, a.sid, a.serial#,
  a.lockwait, a.machine,a.status,
  a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where nvl(a.username,'NULL')< >'NULL;
insert into my_lock
select id1, kaddr, sid, request,type
 from v$lock;
insert into my_sqltext
select hash_value , sql_text
 from v$sqltext s, my_session m
 where s.hash_value=m.sql_hash_value;
column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999
prompt "正在等待别人的用户"
select a.sid, a.serial#, 
a.machine,a.last_call_et, a.username, b.id1
 from my_session a, my_lock b
 where a.lockwait = b.kaddr;
prompt "被等待的用户"
select a.sid, a.serial#, 
a. machine, a.last_call_et,a.username,
b. b.type,a.status,b.id1
 from my_session a, my_lock b
 where b.id1 in
   (select distinct e.id1
   from my_session d, my_lock e
  where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0;
prompt "查出其 sql "
select a.username, a.sid, a.serial#,
 b.id1, b.type, c.sql_text
 from my_session a, my_lock b, my_sqltext c
 where b.id1 in
  (select distinct e.id1
   from my_session d, my_lock e
   where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0
  and c.hash_value =a.sql_hash_value;

以上思路也可用于其它大型数据库系统如 Informix, Sybase,DB2中。通过使用该脚本，可以极大地提高获取系统中当前锁等待的情况，从而及时解决数据库应用系统中的锁等待问题。而且，由于实际上已取出其 program 名及相应的 sql 语句，故可以在事后将其记录下来，交给其开发人员进行分析并从根本上得到解决。