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
