Blocking Sessions

How to find Blocking Sessions

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data.

This will block the second session until the first session has done its work.

1. Simulation
2. Finding Out Who’s Holding a Blocking Lock
3. Solution


1. Simulation

Session 1:

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> create table t (a varchar2(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where a='z' for update;

A
-
z

SQL>
SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46

SQL>

Session 2:

In second session try to update the rows which you have selected above. 

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
39

SQL>
SQL> update t set a='x' where a='z';

-- hanging here --
..
..


2. Finding Out Who’s Holding a Blocking Lock

sqlplus / as sysdba

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;
	
select sid, serial#, username,status from v$session where sid in ('holder','waiter');

-- OR --

select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;

-- OR --

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;

-- OR --
   
SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
   
-- OR --

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';
SELECT object_name FROM dba_objects WHERE object_id='&object_id_from_above_output';
select sid,type,lmode,request,ctime,block from v$lock;
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;

Output:

SQL> SELECT s1.username || '@' || s1.machine
  2      || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
  3    4    5      WHERE s1.sid=l1.sid AND s2.sid=l2.sid
  6      AND l1.BLOCK=1 AND l2.request > 0
  7      AND l1.id1 = l2.id1
  8      AND l1.id2 = l2.id2;

BLOCKING_STATUS
----------------------------------------------------------------------------------
SH@rac2.rajasekhar.com ( SID=46 )  is blocking SH@rac2.rajasekhar.com ( SID=39 )

SQL> select sid, serial#, username,status from v$session where sid in (46,39);

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        39         77 SH                   ACTIVE <-- waiter
        46         13 SH                   INACTIVE <-- Holder

SQL>


SQL>

-- OR --

SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;  2    3    4    5    6

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 46                                           589826       1571          6          0 TX
Waiter: 39                                           589826       1571          0          6 TX

SQL>

-- OR --

SQL> SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   gv$session s
WHERE
   blocking_session IS NOT NULL;  2    3    4    5    6    7    8    9

BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------
              46         39         77            1626

SQL>

-- OR --

SQL> SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;  2    3    4    5    6    7    8    9

BLOCKING_SESSIONS
----------------------------------------------------------
46 is blocking 39

SQL>


3. Solution


Inform to the holder to commit/rollback.

--- OR ----

kill the holder session, if it is ok

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,13';

System altered.

SQL>

-- After killing holder session, waiter session got completed

SQL> update t set a='x' where a='z';

1 row updated.

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

Leave a Reply

Your email address will not be published. Required fields are marked *