A BLOCKING SESSION

 
QUESTIONS

 
SYMPTOMPS

 
CAUSE

 
TESTCASE

A user and a table for this testcase

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;

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

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

SQL> SELECT * FROM B_ROW;

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

Open 2 more sessions under TESTCASE user

-- Session 1	
set sqlprompt "_user'@'SES_1 > "
SELECT DISTINCT SID FROM V$MYSTAT;

-- Session 2
set sqlprompt "_user'@'SES_2 > "	
SELECT DISTINCT SID FROM V$MYSTAT;


SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_1 > "
TESTCASE@SES_1 > SELECT DISTINCT SID FROM V$MYSTAT;

       SID
----------
        10

SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> set sqlprompt "_user'@'SES_2 > "
TESTCASE@SES_2 > SELECT DISTINCT SID FROM V$MYSTAT;

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

In the first session start a transaction 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

Now try to update the same row in the second session. The session hangs until the first session release its lock by issuing a commit or rollback

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;

By querying the V$SESSION view it’s easy to identify a blocking session. If a session waits for another session, then it has TX – row lock contention wait event and BLOCKING_SESSION column has 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

Or you can use V$SESSION_BLOCKERS to find the same data (11g and above)

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

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

To find out what statement is waiting to be executed

SELECT SID, SERIAL#, PREV_SQL_ID, SQL_ID, SQL_CHILD_NUMBER, STATUS FROM V$SESSION WHERE 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 SQL_ID IN ('fnak0a1mdxbmr','5zprqh5ydng78');

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
fnak0a1mdxbmr SELECT * FROM B_ROW
5zprqh5ydng78 UPDATE B_ROW SET STRING='001' WHERE DIGIT=1

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 DBMS_ROWID and V$SESSION it’s easy to find 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  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

To find out all required details about the lock

SET LINES 300 
SET PAGES 999
COL BLOCKING FOR A20 
COL WAITING FOR A20
COL ON_OBJECT FOR A15
COL WAITING_SQLID FOR A15

SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || SES1.USERNAME || ')' "BLOCKING",
       SES2.SID || ',' || SES2.SERIAL# || '(' || SES2.USERNAME || ')' "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 lock the session should complete its work or it can be terminated by ALTER SYSTEM

TESTCASE@SES_1 > COMMIT;

Commit complete.

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

alter system kill session 'sid,serial' immediate;

Note : if the session has done lots of work when it’s been terminated all unsaved work will be rollbacked. Rolling back could take lots of time.

 
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.