数码控科技猎奇Iphone动漫星座游戏电竞lolcosplay王者荣耀攻略allcnewsBLOGNEWSBLOGASKBLOGBLOGZSK全部技术问答问答技术问答it问答代码软件新闻开发博客电脑/网络手机/数码笔记本电脑互联网操作系统软件硬件编程开发360产品资源分享电脑知识文档中心IT全部全部分类 全部分类技术牛文全部分类教程最新 网页制作cms教程平面设计媒体动画操作系统网站运营网络安全服务器教程数据库工具网络安全软件教学vbscript正则表达式javascript批处理更多»编程更新教程更新游戏更新allitnewsJava 新闻网络医疗信息化安全创业站长电商科技访谈域名会议专栏创业动态融资创投创业学院 / 产品经理创业公司人物访谈营销 开发数据库服务器系统虚拟化云计算 嵌入式移动开发作业作业1常见软件all电脑网络手机数码生活游戏体育运动明星影音休闲爱好文化艺术社会民生教育科学医疗健康金融管理情感社交地区其他电脑互联网软件硬件编程开发360相关产品手机平板其他电子产品摄影器材360硬件通讯智能设备购物时尚生活常识美容塑身服装服饰出行旅游交通汽车购房置业家居装修美食烹饪单机电脑游戏网页游戏电视游戏桌游棋牌游戏手机游戏小游戏掌机游戏客户端游戏集体游戏其他游戏体育赛事篮球足球其他运动球类运动赛车健身运动运动用品影视娱乐人物音乐动漫摄影摄像收藏宠物幽默搞笑起名花鸟鱼虫茶艺彩票星座占卜书画美术舞蹈小说图书器乐声乐小品相声戏剧戏曲手工艺品历史话题时事政治就业职场军事国防节日风俗法律法规宗教礼仪礼节自然灾害360维权社会人物升学入学人文社科外语资格考试公务员留学出国家庭教育学习方法语文物理生物工程学农业数学化学健康知识心理健康孕育早教内科外科妇产科儿科皮肤科五官科男科整形中医药品传染科其他疾病医院两性肿瘤科创业投资企业管理财务税务银行股票金融理财基金债券保险贸易商务文书国民经济爱情婚姻家庭烦恼北京上海重庆天津黑龙江吉林辽宁河北内蒙古山西陕西宁夏甘肃青海新疆西藏四川贵州云南河南湖北湖南山东江苏浙江安徽江西福建广东广西海南香港澳门台湾海外地区

快速查出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 语句,故可以在事后将其记录下来,交给其开发人员进行分析并从根本上得到解决。

  • 本文相关:
  • oracle使用instr或like方法判断是否包含字符串
  • oracle通过行范围查询取4至10行
  • oracle 11g 设置用户密码大小写敏感测试
  • Oracle 查询表信息获取表字段及字段注释
  • ORACLE 10G修改字符编码没有超字符集的限制
  • ORACLE DATAGUARD中手工处理日志v$archive_GAP的方法
  • 如何在Oracle中导入dmp文件
  • DBA_Oracle Startup / Shutdown启动和关闭过程详解(概念)(对数据库进行各种维护操作)
  • PL/SQL Dev连接Oracle弹出空白提示框的解决方法分享
  • Oracle将查询的结果放入一张自定义表中并再查询数据
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved