oracle blocking session

来源：互联网发布：笛子一般买什么调知乎编辑：程序博客网时间：2024/09/21 06:37

SQL> select sid from v$mystat where rownum=1;

SID
----------
129

SQL> insert into test.test01(id) values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> update test.test01 set id=3 where id=2;

1 row updated.

启动另外一个session

SQL> select sid from v$mystat where rownum=1;

SID
----------
128

SQL> update test.test01 set id=4 where id=2;

在前面的sid=129的session上查询

SQL> select sid,blocking_session from v$session where blocking_session is not null;

       SID BLOCKING_SESSION
---------- ----------------
       128              129

SQL> select sid,blocking_session ,event from v$session where blocking_session is not null;

       SID BLOCKING_SESSION EVENT
---------- ---------------- ------------------------------
       128              129 enq: TX - row lock contention

以上测试不难看出blocking_session字段表示了128这个session被blocking_session字段的值即129session阻塞了。

但有活动事务对对象加锁的时候,会在v$locked_object视图中有记录如object_id,session_id等,通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN字段都为空.下图中session_id为139的是被阻塞的session.

select * from v$locked_object;

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'

or
select sql_text, sql_fulltext, sql_id from v$sqlarea where sql_id='6BE7D33C';

or
select sql_text from v$sqltext where sql_id = '6BE7D33C';
这时候DBA可以联系造成阻塞的session结束事务或者根据情况用命令终止session

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

0 0