删繁就简 - 云和恩墨的一道面试题解析
张乐奕，网名 Kamus
云和恩墨副总经理，Oracle ACE总监，ACOUG 联合创始人
我们有一道面试题，原以为很简单，但是却发现面试者能够完美解出的几乎没有，一部分人有思路，但是可能是因为面试紧张，很难在指定时间内完成解题，而更大一部分人连思路也不清晰。
请听题
题目是：请将 emp.empno=7369 的记录 ename 字段修改为“ENMOTECH”并提交，你可能会遇到各种故障,请尝试解决。
其实题目的设计非常简单，一个 RAC 双节点的实例环境，面试人员使用的是实例2，而我们在实例1中使用 select for update 将 EMP 表加锁:
SQL> SELECT * FROM emp FOR UPDATE;此时在实例2中，如果执行以下 SQL 语句尝试更新 ename 字段，必然会被行锁堵塞:
这道面试题中包含的知识点有：
如何在另外一个 session 中查找被堵塞的 session 信息；
如何找到产生行锁的 blocker；
在杀掉 blocker 进程之前会不会向面试监考人员询问，我已经找到了产生堵塞的会话，是不是可以kill掉；
在获得可以 kill 掉进程的确认回复后，正确杀掉另一个实例上的进程。
这道题我们期待可以在5分钟之内获得解决，实际上大部分应试者在15分钟以后都完全没有头绪。
注意：其实Oracle的任何复杂问题处理，都可以是由删繁就简的步骤逐层推演出来的，保持清醒的思路，对于DBA的工作非常重要。
正确的思路和解法应该如下：
检查被阻塞会话的等待事件
更新语句回车以后没有回显，明显是被锁住了，那么现在这个会话经历的是什么等待事件呢？
可以通过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 |
以上使用的是关联 v$sql 的 SQL 语句，实际上通过登录用户名等也可以快速定位被锁住的会话。
查找 blocker
得知等待事件是 enq: TX – row lock contention，行锁，接下来就是要找到谁锁住了这个会话。在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=79; SID INST_ID BLOCKING_INSTANCE BLOCKING_SESSION --- ------- ----------------- ---------------- 79 2 1 73 |
上述方法是最简单的，如果是使用更传统的方法，实际上也并不难，从 gv$lock 视图中去查询即可。
| 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需谨慎
第三个知识点是考核作为乙方的谨慎，即使你查到了 blocker，是不是应该直接 kill 掉，必须要先征询客户的意见，确认之后才可以杀掉。
清除blocker
已经确认了可以 kill 掉 session 之后，需要再找到相应 session的serail#，这是 kill session 时必须输入的参数。
| SQL> SELECT SID,SERIAL# FROM gv$session WHERE INST_ID=1 AND SID=73; SID SERIAL# ---------- ---------- 73 15625 |
如果是 11gR2 数据库，那么直接在实例2中加入@1参数就可以杀掉实例1中的会话，如果是10g，那么登入实例1再执行 kill session 的操作。
| SQL> ALTER system KILL SESSION '73,15625,@1'; System altered. |
再检查之前被阻塞的更新会话，可以看到已经更新成功了。
| SQL> UPDATE emp SET ename='ENMOTECH' WHERE empno=7369; 1 ROW updated. |
对于熟悉整个故障解决过程的人，或者具备清晰思路的DBA，5分钟之内就可以解决问题。
深入一步
对于 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 是事务的 USN+SLOT，而 ID2 则是事务的 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 直接就对应了，没有问题。 那么 ID1=589854 是如何对应的？将之转换为16进制，是 0x9001E，然后分高位和低位分别再转换为10进制，高位的16进制9就是十进制的9，也就是 XIDUSN=9，而低位的16进制1E转换为10进制是30，也就是 XIDSLOT=30。
文章写到这里，忽然感觉网上那些一气呵成的故障诊断脚本其实挺误人的，只需要给一个参数，运行一下脚本就列出故障原因。所以很少人愿意再去研究这个脚本为什么这么写，各个视图之间的联系是如何环环相扣的。所以当你不再使用自己的笔记本，不再能迅速找到你赖以生存的那些脚本，你还能一步一步地解决故障吗？
搜索 盖国强（Eygle）微信号：eeygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。
从Approx_Count_Distinct到M7的CPU集成
云和恩墨