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
Thank you !!!
its very useful for core DBA associates
could you please post ORACLE Exadata Technology
Dear Rajashekhar,
Hope your doing well.
Today I faced some pmon system blocking session issue in live DB,It contain 762 ID.We know how to clear the user sessions.But I want to know how to clear the system blocking sessions.If you have any queries or doc.Could you please clarify me on this.
Regards,
Purushotham.S
Oracle Database Admin
Hyd
Hi Purushotham –
Thanks for the question.
May i know how did you find the system blocking session for PMON? Is there any way/query to find system blocking for PMON?
We know only to find Oracle User Session blocking.
Thank you,
Rajasekhar
Hi,
Your blog is very much helpful. Kindly work on apache airflow. We are looking forward for this topic.
Thanks