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

快速查出Oracle数据库中的锁等待---摘自《计算机世界日报》(文/赵华良)----在大型数据库系统中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。----这些锁定中有只读锁、排它锁，共享排它锁等多种类型，而且每种类型又有行级锁（一次锁住一条记录），页级锁（一次锁住一页，即数据库中存储记录的最小可分配单元），表级锁（锁住整个表）。----若为行级排它锁，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改(Update)或删除(delete)操作，若为表级排它锁，则所有其它用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚后(rollback)后，锁住的资源便会得到释放，从而允许其它用户进行操作。----但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现死机，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。----因而，如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。----由于数据库应用系统越来越复杂，一旦出现由于锁资源未及时释放的情况，便会引起对一相同表进行操作的大量用户无法进行操作，从而影响到系统的使用。此时，DBA应尽量快地解决问题。但是，由于在Oracle8.0.x中执行获取正在等待锁资源的用户名的查询语句selecta.username,a.sid,a.serial#,b.id1fromv$sessiona,v$lockbwherea.lockwait=b.kaddr----十分缓慢，（在Oracle7.3.4中执行很快)，而且，执行查找阻塞其它用户的用户进程的查询语句selecta.username,a.sid,a.serial#,b.id1fromv$sessiona,v$lockbwhereb.id1in(selectdistincte.id1fromv$sessiond,v$lockewhered.lockwait=e.kaddr)anda.sid=b.sidandb.request=0----执行得也十分缓慢。因而，往往只好通过将v$session中状态为inactive（不活动）并且最后一次进行操作时间至当前已超过20分钟以上(last_call_et20*60秒)的用户进程清除，然后才使得问题得到解决。----但是，这种方法实际上是把婴儿与脏水一起泼掉。因为，有些用户的进程尽管也为inactive，并且也已有较长时间未活动，但是，那是由于他们处于锁等待状态。----因而，我想出了一个解决办法。即通过将问题发生时的v$lock，v$session视图中的相关记录保存于自己建立的表中，再对该表进行查询，则速度大大提高，可以迅速发现问题。经实际使用，效果非常好。在接到用户反映后，几秒钟即可查出由于锁住资源而影响其它用户的进程，并进行相应的处理。----首先，以dba身份（不一定为system）登录入数据库中，创建三个基本表:my_session，my_lock,my_sqltext，并在将会进行查询的列上建立相应的索引。语句如下：rem从v$session视图中取出关心的字段，创建my_session表,并在查询要用到的字段上创建索引，以加快查询速度droptablemy_session;createtablemy_sessionasselecta.username,a.sid,a.serial#,a.lockwait,a.machine,a.status,a.last_call_et,a.sql_hash_value,a.programfromv$sessionawhere1=2;createuniqueindexmy_session_u1onmy_session(sid);createindexmy_session_n2onmy_session(lockwait);createindexmy_session_n3onmy_session(sql_hash_value);----rem从v$lock视图中取出字段，创建my_lock表，并在查询要用到的字段上创建索引，以加快查询速度droptablemy_lock;createtablemy_lockasselectid1,kaddr,sid,request,typefromv$lockwhere1=2;createindexmy_lock_n1onmy_lock(sid);createindexmy_lock_n2onmy_lock(kaddr);----rem从v$sqltext视图中取出字段，创建my_sqltext表，并在查询要用到的字段上创建索引，以加快查询速度droptablemy_sqltext;createtablemy_sqltextasselecthash_value,sql_textfromv$sqltextwhere1=2;createindexmy_sqltext_n1onmy_sqltext(hash_value);----然后，创建一个SQL脚本文件，以便需要时可从SQL*Plus中直接调用。其中，首先用truncatetable表名命令将表中的记录删除。之所以用truncate命令，而不是用delete命令，是因为delete命令执行时，将会产生重演记录，速度较慢，而且索引所占的空间并未真正释放，若反复做insert及delete,则索引所占的空间会不断增长，查询速度也会变慢。而truncate命令不产生重演记录，速度执行较delete快，而且索引空间被相应地释放出来。删除记录后，再将三个视图中的相关记录插入自己创建的三个表中。最后，对其进行查询，由于有索引，同时由于在插入时条件过滤后，记录数相对来说较少，因而查询速度很快，马上可以看到其结果。----此时，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为未正常操作，即，其状态为inactive，且其last_call_et已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从而释放锁住的资源。altersystemkillsession'sid,serial#';----SQL脚本如下：setechooffsetfeedbackoffprompt'删除旧记录.....'truncatetablemy_session;truncatetablemy_lock;truncatetablemy_sqltext;prompt'获取数据.....'insertintomy_sessionselecta.username,a.sid,a.serial#,a.lockwait,a.machine,a.status,a.last_call_et,a.sql_hash_value,a.programfromv$sessionawherenvl(a.username,'NULL')'NULL;insertintomy_lockselectid1,kaddr,sid,request,typefromv$lock;insertintomy_sqltextselecthash_value,sql_textfromv$sqltexts,my_sessionmwheres.hash_value=m.sql_hash_value;columnusernameformata10columnmachineformata15columnlast_call_etformat99999headingSecondscolumnsidformat9999prompt正在等待别人的用户selecta.sid,a.serial#,a.machine,a.last_call_et,a.username,b.id1frommy_sessiona,my_lockbwherea.lockwait=b.kaddr;prompt被等待的用户selecta.sid,a.serial#,a.machine,a.last_call_et,a.username,b.b.type,a.status,b.id1frommy_sessiona,my_lockbwhereb.id1in(selectdistincte.id1frommy_sessiond,my_lockewhered.lockwait=e.kaddr)anda.sid=b.sidandb.request=0;prompt查出其sqlselecta.username,a.sid,a.serial#,b.id1,b.type,c.sql_textfrommy_sessiona,my_lockb,my_sqltextcwhereb.id1in(selectdistincte.id1frommy_sessiond,my_lockewhered.lockwait=e.kaddr)anda.sid=b.sidandb.request=0andc.hash_value=a.sql_hash_value;----以上思路也可用于其它大型数据库系统如Informix,Sybase,DB2中。通过使用该脚本，可以极大地提高获取系统中当前锁等待的情况，从而及时解决数据库应用系统中的锁等待问题。而且，由于实际上已取出其program名及相应的sql语句，故可以在事后将其记录下来，交给其开发人员进行分析并从根本上得到解决。