Oracle会话阻塞

单实例的会话阻塞

模拟232会话被1224会话阻塞的情况

 select * from V$SESSION_BLOCKERS;
       SID SESS_SERIAL#    WAIT_ID WAIT_EVENT WAIT_EVENT_TEXT                BLOCKER_INSTANCE_ID BLOCKER_SID BLOCKER_SESS_SERIAL#
---------- ------------ ---------- ---------- ------------------------------ ------------------- ----------- --------------------
       232        14127         24        241 enq: TX - row lock contention                    1        1224                 3975 

会话1224所持有的锁阻塞了会话232执行的SQL语句。
1224 会话是阻塞者
232会话是等待者  

SYS > select sid,
    BLOCKING_SESSION ,
    event,seconds_in_wait from v$session where username='SCOTT';

       SID BLOCKING_SESSION EVENT                          SECONDS_IN_WAIT
---------- ---------------- ------------------------------ ---------------
       232             1224 enq: TX - row lock contention             1077      # 会话232等待行锁等待了1077秒
      1224                  SQL*Net message from client                170

232受阻塞会话等待的对象，等待的数据块号

 select row_wait_obj#, 
 row_wait_file#, 
 row_wait_block#,
 row_wait_row# 
 from v$session where sid=232;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        97896           1024          106534             9	

阻塞等待对象的详细信息

select owner, object_type, 
object_name, 
data_object_id 
from dba_objects where object_id=97896;
OWNER      OBJECT_TYP OBJECT_NAM DATA_OBJECT_ID
---------- ---------- ---------- --------------
SCOTT      TABLE      EMP                 97896

查看阻塞行的具体信息，需要对象号、文件号、块号和行号来生成rowid

select * from scott.emp
where rowid=dbms_rowid.rowid_create (rowid_type => 1, object_number => 97896, relative_fno => 1024 ,block_number =>106534, row_number => 9);

查询DML死锁会话sid，及引起死锁的堵塞者会话blocking_session

SELECT sid,event,seq#,p1,p1raw,p2,p3 FROM v$session WHERE wait_time=0;

如果正在等待，wait_time的值为0。如果已经完成，wait就不等于0. 使用wait_time来断言你是否找到潜在被阻塞的会话。

v$lock视图中，lmode代表会话持有锁的锁模式，request代表会话等待请求锁的锁模式，block=2代表R