CODEWITHSUNDEEP
sqloracle-databaseselectlocking
user3281767
user3281767

Reputation: 1

SQL - lock select check

I created a locks on 3 sessions a database record-TX table.

Next I wrote a query to check the locks on the database Oracle but something is not right:

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;

Result:

| 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 |

Rather it should be something like this:

| 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 |

Where is the problem please help.

Upvotes: 0

Views: 78

Answers (1)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

You join on s1.sid=s2.sid - that will join the row with itself. I think you want to join using the blocking 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;

Or (using ansi joins):

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;

Upvotes: 3

Related Questions