原文鏈接：https://www.modb.pro/db/21859

導讀：Oracle RAC環境下定位並殺掉最終阻塞的會話，本文通過一個測試demo來具體介紹。

實驗環境：

Oracle RAC 11.2.0.4 （2節點）

• 1.模擬故障：會話被級聯阻塞
• 2.常規方法：梳理找出最終阻塞會話
• 3.改進方法：立即找出最終阻塞會話

但上文給出的例子過於簡單，實際對於生產中複雜的阻塞問題，一步步找最終阻塞就比較麻煩。所以本篇旨在尋求更好更快捷的辦法。

1. 模擬故障：會話被級聯阻塞

準備工作：
我這裡在每個實例開兩個會話來模擬RAC在負載均衡模式下的業務會話：
實例1：會話1，會話2;
實例2：會話3，會話4;
在 時間點1 -> 時間點2 -> 時間點3 -> 時間點4 的這個時間軸上分別執行以下操作：

時間點1：
在實例1的會話1（INS1-session1）執行語句未提交或回滾：

<code>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）執行語句：/<code>

時間點2：
在實例2的會話3（INS2-session3）執行語句：

<code>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）執行語句：/<code>

時間點3：在實例2的會話4（INS2-session4）執行語句：

<code>select * from v$mystat where rownum = 1;update emp set sal = 15000 where empno = 7839;rollback;/<code>

時間點4：
在實例1的會話2（INS1-session2）執行語句：

<code>select * from v$mystat where rownum = 1;
update emp set job = 'CEO' where empno = 7839;
rollback;/<code>

此時可以看到，在後面3個時間點進行操作的會話均hang住，顯然都是被阻塞了。4個會話的現象如下：

那麼他們究竟都是被誰阻塞了呢？下文會詳細分析。

2.常規方法：梳理找出最終阻塞會話

我們常規會去GV$SESSION查詢blocking_session，再看這個blocking_session有沒有又被其他會話阻塞，直到找到根源。

<code>--blockingset lines 180col program for a30col machine for a20select inst_id,       SID,       SERIAL#,     event,     machine,       sql_id,       blocking_session,       blocking_instance  from gv$session where blocking_session is not null;/<code>

結果如下：

<code>SYS@jyzhao1 >--blocking
SYS@jyzhao1 >set lines 180
SYS@jyzhao1 >col program for a30
SYS@jyzhao1 >col machine for a20
SYS@jyzhao1 >select inst_id,
  2         SID,
  3         SERIAL#,
  4        event,
  5        machine,
  6         sql_id,
  7         blocking_session,
  8         blocking_instance
  9    from gv$session
 10   where blocking_session is not null;

   INST_ID        SID    SERIAL# EVENT                                    MACHINE              SQL_ID        BLOCKING_SESSION BLOCKING_INSTANCE
---------- ---------- ---------- ---------------------------------------- -------------------- ------------- ---------------- -----------------
         1        146       6283 enq: TX - row lock contention            jyrac1               052gy77vp276s               25                 2
         2         25      10250 enq: TX - row lock contention            jyrac2               3t2npbvdcf2d2              150                 1
         2        145      32069 enq: TX - row lock contention            jyrac2               0ct116qw46shq               25                 2

SYS@jyzhao1 >/<code>

可以看到實例1的sid=146的會話以及實例2的sid=145的會話都被實例2的sid=25的會話阻塞，而實例2的sid=25的這個會話又被實例1的sid=150的會話阻塞。這個例子只模擬了幾個會話尚且可以快速定位，但如果是真實故障，很可能受影響的不止這麼幾個會話，雖然也可以慢慢最終找出來，但畢竟會看的眼花繚亂是不是。我們高傲的DBA又怎麼會甘心一直去做這種事情呢？

3.改進方法：立即找出最終阻塞會話

之前我在單實例或者確認業務只跑在某一個節點的環境，一直在用的一個找出最終阻塞會話的腳本：

<code>--cascade blocking
set lines 200 pages 100
col tree for a30
col event for a40
select *
  from (select a.sid, a.serial#,
               a.sql_id,
               a.event,
               a.status,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(SID, '               level as tree_level
          from v$session a
         start with a.blocking_session is not null
        connect by nocycle a.sid = prior a.blocking_session)
 where isleaf = 1
 order by tree_level asc;/<code>

這個腳本用到了start with connect by prior 的遞歸查詢用法，非常方便可以直接找出最終阻塞的會話；可如果是RAC，業務是負載均衡跑在多個節點的，那上面的這個腳本就不好用了，比如我上面構造的這個例子，就需要明確查出各個會話分別在哪個實例上，否則你怎麼確認去哪裡殺呢，怎麼辦呢？其實也簡單，只需要稍加改動下這個腳本即可，改後如下：

<code>--cascade blocking@gv$session 

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, '                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;/<code>

結果如下：

<code>--cascade blocking@gv$session
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, '                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;/<code>

非常清晰可以看到最終阻塞其他會話的會話是實例1的sid=150，serial#=8742的會話。
那麼與相關人員都確認後，就可以直接殺掉這個最終阻塞會話：

<code>SYS@jyzhao1 >alter system kill session '150,8742' immediate;
System altered./<code>

再次查詢，恢復正常，不再有堵塞了：

<code>SYS@jyzhao1 >--cascade blocking@gv$session
SYS@jyzhao1 >select *
  2    from (select a.inst_id, a.sid, a.serial#,
  3                 a.sql_id,
  4                 a.event,
  5                 a.status, 

  6                 connect_by_isleaf as isleaf,
  7                 sys_connect_by_path(a.SID||'@'||a.inst_id, '   8                 level as tree_level
  9            from gv$session a
 10           start with a.blocking_session is not null
 11          connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance))
 12   where isleaf = 1
 13   order by tree_level asc;

no rows selected

SYS@jyzhao1 >/<code>

至此，就達到了我們在RAC環境中快速定位並殺掉這種最終阻塞會話的目的。

想了解更多關於數據庫、雲技術的內容嗎？

快來關注“數據和雲"、"雲和恩墨，"公眾號及"雲和恩墨"官方網站，我們期待大家一同學習與進步！

小程序”DBASK“在線問答，隨時解惑，歡迎瞭解和關注！

閱讀更多 數據和雲智能 的文章

關鍵字: 定位 connect where