Terminating Sessions: Find Blocking Locks in Oracle

Oracle 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 until the first one has done its work. Blocking locks mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.

When a DML is executed (update/delete/insert,merge, and select …. for update) oracle obtains 2 locks on the table. Row level Lock (TX) – This obtains a lock on the particular row being modified and any other transaction attempting to modify the same row gets blocked, till the one already owning it finishes. Table Level Lock (TM) – When Row lock (TX) is obtained an additional Table lock is also obtained to prevent any DDL operations to occur while a DML is in progress. Example: to avoid truncate and alter operation during table modification.

User can modify different rows of the table at the same time but cannot modify the same row at the same time. During row revel lock oracle acquire lock mode 3. Parallel DML operations and serial insert using direct load operations take exclusive table locks with lock mode 6. Below lock mode 6 lock the whole table and during this lock user even can’t modify the rows. This will result in library cache lock. So lock mode 3 is common but avoid using hints during insert because it will lock the whole table.

6   Exclusive (X)              Lock table in exclusive mode
create index    — duration and timing depend on options used
insert /*+ append */

From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You’ll often have to identify these sessions in order to improve your application to avoid as many blocking locks as possible.

You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

Oracle provide views like, DBA_BLOCKERS and V$LOCK using which we can easily find the blocking locks. Here, we will try to find blocking locks using V$LOCK view which is faster to query and makes it easy to identify the blocking session.

SQL> select * from v$lock ;

Here we are interested in the BLOCK column. If a session holds a lock that’s blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it’s unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 542 is blocking SID 419. SID 542 corresponds to Session 1 in our example, and SID 419 is our blocked Session 2. To avoid having to stare at the table and cross-compare ID1’s and ID2’s, put this in a query:

Query to Find Blocking Locks in Oracle

 SQL> select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
 from gv$lock l1, gv$lock l2
 where l1.block =1 and l2.request > 0and l1.id1=l2.id1
 and l1.id2=l2.id2;

Oracle Blocking Sessions Script to check Detailed Information

SQL> SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
     FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND
     s1.inst_id=l1.inst_id AND
     s2.sid=l2.sid AND
     s2.inst_id=l2.inst_id AND
     l1.BLOCK=1 AND
    l2.request > 0 AND
    l1.id1 = l2.id1 AND
    l2.id2 = l2.id2 ;

Script to check blocking sessions using v$session

set lines 1234 pages 9999
 col inst_id for a10
 col serial# for a10
 col machine for a30
 col username for a10
 col event for a20
 col blocking_session for 999999
 col blocking_instance for 999999
 col status for a10
 col INST_ID for 9999
 col SERIAL# for 999999
 SQL> select inst_id,sid,serial#, machine, username, event, blocking_session, blocking_instance, status, sql_id from gv$session where status ='ACTIVE'and username is not null; 

Find SQL_ID from SID in Oracle

SQL> select sql_id from v$session where sid=4120;

Check SQL Text from V$SQL in Oracle

SQL> select sql_fulltext from v$sql where sql_id ='xxxxx';

Kill / Terminate Blocking Session in Oracle
Be very careful when identifying the session to be killed. If you kill Oracle blocking session belonging to a background process you will cause an instance crash.

There are a number of ways to kill blocking sessions both from Oracle sql prompt and externally.

Identify the Session to be Killed and use below command to kill session.

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

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' IMMEDIATE;

The DISCONNECT SESSION command kills the dedicated server process, which is equivalent to killing the server process from the operating system

kill -9 spid

 Oracle Table Lock

How to Find and Remove Table Lock in Oracle

If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you’ll never have to wait forever.

These queries should help you to identify the cause of your blocking sessions!