Finding Locks in a oracle database

Finding Locks in a database (Stand-alone/RAC)

Select * from v$lock;
For RAC databases the GV$ and GV_$ views will show information on the inst_id


Set lines 136
Set pages 200
Select * from gv_$lock; *amended with ‘where type=’TX’;
Which session is the blocker and which sessions are waiting to get the lock?

This SELECT will return no rows if there are no waiters and blockers. You can
never find situations when there is only blocker(s) or waiter(s). One session
can block many others.

column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
SCOTT 9 TX Exclusive None 262154 117
SCOTT 11 TX None Exclusive 262154 117

Session 9 is blocking(LMODE=Exclusive)

Session 11 is waiting(REQUEST=Exclusive)

The meaning of ID1 and ID2 depends on the lock TYPE.

• We can see situations where a session is both a Blocker and a Waiter.

• If there are only two sessions and both are Blockers and Waiters then we
got a deadlock situation (which Oracle will solve automatically).

• A fast way to check blocking/waiting situations is to run:

SELECT * FROM v$lock WHERE block > 0 OR request > 0;