Blocking Sessions:
Applies to: Oracle
9i/10g/11g :
Blocking session occurs when one
session acquired an exclusive lock on an object and doesn't
release it, another session (one or more) want to modify the
same data.First session will block the second until it completes its job
Finding blocking
sessions:
Using v$session:
SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
v$session s
WHERE
blocking_session IS NOT NULL
Using v$lock:
select * from v$lock where block=1;
select
count(*) from gv$lock where block=1;
select sid from v$lock where block=1;
Which Session is
blocking?
select
(select username from v$session where
sid=a.sid) blocker,
a.sid,' is blocking ',(select username from
v$session where sid=b.sid) blockee,b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
Finding the query of
the sessions:
SELECT a.sql_text, b.sql_hash_value FROM v$sqltext a,v$session b
WHERE a.address = b.sql_address
AND a.hash_value =
b.sql_hash_value
AND b.sid = &1
ORDER BY a.piece;
Complete Details of
blocking sessions:
select distinct
a.sid "waiting sid"
, d.sql_text "waiting SQL"
, a.ROW_WAIT_OBJ# "locked object"
, a.BLOCKING_SESSION "blocking
sid"
, c.sql_text "SQL from blocking
session"
from v$session a, v$active_session_history
b, v$sql c, v$sql d
where a.event='enq: TX - row lock
contention'
and a.sql_id=d.sql_id
and a.blocking_session=b.session_id
and c.sql_id=b.sql_id
and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#
and b.CURRENT_FILE#= a.ROW_WAIT_FILE#
and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#
Find the Unix process
id from SID:
select spid from v$process where background is null
and addr in (select paddr from v$session where sid=&session_id);
Find SID from SPID:
(Not very much required here)
select s.username, s.status, s.sid,
s.serial#,
p.spid,
s.machine, s.process, s.lockwait
from
v$session s, v$process p
where s.process = '&unix_pid'
and
s.paddr = p.addr;
Blocking Session has to be released by
taking concurrence with application team:
How to release a
blocking Session:
Ge the SID details:
select sid,SERIAL#,status,username from
v$session where sid=<Blocking Session>;
select SID,MACHINE,TERMINAL,PROGRAM,MODULE
from v$session where sid=<Blocking Session>;
Disconnecting the
Session:
alter system disconnect session
'<SID>,<Serial#>' IMMEDIATE;
Kill the Server
Process:
kill -9 <Unix Process Id from SID>
Corrective Action:
For a blocking session only
two corrective actions:
1. Disconnect the
blocking session
2. Wait
for completing the blocking session
Preventive Action:
Application has to
be designed/corrected as no two or more sessions required the same
data at the same time to be modified.
