【DB筆試面試676】在Oracle中，一個RAC雙節點的實例環境…給EMP表加鎖：請嘗試解決這個故障。
- 2019 年 11 月 5 日
- 筆記
題目部分
在Oracle中，一個RAC雙節點的實例環境，面試人員使用的是實例2，而在實例1中已經使用「SELECT * FROM SCOTT.EMP FOR UPDATE;」給EMP表加鎖：
SQL> SELECT * FROM SCOTT.EMP FOR UPDATE;
此時在實例2中，如果執行以下SQL語句嘗試更新ENAME欄位，那麼必然會被行鎖堵塞：
SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369;
請嘗試解決這個故障。
♣
答案部分
這道面試題中包含的知識點有：
① 如何在另外一個SESSION中查找被堵塞的SESSION資訊；
② 如何找到產生行鎖的BLOCKER；
③ 在殺掉BLOCKER進程之前會不會向面試監考人員詢問，是否可以KILL掉阻塞者；
④ 在獲得可以KILL掉進程的確認回復後，正確殺掉另一個實例上的進程。
正確的思路和解法應該如下：
（1）檢查被阻塞會話的等待事件
更新語句回車以後沒有回顯，明顯是被阻塞了，那麼現在這個會話當前是什麼等待事件呢？可以通過SESSION等待去獲取這些資訊：
SQL> SELECT SID,EVENT,USERNAME,SQL.SQL_TEXT FROM V$SESSION S,V$SQL SQL WHERE S.SQL_ID=SQL.SQL_ID AND SQL.SQL_TEXT LIKE 'UPDATE SCOTT.EMP SET ENAME%'; SID EVENT USERNAME SQL_TEXT ---------- ---------------------------------- ----------- ---------------------------------------------------------------- 31 enq: TX - row lock contention SYS UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369
說明被阻塞的會話SID為31。以上使用的是關聯V$SQL的SQL語句，實際上通過登錄用戶名等也可以快速定位被鎖住的會話。
（2）查找 BLOCKER
得知等待事件是「enq: TX – row lock contention」，行鎖，接下來就是要找到誰鎖住了這個會話。在Oracle 10gR2以後，只需要查詢GV$SESSION視圖就可以迅速定位BLOCKER，通過BLOCKING_INSTANCE和BLOCKING_SESSION欄位即可。
SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE,BLOCKING_SESSION FROM gv$session WHERE INST_ID=2 AND SID=31; SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION ---------- ---------- ----------------- ---------------- 31 2 1 65 SQL> SELECT SID,EVENT,S.USERNAME,SQL.SQL_TEXT FROM GV$SESSION S,GV$SQL SQL WHERE (S.SQL_ID=SQL.SQL_ID OR S.PREV_SQL_ID=SQL.SQL_ID) AND S.INST_ID=SQL.INST_ID AND SID=65; SID EVENT USERNAME SQL_TEXT ---------- ------------------------------ --------- ------------------------------------- 65 SQL*Net message from client LHR SELECT * FROM SCOTT.EMP FOR UPDATE
可以看到，實例1上的SID為65的會話阻塞了實例2上的SID為31的會話，並且SQL語句為「SELECT * FROM SCOTT.EMP FOR UPDATE」。上述方法是最簡單的，如果使用更傳統的方法，那麼實際上也並不難，從GV$LOCK視圖中去查詢即可，如下所示：
SQL> SQL> SELECT TYPE,ID1,ID2,LMODE,REQUEST FROM gv$lock WHERE sid=31 and INST_ID=2; TY ID1 ID2 LMODE REQUEST -- ---------- ---------- ---------- ---------- AE 100 0 4 0 TX 262148 1512 0 6 TM 73201 0 3 0 SQL> SELECT INST_ID,SID,TYPE,LMODE,REQUEST FROM gv$lock WHERE ID1=262148 and ID2=1512; INST_ID SID TY LMODE REQUEST ---------- ---------- -- ---------- ---------- 2 31 TX 0 6 1 65 TX 6 0
說明是實例1上的SID為65的會話阻塞了實例2上的SID為31的會話。
（3）乙方DBA需謹慎
第三個知識點是考核作為乙方的謹慎，即使查到了BLOCKER，是不是應該直接KILL掉，必須要先徵詢客戶的意見，確認之後才可以殺掉。
（4）清除BLOCKER
已經確認了可以KILL掉SESSION之後，需要再找到相應SESSION的SERAIL#，這是KILL SESSION時必須輸入的參數。
SQL> SELECT SID,SERIAL# FROM gv$session WHERE INST_ID=1 AND SID=65; SID SERIAL# ---------- ---------- 65 3707
如果是 11gR2 資料庫，那麼直接在實例2中加入@1參數就可以殺掉實例1中的會話，如果是10g，那麼登入實例1再執行KILL SESSION的操作。
SQL> ALTER SYSTEM KILL SESSION '65,3707,@1' IMMEDIATE; System altered.
再檢查之前被阻塞的更新會話，可以看到已經更新成功了。
SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369; 1 row updated.
（5）延伸內容
在該問題中，面試官可能會再延伸的詢問以下問題：
① 在V$LOCK視圖中顯示的ID1和ID2是什麼意思？
答：在V$LOCK中，當TYPE列的值為TM鎖時，則ID1列的值為DBA_OBJECTS.OBJECT_ID，ID2列的值為0；當TYPE列的值為TX鎖時，則ID1列的值為視圖V$TRANSACTION中的XIDUSN欄位（Undo Segment Number，事務對應的撤銷段序列號）和XIDSLOT欄位（Slot Number，事務對應的槽位號），其中，ID1的高16位為XIDUSN，低16位為XIDSLOT。ID2列的值為視圖V$TRANSACTION中的XIDSQN欄位（Sequence Number：事務對應的序列號）。
當TYPE列的值為TX鎖時，計算ID1列的值的公式為：
SELECT TRUNC(ID1/POWER(2,16)) AS XIDUSN,BITAND(ID1,TO_NUMBER('FFFF','XXXX')) + 0 AS XIDSLOT , ID2 XIDSQN FROM DUAL;
所有與鎖有關的數據字典視圖之間的關聯關係如下圖所示：
② 在使用KILL命令殺會話的時候加上IMMEDIATE和不加IMMEDIATE的區別是什麼？KILLED狀態的會話如何刪除其OS進程？
答：一般情況下，可以通過執行SQL語句「ALTER SYSTEM KILL SESSION 'SID,SERIAL#'」直接殺掉會話。當SESSION是ACTIVE的時候，ALTER SYSTEM KILL SESSION只是將SESSION的狀態標識為KILLED，SERVER變為PSEUDO狀態，但可能並不會立即釋放SESSION所有所持有的資源，所以，在執行完ALTER SYSTEM KILL SESSION後，會話還是一直存在（V$SESSION視圖中存在，且後邊OS進程也存在）。所以，在執行命令KILL SESSION的時候，可以在後邊加上IMMEDIATE，這樣在沒有事務的情況下，相關會話就會立即被刪除而不會變為KILLED的狀態（V$SESSION視圖中不存在），當有事務存在的情況下，會先進行回滾相關的事務，然後釋放會話所佔有的資源。
另外，由於變為KILLED狀態的會話的PADDR列都變成了另外一個值，因此，通過平常的連接方式就沒有辦法關聯到後台進程，在Oracle 11g下提供了CREATOR_ADDR列，該列可以關聯到後台進程，對於Oracle 10g可以通過特殊的SQL找到後台的進程號。
Oracle 10g可以使用如下的腳本：
SELECT INST_ID, SPID, PROGRAM,'KILL -9 '|| SPID KILL9 FROM GV$PROCESS A WHERE PROGRAM != 'PSEUDO' AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SESSION) AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$BGPROCESS) AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SHARED_SERVER) ;
Oracle 11g可以使用如下的腳本：
SET LINE 9999 COL SESSIONID FORMAT A20 COL SESSIONID_KILLED FORMAT A20 COL KILL_SESSION FORMAT A60 SELECT A.INST_ID, A.SID || ',' || A.SERIAL# || ',' || (SELECT SPID FROM GV$PROCESS B WHERE B.INST_ID = A.INST_ID AND A.CREATOR_ADDR = B.ADDR --AND DECODE(A.STATUS,'KILLED',A.CREATOR_ADDR,A.PADDR) = B.ADDR ) SESSIONID, A.PADDR, A.STATUS, A.PROGRAM, 'ALTER SYSTEM DISCONNECT SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE;' KILL_SESSION FROM GV$SESSION A WHERE A.USERNAME = 'SYS' AND A.STATUS = 'KILLED';
本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗
