我们有一道面试题,原以为很简单,但是却发现面试者能够完美解出的几乎没有,一部分人有思路,但是可能是因为面试紧张,很难在指定时间内完成解题,而更大一部分人连思路也不清晰。
题目是:请将emp.empno=7369的记录ename字段修改为“ENMOTECH”并提交,你可能会遇到各种故障,请尝试解决。
其实题目的设计非常简单,一个RAC双节点的实例环境,面试人员使用的是实例2,而我们在实例1中使用select for update将EMP表加锁。
SQL> select * from emp for update;
此时在实例2中,如果执行以下SQL语句尝试更新ename字段,必然会被行锁堵塞。
SQL> update emp set ename='ENMOTECH' where empno=7369;
这道面试题中包含的知识点有:
1. 如何在另外一个session中查找被堵塞的session信息;
2. 如何找到产生行锁的blocker;
3. 在杀掉blocker进程之前会不会向面试监考人员询问,我已经找到了产生堵塞的会话,是不是可以kill掉;
4. 在获得可以kill掉进程的确认回复后,正确杀掉另一个实例上的进程。
这道题我们期待可以在5分钟之内获得解决,实际上大部分应试者在15分钟以后都完全没有头绪。
正确的思路和解法应该如下:
检查被阻塞会话的等待事件
更新语句回车以后没有回显,明显是被锁住了,那么现在这个会话经历的是什么等待事件呢?
SQL> select sid,event,username,sql.sql_text
2 from vsession s,vsql sql
3 where s.sql_id=sql.sql_id
4 and sql.sql_text like 'update emp set ename%';
SID EVENT USERNAME SQL_TEXT
---------- ------------------------------ ---------- ----------------------------------------------------------------------
79 enq: TX - row lock contention ENMOTECH 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 vlock 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 gvLock 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.
对于熟悉整个故障解决过程的人,5分钟之内就可以解决问题。
深入一步
对于TX锁,在vlock视图中显示的ID1和ID2是什么意思? 解释可以从vlock_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 vsession where sid=73;
TADDR
----------------
000000008E3B65C0
SQL> select XIDUSN,XIDSLOT,XIDSQN from vtransaction 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。
文章写到这里,忽然感觉网上那些一气呵成的故障诊断脚本其实挺误人的,只需要给一个参数,运行一下脚本就列出故障原因。所以很少人愿意再去研究这个脚本为什么这么写,各个视图之间的联系是如何环环相扣的。所以当你不再使用自己的笔记本,不再能迅速找到你赖以生存的那些脚本,你还能一步一步地解决故障吗?