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. It 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 ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- -------------------------------------
AF8E2C4C AF9E2C50 419 TX 141028 15289 0 6 675 0
ADDF7EC8 ADDF8EE0 542 TM 77529 0 3 0 687 0
ADDF7F74 ADDF7F8C 419 TM 77529 0 3 0 675 0
ADEBEA20 ADEBEB4C 542 TX 141028 152899 6 0 687 1
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 sessions using v$lock
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 > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
To Get Detailed information on blocking locks
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 ;
Finding blocking sessions with 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;
Finding SQL_ID from SID using v$session
SQL> select sql_id from v$session where sid=4120;
SQL_ID
-------------
SQL> select sql_fulltext from v$sql where sql_id ='xxxxx';
Killing Oracle Sessions
Be very careful when identifying the session to be killed. If you kill a 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
ALTER SYSTEM KILL SESSION
ALTER SYSTEM DISCONNECT SESSION
The Windows Approach
The UNIX Approach
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
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
To identifying blocked objects
The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks - check this site for a complete list: http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198
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.
The following queries shows you all the TM locks:
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
SID
ID1
92
20127
51
20127
The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:
SELECT object_name FROM dba_objects WHERE object_id=20127
These queries should help you to identify the cause of your blocking sessions!
