Blocker Sessions in Oracle

In this post basically I will try to explain all about session blockers(or locks), how to identify them and finally how to resolve them.(In Production after getting the confirmation from the application development teams.)

For identifying the blockers, all the details are present in the dynamic views

1. v$session
2. v$lock
3. v$process

In the above dynamic views the columns which are useful to identify the details are as below

1. In v$session

- SID – Session Identifier.(This is a sequence number generated from audses$ sequence)
- SERIAL# – Session serial number. Used to identify a session’s objects.( SID & Serial# uniquely identify the session to be killed after the blocker session is identified).
- PADDR – Address of OS level process that owns the session
- USERNAME – Oracle User Name
- COMMAND – Command in progress (last statement parsed). We can find the sql command using the query - SELECT command_name FROM v$sqlcommand WHERE command_type = n;
- LOCKWAIT – Address of the lock the session is waiting for; NULL if none
- STATUS – Status of the session;
        ACTIVE – Session currently working executing the SQL statements.
        INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
        KILLED - Session marked to be killed
        CACHED - Session temporarily cached for use by Oracle*XA
        SNIPED - An inactive session that has exceeded some configured limits.
- SCHEMANAME - Schema User name
- OSUSER - Operating System client user name
- PROCESS - Operating system client process ID
- MACHINE - Operating system machine name
- TERMINAL - Operating system Terminal name
- PROGRAM - Operating system program name
- TYPE - Session Type
- SQL_ID - SQL identifier of the SQL statement that is currently being executed
- MODULE - Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
- ROW_WAIT_OBJ# - Object ID of the table containing the row specified in ROW_WAIT_ROW#
- ROW_WAIT_FILE# - Identifier of the data file containing the row specified in ROW_WAIT_ROW#
- ROW_WAIT_BLOCK# - Identifier for the block containing the row specified in ROW_WAIT_ROW#
- ROW_WAIT_ROW# - Current Row being locked
- LAST_CALL_ET - If the session status is ACTIVE, then the value represents the elapsed time(in seconds) since the session has become active.
                 If the session status is INACTIVE, then the value represents the elapsed time(in seconds) since the session has become inactive.
- BLOCKING_SESSION - Session identifier of the blocking session.
- EVENT - Resource or event for which the session is waiting
- STATE - Wait State
            - WAITING - Session is currently waiting
            - WAITED UNKNOWN TIME - Duration of the last wait is unknown
            - WAITED SHORT TIME - Last wait was less than hundredth of a second
            - WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column

Creating the Blocking Sessions (For Demonstration):

Let us now create the row blockers for demonstration purpose. Consider the HR sample schemas which come with Oracle Installation.

For creating the row level locks, let us have two sessions

Session I – Try to updating Region_ID value of India country to 1 (currently it is 3 as shown below) in HR schema table – COUNTRIES. And please note that after the update statement the COMMIT statement is not executed making the update statement grab the exclusive row lock on table – COUNTRIES

 
SQL> update HR.COUNTRIES set REGION_ID = 1 where COUNTRY_NAME = 'India';

1 row updated.

SQL> --Note that the changes are not COMMITTED--

Session II – Open a new session by connecting to the database using user account SH and try updating the same row(Country India, Region Id value to say 10) in HR.COUNTRIES table.

SQL> show user                                    
USER is "SH"
SQL> 
SQL> update HR.COUNTRIES set REGION_ID=10 where COUNTRY_NAME='India';

--Note that the SQL> prompt is not returned as this session is waiting above HR session update to get committed or rollback--

Now lets check the details on which session is blocking which one and gathered the necessary details by querying the v$session, v$lock & v$process dynamic views.

Lets connect to database as sys dba to check the details about locks executing the below query on v$session dynamic view.

SQL> set linesize 10000
column STATE format a20
column USERNAME format a10
column EVENT format a30
select SID, SERIAL#, USERNAME, STATUS, STATE, EVENT, BLOCKING_SESSION from v$session where USERNAME is not null; 

       SID    SERIAL# USERNAME   STATUS   STATE                 EVENT                         BLOCKING_SESSION
