實驗：

session 1:
SQL> show user;
USER is "SYS"
SQL>
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;

Table created.

SQL> alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);

Table altered.

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');

1 row created.

SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');

1 row created.

SQL> commit;

Commit complete.

SQL> select sid from v$mystat where rownum<2;

       SID
----------
        61

SQL>
SQL> select * from t_all_objs;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test1
TEST                           2013011702 test2

SQL> update t_all_objs set object_name='test11' where object_id=2013011701;

1 row updated.

未提交...................................

session 2:

SQL> update t_all_objs set object_name='test101' where object_id=2013011701;

1 row updated.

hang住了...........................

--顯然是有阻塞，假設我們只知道阻塞的物件是T_ALL_OBJS表，則排障如下，主要是根據將object_id關聯到v$session的ROW_WAIT_OBJ#，如下：
set lines 200 pages 999
col ORACLE_USERNAME for a14
col OBJECT_NAME for a20
col MACHINE for a14
col OS_USER_NAME for a14
col terminal for a14
select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       l.os_user_name,
       s.machine,
       s.terminal,
       o.object_name,
       o.object_type,
       o.object_id,
       s.logon_time
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
   and o.object_name='T_ALL_OBJS'
   and l.session_id = s.sid
order by sid, s.serial#;

       SID    SERIAL# LOCKED_MODE ORACLE_USERNAM OS_USER_NAME   MACHINE        TERMINAL       OBJECT_NAME          OBJECT_TYPE          OBJECT_ID LOGON_TIME
---------- ---------- ----------- -------------- -------------- -------------- -------------- -------------------- ------------------- ---------- ------------
        59        165           3 SYS            oracle         wang           pts/9          T_ALL_OBJS           TABLE                    89985 06-NOV-17
        61        721           3 SYS            oracle         wang           pts/8          T_ALL_OBJS           TABLE                    89985 06-NOV-17

或者直接查詢dba_object的object_id值。。。。。。。。。。。

接著關聯到ROW_WAIT_OBJ#=89985，即ROW_WAIT_OBJ#=object_id
SQL> select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention' and ROW_WAIT_OBJ#=89985;

       SID SQL_ID        STATUS   BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------- ---------------- ------------- -------------- --------------- -------------
        59 c53uad8st2u8t ACTIVE                 61         89985              1          102393             0

--接著根據blocking_seesin=61，查詢：
SQL> select sid,serial#,sql_id,status,event,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=61;

       SID    SERIAL# SQL_ID        STATUS   EVENT                                                            BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------- ------------- -------- ---------------------------------------------------------------- ---------------- ------------- -------------- --------------- -------------
        61        721               INACTIVE SQL*Net message from client                                                                  -1              0             0              0

--找到原因sid,殺掉：
SQL> alter system kill session '61,721' immediate;

System altered.

SQL>

--發現session 2已經提交了
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;

1 row updated.

--commit提交後查詢
SQL> commit;

Commit complete.

SQL> select * from t_all_objs;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST                           2013011701 test101
TEST                           2013011702 test2

SQL>

============================================================================================

或者直接用如下三種方法排查：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request<>0;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        59 TX     393249      10702          0          6        127          0
        61 TX     393249      10702          6          0        135          1

select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
   and a.id2 = b.id2
   and a.block = 1
   and b.block = 0;

HOLD_SID   WAIT_SID TY        ID1        ID2      CTIME
---------- ---------- -- ---------- ---------- ----------
        61         59 TX     393249      10702        108

select decode(request,0,'holder: ','waiter: ') ||
sid session_id, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
order by id1, request;

SESSION_ID                                              ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 61                                           393249      10702          6          0 TX
waiter: 59                                           393249      10702          0          6 TX

tx鎖之ROW_WAIT_OBJ#和object_id關聯排障

相關文章