select阻塞update 之row lock contention

上周一个客户的系统，行锁争用比较严重，客户的代码没法发出来，就在自己的测试库重现一下

select 是有可能会阻塞update和insert 的（使用for update的情况下），避免这种情况可以加上nowait。
首先，我这个库非常闲

SQL> select event,sid,p1,p2,p3 from v$session_wait where event like '%row lock%';

no rows selected

测试表
SQL> desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNO                                                VARCHAR2(32)

编写源头锁语句
SQL>  select sno from t1 where sno=1 for update;


SNO
--------------------------------
1
1

执行update
SQL> update t1 set sno=2 where sno=1;

hang住了，一直执行，不出结果

SQL> select event,sid,p1,p2,p3 from v$session_wait where event like '%row lock%';


EVENT                                 SID         P1         P2         P3
------------------------------ ---------- ---------- ---------- ----------
enq: TX - row lock contention          58 1415053318     589836       1681

出现等待事件 enq: TX - row lock contention

查看在这个等待时间在等待什么
SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention';


ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        97660              4          272118             0
查看等待的对象是谁
SQL>     select object_name from dba_objects where object_id in (97660);


OBJECT_NAME
--------------------------------------------------------------------------------
T1
查看它的类型

SQL>     select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where object_name='T1';


OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
TEST       T1                             TABLE

对这个对象有什么操作

SQL> select s1.sid,s2.sql_text from v$session s1,v$sql s2 where s1.sid =58 
  2         and (s1.sql_id=s2.sql_id or s1.prev_sql_id=s2.sql_id);


 SID SQL_TEXT
---- --------------------------------------------------
  58 update t1 set sno=2 where sno=1

查看锁的状况
SQL>  select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 ;


 SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---- -- ---------- ---------- ---------- ---------- ---------- ----------
  46 TX     589836       1681          6          0       1520          1

SQL> SELECT A.SID, ' IS BLOCKING ', B.SID
  2    FROM V$LOCK A, V$LOCK B
  3  WHERE A.BLOCK = 1
  4     AND B.REQUEST > 0
  5     AND A.ID1 = B.ID1
  6     AND A.ID2 = B.ID2;


 SID 'ISBLOCKING'   SID
---- ------------- ----
  62  IS BLOCKING    58

找出锁源头的pid
然后kill －9

SQL>   SELECT SID,SPID,PID FROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID=46;


 SID SPID                            PID
---- ------------------------ ----------
  46 5073                             45


再次查看，update也执行完了。
SQL> update t1 set sno=2 where sno=1;


2 rows updated.

注：
       Name           Null        Type    
----------   ----------   ----------
ADDR                       RAW(0)
KADDR                      RAW(0)
SID                        NUMBER
TYPE                       VARCHAR2(2)
ID1                        NUMBER
ID2                        NUMBER
LMODE                      NUMBER
REQUEST                    NUMBER
CTIME                      NUMBER
BLOCK                      NUMBER

·SID：表示持有锁的会话信息。
·TYPE：表示锁的类型。值包括TM和TX等。
·LMODE：表示会话等待的锁模式的信息。用数字0－6表示，和表1相对应。
·REQUEST：表示session请求的锁模式的信息。
·ID1,ID2：表示锁的对象标识。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2109355/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2109355/