Print Friendly, PDF & Email
What is a blocking session in the Oracle database? How to identify a blocked and a blocking session in the Oracle database?

QUESTIONS
What is a blocking session?
What is a waiting session?
How to find a blocking session?
Why my transaction hangs when I update a row?
Who blocked my transaction?

SYMPTOMS
Users complain about an application completely hangs.
A user complains about his update or delete statement is being executed forever or his update/delete statement sometimes takes lots of time to complete.

CAUSE
A session has modified a row without ending its transaction. Then another session tries to modify the same row, but it has to wait until the first session has done its work. In this situation the first session is the BLOCKING SESSION and the second session is the WAITING SESSION.

When a session does something with a table row (eg UPDATE or DELETE) a row is locked by that session, therefore no one can modify that row until a lock has been removed through issuing the ROLLBACK or the COMMIT statement by the session holding the lock.

QUERY
Here is the query to identifiy a blocking and a blocked session in the Oracle database

------------------------------------------------------------
-- Victor Khalturin
-- Version 09:51 27-May-2021 (002)
--
-- Display both a blocking and a blocked sessions with rowid of a blocked row.
--
------------------------------------------------------------
SET LINES 300 
SET PAGES 999
COL BLOCKING FOR A20 
COL WAITING FOR A20
COL ON_OBJECT FOR A30
COL WAITING_SQLID FOR A15

SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || NVL(SES1.USERNAME,'BACKGROUND') || ')' "BLOCKING",
       SES2.SID || ',' || SES2.SERIAL# || '(' || NVL(SES2.USERNAME,'BACKGROUND') || ')' "WAITING", 
	   SES2.SECONDS_IN_WAIT,
	   DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
	   DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#, 
	   SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
	   SES2.SQL_ID "WAITING_SQLID"
FROM   V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO 
WHERE  SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND 
       SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;

TESTCASE
Create a user for the testcase

-- Create user and provide necessary grants

CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT SELECT ON V_$MYSTAT TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON DBA_OBJECTS TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;

Connect as the TESTCASE user and create the following table

-- Connect as the TESTCASE user 

SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> SHO USER
USER is "TESTCASE"

-- Create the requested table 

CREATE TABLE B_ROW(DIGIT NUMBER, STRING VARCHAR(10));
INSERT INTO B_ROW VALUES (1, 'ONE');
COMMIT;

-- List content of the table 

SQL> SELECT * FROM B_ROW;

     DIGIT STRING
---------- ----------
         1 ONE

 
Establish 2 new sessions by the TESTCASE user from 2 diffrent terminals

NOTE

If you are using the SQL PLUS you can set the SQL PROMPT for each session so you can easily distinguish between them while performing the testcase.

-- To set a sqlprompt 

set sqlprompt "_user'@'SES_id > "

-- To get a session ID 

SELECT DISTINCT SID FROM V$MYSTAT;

For instance

-- Session 1	

SQL> CONNECT TESTCASE/TESTCASE
Connected.

-- Set Sql Prompt 

SQL> set sqlprompt "_user'@'SES_1 > "

-- Get session SID

TESTCASE@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
        10
-- Session 2

SQL> CONNECT TESTCASE/TESTCASE
Connected.

-- Set Sql Prompt 

SQL> set sqlprompt "_user'@'SES_2 > "

-- Get session SID

TESTCASE@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
       625

 
When sessions are established start a transaction in the 1st session without committing it

TESTCASE@SES_1 > SELECT * FROM B_ROW;

     DIGIT STRING
---------- ----------
         1 ONE

TESTCASE@SES_1 > UPDATE B_ROW SET STRING='one' WHERE DIGIT=1;

1 row updated.

TESTCASE@SES_1 > SELECT * FROM B_ROW;

     DIGIT STRING
---------- ----------
         1 one

-- No COMMIT in this session

 
Next try to update the same row in the 2nd session. The 2nd session will hang until the 1st session has released its lock through issuing the COMMIT or the ROLLBACK statement.

TESTCASE@SES_2 > SELECT * FROM B_ROW;

     DIGIT STRING
---------- ----------
         1 ONE

TESTCASE@SES_2 > SET TIMING ON
TESTCASE@SES_2 > UPDATE B_ROW SET STRING='001' WHERE DIGIT=1;

-- Now the session has hung as the same row is locked by the first session

 
By following the former steps you have created situation when one session is blocking another session. By querying the V$SESSION view it’s easy to identify a BLOCKING SESSION. If a session is waiting because of row lock made by another session, then it has TX – row lock contention wait event and the BLOCKING_SESSION column of such session in wait contains a SID of a blocking session.

SET LINES 300 
SET PAGES 999
COL SID FOR 99999
COL SERIAL# FOR 99999
COL EVENT FOR A30

SELECT SID, 
       SERIAL#, 
       SQL_ID, 
       EVENT, 
       SECONDS_IN_WAIT, 
       BLOCKING_SESSION 
FROM 
       V$SESSION 
WHERE 
       BLOCKING_SESSION IS NOT NULL;
   SID SERIAL# SQL_ID        EVENT                          SECONDS_IN_WAIT BLOCKING_SESSION
------ ------- ------------- ------------------------------ --------------- ----------------
   625   10264 5zprqh5ydng78 enq: TX - row lock contention               43               10

In this testcase the session with SID 10 is blocking the session with SID 625 for 43 seconds.

 
Another way to find a blocking session is to query V$SESSION_BLOCKERS view (11g and above) that provides the same data about blockers

