IT博客汇 | How to Analyse Row Lock Contention in Oracle 10gR2 and later

How to Analyse Row Lock Contention in Oracle 10gR2 and later

kamus发表于 2015-04-04 08:56:29

我们有一道面试题，原以为很简单，但是却发现面试者能够完美解出的几乎没有，一部分人有思路，但是可能是因为面试紧张，很难在指定时间内完成解题，而更大一部分人连思路也不清晰。

题目是：请将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 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   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 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.

对于熟悉整个故障解决过程的人，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。

文章写到这里，忽然感觉网上那些一气呵成的故障诊断脚本其实挺误人的，只需要给一个参数，运行一下脚本就列出故障原因。所以很少人愿意再去研究这个脚本为什么这么写，各个视图之间的联系是如何环环相扣的。所以当你不再使用自己的笔记本，不再能迅速找到你赖以生存的那些脚本，你还能一步一步地解决故障吗？

Share/Save