oracle-database

Find Row Lock using select * from V$SESSION where status='ACTIVE'

I'm unable to update a particular row (Using SQL Developer directly) in my database and I'm trying to figure out why. The update just keeps going infinitely. I'm guessing a query has a lock on that particular row, but I'm unsure how to find out. Any ideas? When do a select * from V$SESSION where status='ACTIVE' I don't see any table name or query info.

Blocking Session Query:

  select blocking_instance,blocking_session from v$session where blocking_instance is not null

Blocking Session Query Results:

blocking_instance  blocking_session
1                  104
1                  104
1                  104
1                  104
1                  104
1                  144
1                  104

SELECT * FROM dba_blockers

holding_session
55
104
145
92

SELECT * FROM dba_waiters

waiting_session  holding_session  lock_type     mode_held  mode_requested lock_id1  lock_id2
144          55           Transaction   Exclusive  Exclusive      262163    1802
104          55           DML           Row-X (SX) S/Row-X (SSX)  24034     0
96           55           DML           Row-X (SX) Row-X (SX)     24034     0
94           92           Transaction   None       Exclusive      589835    1904
92           92           Transaction   None       Exclusive      589835    1904
104          104          DML           Row-X (SX) S/Row-X (SSX)  24034     0
96           104          DML           Row-X (SX) Row-X (SX)     24034     0
94           145          Transaction   Exclusive  Exclusive      589835    1904
92           145          Transaction   Exclusive  Exclusive      589835    1904
Solution

  • Assuming that you have session 1 that holds the lock, session 2 that is waiting on the lock, and session 3 where you can run queries to diagnose the problem,

    SELECT *
  FROM dba_blockers

SELECT *
  FROM dba_waiters

    will show you which sessions are blocking other sessions and which sessions are blocked by other sessions along with the type of lock. That will allow you to determine that session 1 is blocking session 2.

    In v$session, the blocking_session for session 2 would also indicate that it was blocked by session 1.