- 10 Jul, 2021
- 0 Comments
- 3 Mins Read
Blocking in Oracle Database
Blocking in Oracle Database
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@upgrade ~]$ sqlplus user1/user1;
SQL> create table test (name varchar2(1));
Table created.
SQL> insert into test values ('ankush');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test where name='ankush' for update;
name
-
ankush
SQL>
SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
50
SQL>
Session 2:
In second session try to update the rows which you have selected above.
[oracle@upgrade ~]$ sqlplus user1/user1;
SQL> select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
60
SQL>
SQL> update test set name='x' where name='ankush';
-- hanging here --
..
..
2. Finding Out Who’s Holding name 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
----------------------------------------------------------------------------------
[email protected] ( SID=50 ) is blocking [email protected] ( SID=60 )
SQL> select sid, serial#, username,status from v$session where sid in (50,60);
SID SERIAL# USERNAME STATUS
---------- ---------- -------------------- --------
60 77 user1 ACTIVE <-- waiter
50 13 user1 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: 50 589826 1571 6 0 TX
Waiter: 60 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
---------------- ---------- ---------- ---------------
50 60 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
----------------------------------------------------------
50 is blocking 60
SQL>
3. Solution
Inform to the holder to commit/rollback.
— OR —-
kill the holder session if you have appropriate permision
alter system kill session 'SID,SERIAL#';(For Single instance) SQL> alter system kill session '50,13'; System altered. SQL> -- After killing holder session, waiter session got completed SQL> update test set name='x' where name='ankush'; 1 row updated. SQL>