Re: Row Locking

Graeme Farmer wrote:
>
> Given that I know the row id of a row that is being locked, is there any way
> to find the user who initiated the lock.
>
> I had a look at the V$LOCK and V$LOCKED_OBJECT views and the best I can
> narrow it down to is the object (table) level.
>
> I can see no entries in either of these two views that would relate to a row
> ID.

If you know the rowid, it should be easy to find the person locking it. The catch is that you need two sessions to do it.

In session A, try to lock the row (select ... for update). The session will hang waiting for the lock.

In session B, run a script that looks up the waiting session's entry in v$session_wait, and takes note of the values for P2 and P3 of the enqueue wait.

Using those numbers look up the sid in v$lock where block = 1, and ID1 = the value from P2 above and ID2 = the value from p3 above, like so:

In one session:

SQL> select sid from v$session where audsid = userenv('sessionid');

       SID

       127

SQL> select * from emp where empid = 10 for update;

(the session hangs waiting for the locked row)

In another session:

SQL> select l.sid from v$lock l, v$session_wait sw where sw.sid = 127 and l.id1 = sw.p2 and l.id2 = sw.p3 and block = 1;

       SID

       131

There's your blocker!
--
Jeremiah Received on Mon Feb 14 2000 - 12:10:20 CST