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

共计 2751 个字符，预计需要花费 7 分钟才能阅读完成。

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

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，一定要彻底搞清楚含义之后再用。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-12/138927.htm