Tag Archives: deadlock

Check the Lock, blocking and deadlock in Oracle database

2 Replies

Check locking and deadlock in Oracle database

Quick way to find out the blocking at instance level:

SELECT blocking_session, sid, serial# , wait_class, event 
FROM v$session 
WHERE blocking_session IS NOT NULL;

OR 

SELECT s1.sid || ' is blocking ' || s2.sid AS blocking_chain
FROM v$session s1
JOIN v$session s2 ON s1.sid = s2.blocking_session;

How long a blocking session is blocking by other sessions:

SELECT sid, serial#, username, blocking_session, wait_class, event, seconds_in_wait
FROM v$session 
WHERE blocking_session IS NOT NULL;

Check the transaction state is active 

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       t.start_time,
       t.status
FROM   gv$transaction t,
       gv$session s
WHERE  t.addr = s.taddr;

Check the blocking with SQL Statement

SELECT s.sid, s.serial#, s.username, s.blocking_session,
       q.sql_text
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.blocking_session IS NOT NULL;

Check the status of session is active or inactive

SELECT sid,
       serial#,
       username,
       status,
       blocking_session,
       event,
       wait_class
FROM v$session
WHERE blocking_session IS NOT NULL;

OR 

SELECT s.sid,
       s.serial#,
       s.username,
       s.status AS blocker_status,
       b.sid AS blocked_sid,
       b.status AS blocked_status
FROM v$session b
JOIN v$session s
     ON b.blocking_session = s.sid;

Inactive session blocker:

SELECT sid,
       serial#,
       username,
       status,
       blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL
AND status = 'INACTIVE';

Kill the inactive session with following command

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;


-- Check with
SELECT sid, serial#, status, blocking_session, username
FROM v$session
WHERE blocking_session IS NOT NULL
AND status = 'INACTIVE';

Note: If you got the following message : session-marked-for-kill. Follow the link to check the transaction status:
ORA-00031 Session Marked For Kill

If multiple session is caused blocking then need to kill multiple session use the following scripts:

Script to kill multiple inactive sessions 

SET PAGES 999 LINES 200
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
FROM v$session
WHERE blocking_session IS NOT NULL
AND status='INACTIVE';

For RAC and 12c Pluggable Database Go to link: 12c Pluggable and RAC Locking