Oracle中診斷阻塞的session

由于鎖的機制,當某一條DML或者DDL SQL語句執行被阻塞的時候,需要找出是什么原因導致這條SQL被阻塞了,下面介紹一下通常的診斷方法:

假設有這樣一個表: table t(id int primary key,val int);數據為:

1,在一個Session,這里把它叫做Session A,做了如下的update語句,沒有提交或者回滾.
SQL> update t set val = 3 where id=1;

2,在一另一個Session,這里把它叫做Session B,做了如下的update語句,Session B會被阻塞.
 

SQL> update t set val = 4 where id=1;

但有活動事務對對象加鎖的時候,會在v$locked_object視圖中有記錄如object_id,session_id等,通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN字段都為空.下圖中session_id為139的是被阻塞的session.
 

select dbo.* from  v$locked_object lo ,dba_objects dbo  where lo.object_id = dbo.object_id and lo.xidusn=0

 

通過查詢v$lock可以看到是哪一個session阻塞了哪一個session:142阻塞了139

with blkedsess as (select * from v$lock where request !=0)
select  blkingsess.sid blockingsid, blkedsess.sid blockedsid
from v$lock blkingsess,blkedsess
where blkingsess.id1 = blkedsess.id1
and blkingsess.id2 = blkedsess.id2
and blkingsess.sid != blkedsess.sid 

在通過v$session可以查到session相關的信息,被阻塞的status一般為ACTIVE,還可以通過sql_address聯合v$sql找到被阻塞的SQL語句.

select sid,serial#,status,sql_address from v$session where sid in(139,142)

select * from V$sql where address='6BE7D33C'
 

這時候DBA可以聯系造成阻塞的session結束事務或者根據情況用命令終止session

alter system kill session '142,7'; 其中142為sid,7為serial#

session 142會收到如下錯誤,而session139往下執行后續步驟.
 

ERROR:
ORA-03114: not connected to Oracle

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]