DBA does come across locking session and blocks in their databases on a work day. It is very common to identify the locking session and then take appropriate action which can be to clear the holding session. The holding session may be caused by application, user forms that has performed some changes to the data and not yet saved, run away process.
You can use multiple v$ tables to identify the culprit session - v$session, v$lock, v$locked_object, v$active_session_history to name a few. Below is one of the query I use to identify the blocking session:
set echo off
set pagesize 80
set underline =
set linesize 220
COLUMN "Holding_sid" FORMAT 99999 heading "Holding_sid " trunc
COLUMN "Holding_serial#" FORMAT 999999 heading "Holding_serial# " Trunc
COLUMN "Holding_Module" FORMAT A50 heading "Holding_Module " Trunc
COLUMN "Sessions_blocked" FORMAT 9999 heading "Sessions_blocked " trunc
COLUMN "Holding_sql_id" FORMAT A30 heading "Holding_sql_id " Trunc
COLUMN "Holding_class" FORMAT A30 heading "Holding_class " Trunc
COLUMN "Holding_event" FORMAT A30 heading "Holding_event " trunc
COLUMN "Holding_secs" FORMAT 9999999 heading "Holding_secs" Trunc
COLUMN "Kill_Command" FORMAT A70 heading "Kill_Command"
COLUMN "Waiting_sid" FORMAT 99999 heading "Waiting_sid " trunc
COLUMN "Waiting_serial#" FORMAT 999999 heading "Waiting_serial# " Trunc
COLUMN "Waiting_sql_id" FORMAT A30 heading "Waiting_sql_id " Trunc
COLUMN "Waiting_event" FORMAT A30 heading "Waiting_event " trunc
COLUMN "Waiting_secs" FORMAT 9999999 heading "Waiting_secs" Trunc
select dbs.sid Holding_sid, dbs.serial# Holding_serial#,
dbs.module Holding_Module,
(select count(sid) from v$session where blocking_session = dbs.sid) Sessions_blocked,
dbs.sql_id Holding_sql_id, dbs.wait_class Holding_class,
dbs.event Holding_event, dbs.seconds_in_wait Holding_secs,
'alter system kill session '|| ''''|| dbs.sid|| ','|| dbs.serial#|| ''' immediate;' "Kill_Command",
dws.sid Waiting_sid, dws.serial# Waiting_serial#,
dws.sql_id waiting_sql_id, dws.event Waiting_event,
dws.seconds_in_wait Waiting_secs
from v$session dbs, v$session dws
where dws.blocking_session = dbs.sid
order by dbs.sid, dws.seconds_in_wait desc;
Another query for sessions waiting for 'enq: TX - row lock contention' is
set echo off
set pagesize 80
set underline =
COLUMN "waiting Sid" FORMAT 9999 heading "Waiter Sid " trunc
COLUMN "waiting SQL" FORMAT A70 heading "Waiter SQL" Trunc
COLUMN "locked object" FORMAT 99999999999999999999 heading "Locked Object"
COLUMN "Filler" Heading " " FORMAT A50
COLUMN "blocking sid" FORMAT 9999 heading "Blocking Sid"
COLUMN "SQL from blocking session" FORMAT A70 heading "Blocking SQL" trunc
" "
select distinct
waiter.sid "waiting sid"
, w_sql.sql_text "waiting SQL"
, waiter.ROW_WAIT_OBJ# "locked object"
,' ' Filler
, waiter.BLOCKING_SESSION "blocking sid"
, b_sql.sql_text "SQL from blocking session"
from v$session waiter, v$active_session_history blocker, v$sql b_sql, v$sql w_sql
where waiter.event='enq: TX - row lock contention'
and waiter.sql_id=w_sql.sql_id
and waiter.blocking_session=blocker.session_id
and b_sql.sql_id=blocker.sql_id
and blocker.CURRENT_OBJ#=waiter.ROW_WAIT_OBJ#
and blocker.CURRENT_FILE#= waiter.ROW_WAIT_FILE#
and blocker.CURRENT_BLOCK#= waiter.ROW_WAIT_BLOCK#;
As the SQL from the active blocking sessions may have happened too quickly, it may not have been sampled and therefore may not be present in V$ACTIVE_SESSION_HISTORY.In this case, the above script will not return output. The following sql will return the CURRENT sql being run by the blocking session.
set echo off
set pagesize 80
set underline =
COLUMN "Waiting Sid" FORMAT 9999 heading "Waiter Sid " trunc
COLUMN "SQL from Waiting Session" FORMAT A70 heading "Waiter SQL" Trunc
COLUMN "locked object" FORMAT 99999999999999999999 heading "Locked Object"
COLUMN "Blocker Event" FORMAT A80
COLUMN "Blocking sid" FORMAT 9999 heading "Blocking Sid"
COLUMN "SQL from blocking session" FORMAT A70 heading "Blocking SQL" trunc
SELECT DISTINCT waiter.sid "Waiting sid" ,
w_sql.sql_text "SQL from Waiting Session" ,
blocker.event "Blocker Event",
blocker.sid "Blocking sid" ,
b_sql.sql_text "SQL from blocking session"
FROM v$session waiter,
v$session blocker,
v$sql w_sql,
v$sql b_sql
WHERE waiter.event ='enq: TX - row lock contention'
AND waiter.blocking_session=blocker.sid
AND w_sql.sql_id =waiter.sql_id
AND b_sql.sql_id =NVL(blocker.sql_id,blocker.prev_sql_id);
Reference:
Script to Potentially Detect the SQL for Blocking Session for TX Lock (Doc ID 729727.1)