COL WAIT_EVENT_TEXT FOR A30
SELECT BLOCKER_SID,
       BLOCKER_SESS_SERIAL#,
       SID,
       SESS_SERIAL#,
       WAIT_EVENT_TEXT 
FROM 
      V$SESSION_BLOCKERS;
BLOCKER_SID BLOCKER_SESS_SERIAL#    SID SESS_SERIAL# WAIT_EVENT_TEXT
----------- -------------------- ------ ------------ ------------------------------
         10                12498    625        10264 enq: TX - row lock contention

To find out what statements are involved in the blocking session situation

SELECT SID, 
       SERIAL#, 
       PREV_SQL_ID, 
       SQL_ID, 
       SQL_CHILD_NUMBER, 
       STATUS 
FROM 
       V$SESSION 
WHERE 
       -- Blocking and Blocked SID
       SID IN (10, 625);
  SID SERIAL# PREV_SQL_ID   SQL_ID        SQL_CHILD_NUMBER STATUS
------ ------- ------------- ------------- ---------------- --------
    10   12498 fnak0a1mdxbmr                                INACTIVE
   625   10264 fnak0a1mdxbmr 5zprqh5ydng78                0 ACTIVE
SET LONG 200000

SELECT SQL_ID, 
       SQL_FULLTEXT 
FROM 
       V$SQL 
WHERE
      -- PREV_SQL_ID for blocker and SQL_ID for blocked
      SQL_ID IN ('fnak0a1mdxbmr','5zprqh5ydng78');
SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
fnak0a1mdxbmr SELECT * FROM B_ROW
5zprqh5ydng78 UPDATE B_ROW SET STRING='001' WHERE DIGIT=1
NOTE

For the blocking session, if a transaction issued several statements you will see the latest one. It may or may not contain the blocking statement.

 
With help of DBMS_ROWID function and V$SESSION view it’s easy to find out the locked row for the waiting session

COL OBJECT_NAME FOR A30 
COL "ROWID" FOR A20

SELECT DO.OBJECT_NAME, 
       DBMS_ROWID.ROWID_CREATE(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) "ROWID"
FROM   
       V$SESSION SES, DBA_OBJECTS DO 
WHERE
      -- Blocked session SID  
      SES.ROW_WAIT_OBJ#=DO.OBJECT_ID AND SID=625;

OBJECT_NAME                    ROWID
------------------------------ --------------------
B_ROW                          AAAS+2AAHAAAACFAAA
SELECT * FROM TESTCASE.B_ROW WHERE ROWID = 'AAAS+2AAHAAAACFAAA';

     DIGIT STRING
---------- ----------
         1 ONE

 
The next query summarize all former queries into one

------------------------------------------------------------
-- Victor Khalturin
-- Version 09:51 27-May-2021 (002)
--
-- Display both a blocking and a blocked sessions with rowid of a blocked row.
--
------------------------------------------------------------
SET LINES 300 
SET PAGES 999
COL BLOCKING FOR A20 
COL WAITING FOR A20
COL ON_OBJECT FOR A30
COL WAITING_SQLID FOR A15

SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || NVL(SES1.USERNAME,'BACKGROUND') || ')' "BLOCKING",
       SES2.SID || ',' || SES2.SERIAL# || '(' || NVL(SES2.USERNAME,'BACKGROUND') || ')' "WAITING", 
	   SES2.SECONDS_IN_WAIT,
	   DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
	   DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#, 
	   SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "LOCKED_ROWID",
	   SES2.SQL_ID "WAITING_SQLID"
FROM   V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO 
WHERE  SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND 
       SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;
BLOCKING             WAITING              SECONDS_IN_WAIT ON_OBJECT       LOCKED_ROWID       WAITING_SQLID
-------------------- -------------------- --------------- --------------- ------------------ ---------------
10,12498(TESTCASE)   625,10264(TESTCASE)              43 TESTCASE.B_ROW  AAAS+2AAHAAAACFAAA 5zprqh5ydng78

 
To release the row lock the blocking session should complete its work.

-- The first session issue COMMIT

TESTCASE@SES_1 > COMMIT;

Commit complete.

Right after the blocking session (first session) has completed its work the second session proceed further.

TESTCASE@SES_2 > SET TIMING ON
TESTCASE@SES_2 > UPDATE B_ROW SET STRING='001' WHERE DIGIT=1;

1 row updated.

Elapsed: 00:00:43.44

It could happen that the blocking session is unable to complete its work on its own. For instance, a user made a set of updates through an application without saving his/her changes and went home as his/her working day is over.

Sometime later another user wants to make another set of updates in the application but his/her updates are hanging or performing too long. A user starts complaining as he/she can’t make urgent updates through the application.

You as administrator identified that the reason for the problem is a session of the user who went home. You see that that session is blocking others.

In this situation, the only possible way to resolve the issue is to terminate the session of the user who went home. It can be done through issuing the ALTER SYSTEM KILL SESSION … command

alter system kill session 'sid,serial' immediate;
NOTE

All unsaved changes of a terminated session roll back automatically. If a session did lots of work before it’s been terminated then rolling back could take a long time to complete.

RELATED
Mostly it’s impossible to know which statement (sql_id) is holding a lock. If you are lucky it could be a value of prev_sql_id column in v$session of a blocking session. Also, you can try to find a blocking sql_id in the library cache v$sql view by querying the table name and a dml keyword (update/delete) but if the cache is flushed nothing is to be there.

This discussion gives more details about a locking sql_id.

 
 

Version  : 12:36 12.01.2018
Modified : 12:11 27-May-2021
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0