阻塞会话处理和死锁

阻塞会话处理和死锁
模拟实验：

模拟故障--会话被级联阻塞

准备工作：
我这里在每个实例开两个会话来模拟RAC在负载均衡模式下的业务会话：
实例1：会话1，会话2;
实例2：会话3，会话4;
在 时间点1 -> 时间点2 -> 时间点3 -> 时间点4 的这个时间轴上分别执行以下操作：

时间点1：
在实例1的会话1（INS1-session1）执行语句未提交或回滚：

select * from v$mystat where rownum = 1;update emp set sal = 8000 where empno = 7788;

时间点2：
在实例2的会话3（INS2-session3）执行语句：

select * from v$mystat where rownum = 1;

delete from emp where empno = 7839;

update emp set job = 'MANAGER' where empno = 7788;

rollback;

时间点3：
在实例2的会话4（INS2-session4）执行语句：

select * from v$mystat where rownum = 1;

update emp set sal = 15000 where empno = 7839;

rollback;

 

时间点4：
在实例1的会话2（INS1-session2）执行语句：

select * from v$mystat where rownum = 1;

update emp set job = 'CEO' where empno = 7839;

rollback;

处理方法：

立即找出最终阻塞会话

select *
  from (select a.inst_id,
               a.sid,
               a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid || '@' || a.inst_id) = prior
                   (a.blocking_session || '@' || a.blocking_instance))
 where isleaf = 1
 order by tree_level asc;

然后经过确认在对应节点杀掉此会话：

alter system kill session '68,313' immediate;

==================================================================================================================================

死锁

定义：当两个用户希望持有对方的资源时就会发生死锁，即两个用户互相等待对方释放时，oracle认定为产生了死锁。

在这种情况下，将以牺牲一个用户作为代价，另一个用户继续执行，牺牲的用户的事务将回滚。

起因：oracle死锁问题实际很少见，如有发生，基本都是不正确的程序设计造成的

oracle会自动发现死锁，选择代价最小的给予撤销
若死锁不能自动释放，需要手工kill session，步骤如下：
1、查看有没有死锁对象：

select 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  from v$session
 where sid in (select sid from v$lock where block = 1);

2、查看导致死锁的sql
select s.sid, q.sql_text
  from v$sql_text q, v$session s
 where q.address = s.sql_address
   and s.sid = &sid
 order by piece;


3、查看谁锁了谁
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
       ' ) is blocking ' || s2.username || ' ( SID=' || s2.sid || ' )' as blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
 where s1.sid = l1.sid
   and s2.sid = l2.sid
   and l1.BLOCK = 1
   and l2.request > 0
   and l1.id1 = l2.id1
   and l2.id2 = l2.id2;

或者还是用上面的脚本：

select *
  from (select a.inst_id,
               a.sid,
               a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
               level as tree_level
          from gv$session a
         start with a.blocking_session is not null
        connect by (a.sid || '@' || a.inst_id) = prior
                   (a.blocking_session || '@' || a.blocking_instance))
 where isleaf = 1
 order by tree_level asc;