oracle数据库会话被锁,如何定位Oracle数据库被锁阻塞会话的根源

首先再次明确下,数据库因为要同时保证数据的并发性和一致性,所以操作有锁等待是正常的。只有那些长时间没有提交或回滚的事物,阻塞了其他业务正常操作,才是需要去定位处理的。

1.单实例环境

2.RAC环境

1. 单实例环境

实验环境:Oracle 10.2.0.5 单实例

会话1模拟业务操作:

SQL> select sid from v$mystat where rownum=1;

SID

----------

144

SQL> show user

USER is "JINGYU"

SQL> select * from t1 where id=1 for update;

ID N CONTENTS

---------- ---------- ----------------------------------------

1 1 Alfred Zhao

会话2模拟业务操作:

SQL> select sid from v$mystat where rownum=1;

SID

----------

149

SQL> show user

USER is "JINGYU"

SQL> update t1 set contents='Mcdull' where id=1;

这里update操作会卡住不动。用户感知就是长时间无法执行成功,很可能还会直接抱怨数据库性能慢。

会话3模拟DBA查看:

SQL> select sid from v$mystat where rownum=1;

SID

----------

145

SQL> show user

USER is "SYS"

SQL> select sid, username, blocking_session from v$session where blocking_session is not null;

SID USERNAME BLOCKING_SESSION

---------- ------------------------------ ----------------

149 JINGYU 144

SQL> select sid, serial#, username from v$session where sid=144;

SID SERIAL# USERNAME

---------- ---------- ------------------------------

144 102 JINGYU

这里可以清楚的看到会话149是被会话144阻塞,进一步查看会话144的serial#值。

这时候的处理方式一般有2种方案:

1)杀掉会话144,当然操作之前需要和应用负责人确认沟通好;

2)如果可以定位到144会话相关责任人,由他来提交或者回滚事物;

处理后可以看到会话2的update操作正常执行成功。

2.RAC环境

实验环境:Oracle 10.2.0.5 RAC

如果是RAC环境,还必须要定位到具体是哪个实例的会话,其实方法非常简单,查询时加入blocking_instance字段即可。

实例2模拟业务操作:

select sid from v$mystat where rownum=1;

select * from t1 where id=1 for update;

实例1模拟业务操作:

select sid from v$mystat where rownum=1;

update t1 set contents='Mcdull' where id=1;

会话模拟DBA查看:

SQL> select sid, username, blocking_instance, blocking_session from gv$session where blocking_session is not null;

SID USERNAME BLOCKING_INSTANCE BLOCKING_SESSION

---------- ------------------------------ ----------------- ----------------

129 JINGYU 2 129

SQL> select inst_id, sid, serial#, username from gv$session where sid=129;

INST_ID SID SERIAL# USERNAME

---------- ---------- ---------- ------------------------------

1 129 617 JINGYU

2 129 207 JINGYU

查询阻塞会话也要注意当前连接的实例,千万别弄错了,比如上面这个情况,如果确定可以杀掉阻塞会话,那么就需要到实例2去杀掉会话;

SQL> select instance_number from v$instance;

INSTANCE_NUMBER

---------------

2

SQL> alter system kill session '129,207';

System altered.

再次看被阻塞的会话操作已经恢复正常。

后记:

整理该文主要缘由是在之前的一次面试过程中,发现自己对这样基本的问题反而太依赖于别人写好的SQL,比如下面这类的SQL,开始并不知道此SQL的具体适用场景:

select a.sid blocker_sid, a.serial#, a.username as blocker_username, b.type,

decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,

b.ctime as time_held,c.sid as waiter_sid,

decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,

c.ctime time_waited

from v$lock b, v$enqueue_lock c, v$session a

where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1

order by time_held, time_waited;

所以对于专业的DBA来说,这样做是很不可取的。从现在起,自己要更多的研究这些基础知识,脚踏实地,练好内功,对于别人写的SQL,一定要彻底搞清楚含义之后再用。

0b1331709591d260c1c78e86d0c51c18.png

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值