前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >如何定位Oracle数据库被锁阻塞会话的根源

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

作者头像
Alfred Zhao
发布2019-05-24 20:32:31
1.2K0
发布2019-05-24 20:32:31
举报

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

1.单实例环境 2.RAC环境

1. 单实例环境

实验环境:Oracle 10.2.0.5 单实例

会话1模拟业务操作:

代码语言:javascript
复制
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模拟业务操作:

代码语言:javascript
复制
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查看:

代码语言:javascript
复制
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模拟业务操作:

代码语言:javascript
复制
select sid from v$mystat where rownum=1; 
select * from t1 where id=1 for update;

实例1模拟业务操作:

代码语言:javascript
复制
select sid from v$mystat where rownum=1; 
update t1 set contents='Mcdull' where id=1;  

会话模拟DBA查看:

代码语言:javascript
复制
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去杀掉会话;

代码语言:javascript
复制
SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              2

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

System altered.

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

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

代码语言:javascript
复制
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,一定要彻底搞清楚含义之后再用。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2016-12-28 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1. 单实例环境
  • 2.RAC环境
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档