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