在Oracle中，一个RAC双节点的实例环境...给EMP表加锁：请尝试解决这个故障。

DB宝 2019-11-03

576

♣题目部分在Oracle中，一个RAC双节点的实例环境，面试人员使用的是实例2，而在实例1中已经使用“SELECT * FROM SCOTT.EMP FOR UPDATE;”给EMP表加锁：
1SQL> SELECT * FROM SCOTT.EMP FOR UPDATE;

此时在实例2中，如果执行以下SQL语句尝试更新ENAME字段，那么必然会被行锁堵塞：
1SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369;

请尝试解决这个故障。

     
♣答案部分

这道面试题中包含的知识点有：
①　如何在另外一个SESSION中查找被堵塞的SESSION信息；
②　如何找到产生行锁的BLOCKER；
③　在杀掉BLOCKER进程之前会不会向面试监考人员询问，是否可以KILL掉阻塞者；
④　在获得可以KILL掉进程的确认回复后，正确杀掉另一个实例上的进程。
正确的思路和解法应该如下：
（1）检查被阻塞会话的等待事件
更新语句回车以后没有回显，明显是被阻塞了，那么现在这个会话当前是什么等待事件呢？可以通过SESSION等待去获取这些信息：
1SQL> 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%';
2
3       SID EVENT                              USERNAME    SQL_TEXT
4---------- ---------------------------------- ----------- ----------------------------------------------------------------
5        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字段即可。
 1SQL> SELECT SID,INST_ID,BLOCKING_INSTANCE,BLOCKING_SESSION FROM gv$session WHERE INST_ID=2 AND SID=31;
 2
 3       SID    INST_ID BLOCKING_INSTANCE BLOCKING_SESSION
 4---------- ---------- ----------------- ----------------
 5        31          2                 1               65
 6
 7SQL>  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;
 8
 9
10       SID EVENT                          USERNAME  SQL_TEXT
11---------- ------------------------------ --------- -------------------------------------
12        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视图中去查询即可，如下所示：
 1SQL> SQL> SELECT TYPE,ID1,ID2,LMODE,REQUEST FROM gv$lock WHERE sid=31 and INST_ID=2;
 2
 3TY        ID1        ID2      LMODE    REQUEST
 4-- ---------- ---------- ---------- ----------
 5AE        100          0          4          0
 6TX     262148       1512          0          6
 7TM      73201          0          3          0
 8
 9SQL> SELECT INST_ID,SID,TYPE,LMODE,REQUEST FROM gv$lock WHERE ID1=262148 and ID2=1512;
10
11   INST_ID        SID TY      LMODE    REQUEST
12---------- ---------- -- ---------- ----------
13         2         31 TX          0          6
14         1         65 TX          6          0

说明是实例1上的SID为65的会话阻塞了实例2上的SID为31的会话。
（3）乙方DBA需谨慎
第三个知识点是考核作为乙方的谨慎，即使查到了BLOCKER，是不是应该直接KILL掉，必须要先征询客户的意见，确认之后才可以杀掉。
（4）清除BLOCKER
已经确认了可以KILL掉SESSION之后，需要再找到相应SESSION的SERAIL#，这是KILL SESSION时必须输入的参数。
1SQL> SELECT SID,SERIAL# FROM gv$session WHERE INST_ID=1 AND SID=65;
2
3       SID    SERIAL#
4---------- ----------
5        65       3707

如果是 11gR2 数据库，那么直接在实例2中加入@1参数就可以杀掉实例1中的会话，如果是10g，那么登入实例1再执行KILL SESSION的操作。
1SQL> ALTER SYSTEM KILL SESSION '65,3707,@1' IMMEDIATE;
2
3System altered.

再检查之前被阻塞的更新会话，可以看到已经更新成功了。
1SQL> UPDATE SCOTT.EMP SET ENAME='ENMOTECH' WHERE EMPNO=7369;
2
31 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列的值的公式为：
1SELECT 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可以使用如下的脚本：
1SELECT INST_ID, SPID, PROGRAM,'KILL -9 '|| SPID  KILL9
2  FROM GV$PROCESS A
3 WHERE PROGRAM != 'PSEUDO'
4   AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SESSION)
5   AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$BGPROCESS)
6   AND (INST_ID, ADDR) NOT IN (SELECT INST_ID, PADDR FROM GV$SHARED_SERVER)
7;

Oracle 11g可以使用如下的脚本：
 1SET LINE 9999
 2COL SESSIONID FORMAT A20
 3COL SESSIONID_KILLED FORMAT A20
 4COL KILL_SESSION FORMAT A60
 5SELECT A.INST_ID,
 6       A.SID || ',' || A.SERIAL# || ',' ||
 7       (SELECT SPID
 8          FROM GV$PROCESS B
 9         WHERE B.INST_ID = A.INST_ID
10           AND A.CREATOR_ADDR = B.ADDR --AND DECODE(A.STATUS,'KILLED',A.CREATOR_ADDR,A.PADDR) = B.ADDR 
11        ) SESSIONID,
12       A.PADDR,
13       A.STATUS,
14       A.PROGRAM,
15       'ALTER SYSTEM DISCONNECT SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE;' KILL_SESSION
16  FROM GV$SESSION A
17 WHERE A.USERNAME = 'SYS'
18   AND A.STATUS = 'KILLED';


本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