---------- ---------- ---------- -------- -------------------- ------------------------------ ----------------
       249  5487      HR         INACTIVE WAITING               SQL*Net message from client
       262  1889      SH         ACTIVE   WAITING               enq: TX - row lock contention    249
       377  1135      SYS        ACTIVE   WAITED SHORT TIME     SQL*Net message to client

Below are the inference we can make from the above query details::
1. It can be noticed from the above query output that the session with SID – 249 (Update query executed by HR user) is basically blocking the session with SID – 262 (the update query executed by SH user).
2. Note the Event column of SID – 262 (SH User), “enq: TX – row lock contention” mean that the lock currently existing row level lock.
3. SID – 249 is in INACTIVE status and it is basically WAITING for other messages to be received from the client(in our case sql*plus client), perhaps the commit/rollback or other queries.
4. SID – 262 is in ACTIVE status and it is basically WAITING for the SID – 249 (HR User) to commit/rollback the transaction in which he is trying to update the row(Country India, REGION_ID = 1)
5. SID – 377 is the session from SYS login, where we have executed the query to find the details about the locks.
 
Now lets find the query details which is basically leading to row contention(lock). For this we need to look for sql_id column value in v$session and then sql_text value in v$sql dynamic view.
Modified query with the sql_id column included is below (please note that my DB session has disconnected and when tried the row lock creation after connecting to DB, system created different SID’s but the concept wise it is all same)
 
set linesize 10000
column STATE format a20
column USERNAME format a10
column EVENT format a30
select SID, SERIAL#, USERNAME, STATUS, STATE, EVENT, SQL_ID, BLOCKING_SESSION from v$session where USERNAME is not null;SQL> SQL> SQL> SQL> 

       SID    SERIAL# USERNAME     STATUS       STATE                  EVENT                              SQL_ID        BLOCKING_SESSION
---------- ---------- ----------   --------   --------------------   ------------------------------     ------------- ----------------
        26      5       HR         INACTIVE     WAITING                 SQL*Net message from client      50tbf7vd82htx
       254  14391       SYS         ACTIVE       WAITED SHORT TIME      SQL*Net message from client      3tyc5ht9g7ynw
       259   7621       SH         ACTIVE       WAITING                 enq: TX - row lock contention    63r8m52wk2uut        26
   
Now querying the v$sql view for the sql_id will give the details of the query causing the row contention.
 
select DISTINCT sq.sql_id, s.PROCESS "OS_ProcessID", s.SID, s.serial#, s.USERNAME "Executed_By_User", sq.sql_text from v$sql sq 
JOIN v$session s ON (s.sql_id = sq.sql_id) where s.USERNAME is not null;

SQL_ID            OS_ProcessID            SID        SERIAL#           Executed_By_User        SQL_TEXT
------------- ------------------------ ---------- ---------- ------------------------------ ------------------------------
50tbf7vd82htx     3799                     26             5                 HR                 update HR.COUNTRIES set REGION
                                                                                            _ID = 1 where COUNTRY_NAME = '
                                                                                            India'

9xbzgbk9znfkd     6288                    254       14391                 SYS                select DISTINCT sq.sql_id, s.P
                                                                                            ROCESS "OS_ProcessID", s.SID,
                                                                                            s.serial#, s.USERNAME "Execute
                                                                                            d_By_User", sq.sql_text from v
                                                                                            $sql sq JOIN v$session s ON (s
                                                                                            .sql_id = sq.sql_id) where s.U
                                                                                            SERNAME is not null

63r8m52wk2uut     27508                    259           7621                 SH                 update HR.COUNTRIES set REGION
                                                                                            _ID=10 where COUNTRY_NAME='Ind
                                                                                            ia'
 
 
Now that you have identified the queries causing the contention, we need to decide on killing them or committing or rollback based on the application need. Once the development team confirms that we can go ahead and kill the culprit session.  There are two ways we can kill the offensive session 
1. Using the database – ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’;
2. Using the OS level Kill command – KILL –9 <OS_ProcessID>

SQL> !ps -ef | grep '3799\|6288\|27508' | grep -v grep
oracle    3799  3027  0 Jan14 pts/0    00:00:00 sqlplus   as sysdba
oracle    6288  6221  0 Jan14 pts/2    00:00:00 sqlplus                        
oracle   27508  7698  0 07:51 pts/3    00:00:00 sqlplus