分享
 
 
 

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

王朝oracle·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

---- 在大型数据库系统中,为了保证数据的一致性,在对数据库中的数据进行操作时,系统会进行对数据相应的锁定。

---- 这些锁定中有"只读锁"、"排它锁","共享排它锁"等多种类型,而且每种类型又有"行级锁"(一次锁住一条记录),"页级锁"(一次锁住一页,即数据库中存储记录的最小可分配单元),"表级锁"(锁住整个表)。

---- 若为"行级排它锁",则除被锁住的该行外,该表中其它行均可被其它的用户进行修改(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_et20*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 语句,故可以在事后将其记录下来,交给其开发人员进行分析并从根本上得到解决。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有