SQL - lock select检查

Question

我在3个会话上创建了一个锁，一个数据库记录-TX表。

接下来，我编写了一个查询来检查数据库Oracle上的锁，但有些地方不太对劲：

select 
    round(s1.seconds_in_wait/60,1) as TIME, s1.event,
    s1.blocking_session as SID_A, s1.username as USER_A, 
    s2.sid as SID_B, s2.username as USER_B  
from 
    v$session  s1, v$session s2   
where
     s1.blocking_session is not null 
     and s1.seconds_in_wait > 1  
     and s1.sid = s2.sid  
order by 
     s1.seconds_in_wait desc;

结果：

| Time | Evetn              |SID_A| USER_A  |SID_B| USER_B  |
-------------------------------------------------------------
|10.1 | enq: TX row lock.. |  45 | Schema1 |  54 | Schema1 |
|15.5 | enq: TX row lock.. |  45 | Schema2 |  95 | Schema2 |

相反，应该是这样的：

| Time | Evetn              |SID_A| USER_A  |SID_B| USER_B  |
-------------------------------------------------------------
|10.1 | enq: TX row lock.. |  45 | Schema1 |  54 | Schema2 |
|15.5 | enq: TX row lock.. |  45 | Schema1 |  95 | Schema3 |

问题出在哪里，请帮助我。

Answer 1

你在s1.sid=s2.sid上加入--它会加入到它自己的行中。我认为你想使用阻塞sid加入：

select 
    round(s1.seconds_in_wait/60,1) as TIME, s1.event,
    s1.sid as SID_A, s1.username as USER_A, 
    s2.sid as SID_B, s2.username as USER_B  
from 
    v$session  s1, v$session s2   
where
     s1.blocking_session is not null 
     and s1.seconds_in_wait > 1  
     and s1.blocking_session = s2.sid  
order by 
     s1.seconds_in_wait desc;

或者(使用ansi连接)：

select 
    round(s1.seconds_in_wait/60,1) as TIME, s1.event,
    s1.sid as SID_A, s1.username as USER_A, 
    s2.sid as SID_B, s2.username as USER_B  
from 
    v$session  s1
join
    v$session s2   
on
    s1.blocking_session = s2.sid  
where
     s1.blocking_session is not null 
     and s1.seconds_in_wait > 1  
order by 
     s1.seconds_in_wait desc;