張樂(lè )奕，網(wǎng)名 Kamus
云和恩墨副總經(jīng)理，Oracle ACE總監，ACOUG 聯(lián)合創(chuàng )始人
我們有一道面試題，原以為很簡(jiǎn)單，但是卻發(fā)現面試者能夠完美解出的幾乎沒(méi)有，一部分人有思路，但是可能是因為面試緊張，很難在指定時(shí)間內完成解題，而更大一部分人連思路也不清晰。
請聽(tīng)題
題目是：請將 emp.empno=7369 的記錄 ename 字段修改為“ENMOTECH”并提交，你可能會(huì )遇到各種故障,請嘗試解決。
其實(shí)題目的設計非常簡(jiǎn)單，一個(gè) RAC 雙節點(diǎn)的實(shí)例環(huán)境，面試人員使用的是實(shí)例2，而我們在實(shí)例1中使用 select for update 將 EMP 表加鎖:
SQL> SELECT * FROM emp FOR UPDATE;
此時(shí)在實(shí)例2中，如果執行以下 SQL 語(yǔ)句嘗試更新 ename 字段，必然會(huì )被行鎖堵塞:
SQL> UPDATE emp SET ename='ENMOTECH' WHERE empno=7369;
這道面試題中包含的知識點(diǎn)有：
如何在另外一個(gè) session 中查找被堵塞的 session 信息；
如何找到產(chǎn)生行鎖的 blocker；
在殺掉 blocker 進(jìn)程之前會(huì )不會(huì )向面試監考人員詢(xún)問(wèn)，我已經(jīng)找到了產(chǎn)生堵塞的會(huì )話(huà)，是不是可以kill掉；
在獲得可以 kill 掉進(jìn)程的確認回復后，正確殺掉另一個(gè)實(shí)例上的進(jìn)程。
這道題我們期待可以在5分鐘之內獲得解決，實(shí)際上大部分應試者在15分鐘以后都完全沒(méi)有頭緒。
注意：其實(shí)Oracle的任何復雜問(wèn)題處理，都可以是由刪繁就簡(jiǎn)的步驟逐層推演出來(lái)的，保持清醒的思路，對于DBA的工作非常重要。
正確的思路和解法應該如下：
檢查被阻塞會(huì )話(huà)的等待事件
更新語(yǔ)句回車(chē)以后沒(méi)有回顯，明顯是被鎖住了，那么現在這個(gè)會(huì )話(huà)經(jīng)歷的是什么等待事件呢？
可以通過(guò)SESSION等待去獲取這些信息：
SQL> SELECT sid,event,username,SQL.sql_text 2 FROM v$session s,v$sql SQL 3 WHERE s.sql_id=SQL.sql_id
4 AND SQL.sql_text LIKE 'update emp set ename%'; SID EVENT USERNAME
--- ------------------------------ ----------
79 enq: TX - ROW LOCK contention ENMOTECH
SQL_TEXT
---------------------------------------------------
UPDATE emp SET ename='ENMOTECH' WHERE empno=7369
以上使用的是關(guān)聯(lián) v$sql 的 SQL 語(yǔ)句，實(shí)際上通過(guò)登錄用戶(hù)名等也可以快速定位被鎖住的會(huì )話(huà)。
查找 blocker
得知等待事件是 enq: TX – row lock contention，行鎖，接下來(lái)就是要找到誰(shuí)鎖住了這個(gè)會(huì )話(huà)。在10gR2以后，只需要 gv$session 視圖就可以迅速定位 blocker，通過(guò) BLOCKING_INSTANCE 和 BLOCKING_SESSION 字段即可。
SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE,
BLOCKING_SESSION
FROM gv$session WHERE INST_ID=2 AND SID=79; SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION
--- ------- ----------------- ---------------- 79 2 1 73
上述方法是最簡(jiǎn)單的，如果是使用更傳統的方法，實(shí)際上也并不難，從 gv$lock 視圖中去查詢(xún)即可。
SQL> SELECT TYPE,ID1,ID2,LMODE,REQUEST
FROM v$lock WHERE sid=79; TY ID1 ID2 LMODE REQUEST
-- ---------- ---------- ----- -------
TX 589854 26267 0 6
AE 100 0 4 0
TM 79621 0 3 0 SQL> SELECT INST_ID,SID,TYPE,LMODE,REQUEST
FROM gv$Lock WHERE ID1=589854 AND ID2=26267; INST_ID SID TY LMODE REQUEST
---------- ---- -- ---------- ---------- 2 79 TX 0 6 1 73 TX 6 0
乙方DBA需謹慎
第三個(gè)知識點(diǎn)是考核作為乙方的謹慎，即使你查到了 blocker，是不是應該直接 kill 掉，必須要先征詢(xún)客戶(hù)的意見(jiàn)，確認之后才可以殺掉。
清除blocker
已經(jīng)確認了可以 kill 掉 session 之后，需要再找到相應 session的serail#，這是 kill session 時(shí)必須輸入的參數。
SQL> SELECT SID,SERIAL#
FROM gv$session
WHERE INST_ID=1 AND SID=73; SID SERIAL#
---------- ---------- 73 15625
如果是 11gR2 數據庫，那么直接在實(shí)例2中加入@1參數就可以殺掉實(shí)例1中的會(huì )話(huà)，如果是10g，那么登入實(shí)例1再執行 kill session 的操作。
SQL> ALTER system
KILL SESSION '73,15625,@1'; System altered.
再檢查之前被阻塞的更新會(huì )話(huà)，可以看到已經(jīng)更新成功了。
SQL> UPDATE emp SET ename='ENMOTECH'
WHERE empno=7369;
1 ROW updated.
對于熟悉整個(gè)故障解決過(guò)程的人，或者具備清晰思路的DBA，5分鐘之內就可以解決問(wèn)題。
深入一步
對于 TX 鎖，在 v$lock 視圖中顯示的 ID1 和 ID2 是什么意思？ 解釋可以從 v$lock_type 視圖中獲取。
SQL> SELECT ID1_TAG,ID2_TAG
FROM V$LOCK_TYPE WHERE TYPE='TX'; ID1_TAG ID2_TAG
--------------- ----------
usn<<16 | slot SEQUENCE
所以 ID1 是事務(wù)的 USN+SLOT，而 ID2 則是事務(wù)的 SQN。這些可以從 v$transaction 視圖中獲得驗證。
SQL> SELECT taddr
FROM v$session WHERE sid=73; TADDR
----------------
000000008E3B65C0
SQL> SELECT XIDUSN,XIDSLOT,XIDSQN
FROM v$transaction
WHERE addr='000000008E3B65C0'; XIDUSN XIDSLOT XIDSQN
---------- ---------- ---------- 9 30 26267
如何和 ID1=589854 and ID2=26267 對應呢？ XIDSQN=26267 和 ID2=26267 直接就對應了，沒(méi)有問(wèn)題。 那么 ID1=589854 是如何對應的？將之轉換為16進(jìn)制，是 0x9001E，然后分高位和低位分別再轉換為10進(jìn)制，高位的16進(jìn)制9就是十進(jìn)制的9，也就是 XIDUSN=9，而低位的16進(jìn)制1E轉換為10進(jìn)制是30，也就是 XIDSLOT=30。
文章寫(xiě)到這里，忽然感覺(jué)網(wǎng)上那些一氣呵成的故障診斷腳本其實(shí)挺誤人的，只需要給一個(gè)參數，運行一下腳本就列出故障原因。所以很少人愿意再去研究這個(gè)腳本為什么這么寫(xiě)，各個(gè)視圖之間的聯(lián)系是如何環(huán)環(huán)相扣的。所以當你不再使用自己的筆記本，不再能迅速找到你賴(lài)以生存的那些腳本，你還能一步一步地解決故障嗎？
如何加入"云和恩墨大講堂"微信群
