创建测试环境:
TEST@ PROD> create table t(id int constraint pk_id2 primary key,val int);
TEST@ PROD> select * from t;
ID VAL
---------- ----------
1 1
2 2
session 1:
查看SID:
SQL> select distinct sid from v$mystat;
SID
----------
125
update 数据,但不要提交:
TEST@ PROD> update t set val = 3 where id=1;
1 row updated.
session 2:
TEST@ PROD> select distinct sid from v$mystat;
SID
----------
141
update 数据,操作被阻塞:
TEST@ PROD> update t set val = 4 where id=1;
session 3(query):
但有活动事务对对象加锁的时候,会在v$locked_object视图中有记录如object_id,session_id等,
通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN字段都为空 ,下图中session_id为141的是被阻塞的session.
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- --------------- -------------------- ------------------------ -----------
5 21 1141 69926 125 TEST oracle 32086 3
0 0 0 69926 141 TEST oracle 31089 3
SQL> SELECT a.sid blocking_sid ,b.sid blocked_sid FROM v$lock a ,v$lock b
2 WHERE b.id1=a.id1 AND a.id2=b.id2
3 AND a.sid<>b.sid
4 AND b.request<>0;
BLOCKING_SID BLOCKED_SID
------------ -----------
125 141
在通过v$session可以查到session相关的信息,被阻塞的status一般为ACTIVE,还可以通过sql_address联合v$sql找到被阻塞的SQL语句.
SQL> select sid,serial#,status,sql_address from v$session where sid in(125,141);
SID SERIAL# STATUS SQL_ADDRESS
---------- ---------- -------- ----------------
125 27 INACTIVE 00
141 37 ACTIVE 00000000B8DFDF38
查看被block 的SQL
SQL> select sql_text from V$sql where address='00000000B8DFDF38';
SQL_TEXT
------------------------------------------------------------------------------------------
update t set val = 4 where id=1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27036311/viewspace-776874/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27036311/viewspace-776874/