《2022年怎样快速查出Oracle数据库中的锁等待 .pdf》由会员分享，可在线阅读，更多相关《2022年怎样快速查出Oracle数据库中的锁等待 .pdf（4页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、- 在大型数据库系统中，为了保证数据的一致性，在对数据库中的数据进行操作时，系统会进行对数据相应的锁定。- 这些锁定中有 只读锁 、排它锁 ，共享排它锁 等多种类型，而且每种类型又有行级锁 （一次锁住一条记录），页级锁 （一次锁住一页，即数据库中存储记录的最小可分配单元），表级锁 （锁住整个表） 。- 若为 行级排它锁 ，则除被锁住的该行外，该表中其它行均可被其它的用户进行修改(Update) 或删除(delete) 操作，若为 表级排它锁 ，则所有其它用户只能对该表进行查询(select) 操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit) 或回滚后 (r
2、ollback) 后，锁住的资源便会得到释放，从而允许其它用户进行操作。- 但是，有时，由于程序中的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，如调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现死机 ，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其它用户的操作。- 因而，如何迅速地诊断出锁住资源的用户以及解决其锁定便是数据库管理员的一个挑战。- 由于数据库应用系统越来越复杂，一旦出现由于锁资源未及时释放的情况，便会引起对一相同表进行操作的大量用户无法进行操作，从而影响到系统的使用。此时，DBA 应尽量快地解决问题。但
3、是，由于在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 f
4、rom 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_et20*60 秒)的用户进程清除，然后才使得问题得到解决。- 但是， 这种方法实际上是把婴儿与脏水一起泼掉。因为，有些用户的进程尽管也为inactive ，并且也已有较长时间未活动，但是，那是由于他们处于锁等待状态。- 因而，我想出了一个解决办
5、法。即通过将问题发生时的v$lock ，v$session视图中的相关记录保存于自己建立的表中，再对该表进行查询，则速度大大提高，可以迅速发现问题。经实际使用，效果非常好。在接到用户反映后，几秒钟即可查出由于锁住资源而影响其它用户的进程，并进行相应的处理。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 4 页 - - - - - - - - - - 首先，以dba 身份（不一定为system ）登录入数据库中，创建三个基本表:my_session ，my_lock, my
6、_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 uniq
7、ue 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$l
8、ock 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
9、my_sqltext ( hash_value); - 然后，创建一个SQL 脚本文件，以便需要时可从SQL*Plus 中直接调用。 其中，首先用truncate table 表名命令将表中的记录删除。之所以用truncate 命令，而不是用 delete 命令，是因为 delete 命令执行时，将会产生重演记录，速度较慢，而且索引所占的空间并未真正释放，若反复做insert 及 delete, 则索引所占名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 4 页 - - -
10、 - - - - - - 的空间会不断增长，查询速度也会变慢。而truncate 命令不产生重演记录，速度执行较delete 快，而且索引空间被相应地释放出来。删除记录后，再将三个视图中的相关记录插入自己创建的三个表中。最后，对其进行查询，由于有索引，同时由于在插入时条件过滤后，记录数相对来说较少，因而查询速度很快，马上可以看到其结果。- 此时，若发现该阻塞其它用户进程的进程是正常操作中，则可通知该用户对其进行提交，从而达到释放锁资源的目的；若为未正常操作，即，其状态为inactive ，且其 last_call_et已为较多长时间，则可执行以下语句将该进程进行清除，系统会自动对其进行回滚，从
11、而释放锁住的资源。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
12、_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 col
13、umn 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; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整
14、理 - - - - - - - 第 3 页，共 4 页 - - - - - - - - - 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.req
15、uest=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 语句，故可以在事后将其记录下来，交给其开发人员进行分析并从根本上得到解决。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 4 页 - - - - - - - - -