Users complain about an application completely hanging. You are wondering if there are any blocked sessions in your Oracle database.
QUESTIONS

What is a blocking session?
What is a waiting session?
How to find a blocking session?
Why does my transaction freezes when I commit an updated row?
Who blocked my transaction or session?

RELATED

DEMO : ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE

SYMPTOMS

Users complain about an application completely hanging or
A user complains about an application freezing when saving performed changes or
A user complains about committing changes for a table taking a lot of time to complete.

CAUSE

A session updated/deleted a row in a table without issuing the COMMIT or the ROLLBACK statement. This means a transaction started but has not been completed yet. As the result, the session is holding a lock on the modified row. No other session can modify the same row until the lock is released.

Later, another session wants to update/delete the same row in the same table as the first session did. But it freezes and It won’t proceed with any work until the row lock is released by the first session.

In this example, the first session is the BLOCKING SESSION and the second session is the WAITING SESSION.

Blocking Session

Automatic Locks in DML Operations

Row Locks (TX)
A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

DEMO

This is the demo of creating both a blocking and a waiting session.

Create a user for the DEMO

-- Create user and provide necessary grants

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

Connect as the DEMO user and create the following table

-- Connect as the DEMO user 

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


-- Create a table for the demo

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 DEMO user from 2 different terminals

NOTE

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

-- To set a SQL Prompt 

set sqlprompt "_user'@'SES_id > "

-- To get a session ID 

SELECT DISTINCT SID FROM V$MYSTAT;

For instance

---------------
-- Session 1
---------------

SQL> CONNECT DEMO/DEMO
Connected.

-- Set the SQL Prompt 

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

-- Get a session SID

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

       SID
----------
        17


---------------
-- Session 2
---------------

SQL> CONNECT DEMO/DEMO
Connected.

-- Set the SQL Prompt 

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

-- Get a session SID

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

       SID
----------
        55

Here the first session has the SID 17 and the second session has the SID 55.

Start a transaction by the 1st session

DEMO@SES_1 > SELECT * FROM B_ROW;

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

-- The UPDATE statement starts new transaction

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

1 row updated.

DEMO@SES_1 > SELECT * FROM B_ROW;

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

-- The COMMIT has not been issued in this session

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

DEMO@SES_2 > SELECT * FROM B_ROW;

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

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


--  The 2nd session freezes at this moment because the 1st session is holding the lock on this row.

At this point, you created a situation when one session is blocking another session. You might be wondering now how you can know about the existence of such sessions in an Oracle Database. So, there are some default views you can query from to find out such kind of data. I prefer to use the V$SESSION view, but there are others as well.

 

EXAMPLE
DBA_WAITERS shows all the sessions that are waiting for a lock.
SQL> SELECT HOLDING_SESSION,WAITING_SESSION,LOCK_TYPE FROM DBA_WAITERS;

HOLDING_SESSION WAITING_SESSION LOCK_TYPE
--------------- --------------- --------------------------
             17              55 Transaction
DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
SQL> SELECT HOLDING_SESSION FROM DBA_BLOCKERS;

HOLDING_SESSION
---------------
             17
V$SESSION_BLOCKERS displays the blocker sessions for each blocked session.
SQL> SELECT BLOCKER_SID,BLOCKER_SESS_SERIAL#,
     SID WAITING_SID, SESS_SERIAL# WAITING_SESS_SERIAL#,WAIT_EVENT_TEXT FROM V$SESSION_BLOCKERS;

BLOCKER_SID BLOCKER_SESS_SERIAL# WAITING_SID WAITING_SESS_SERIAL# WAIT_EVENT_TEXT
----------- -------------------- ----------- -------------------- ----------------------------------
         17                 1816          55                35751 enq: TX - row lock contention
V$SESSION displays session information for each current session.
SQL> SELECT BLOCKING_SESSION, SID WAITING_SESSION, SECONDS_IN_WAIT, EVENT FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL;

BLOCKING_SESSION WAITING_SESSION SECONDS_IN_WAIT EVENT
---------------- --------------- --------------- ------------------------------------
              17              55            4837 enq: TX - row lock contention

In this DEMO the session with SID 17 is blocking the session with SID 55 for 4837 seconds.

The DBT_BLOCKERS custom view displays the all neccesary information about current blocking and waiting sessions. For instance

SET PAGES 100
SET LINES 200 
COL BLOCKING FOR A20 
COL BLOCKED FOR A20
COL ON_OBJECT FOR A40
COL ROWID_FOR_LOCKED_ROW FOR A20
COL LOCKED_SQLID FOR A15

SELECT * FROM DBT_BLOCKERS;
BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT       ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- --------------- -------------------- ---------------
17,1816(DEMO)        55,35751(DEMO)                  4903              82 DEMO.B_ROW      AAASfmAAHAAAAFuAAA   5zprqh5ydng78

To release the row lock, commit the transaction by the first session

DEMO@SES_1 > SELECT * FROM B_ROW;

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

DEMO@SES_1 > COMMIT;

Commit complete.

Right after the 1st session has committed its transaction, the 2ndsession continues working.

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

1 row updated.

Elapsed: 01:28:36.55

-- Commiting transaction

DEMO@SES_2 > commit;

Commit complete.

Elapsed: 00:00:00.00

DEMO@SES_2 > SELECT * FROM B_ROW;

     DIGIT STRING
---------- ----------
         1 001

The demo is completed. Drop the DEMO user

SQL> SHO USER
USER is "SYS"

SQL> DROP USER DEMO CASCADE;

User dropped.
NOTE

Imagine a situation where a user made a set of updates through an application without saving changes and went home as a working day was over. The application will hold the row locks until the morning of the next working day when a user is back. It might potentially block the work of other users.

To disconnect such IDLE blocking sessions, use either the ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE or the ALTER SYSTEM KILL SESSION ... IMMEDIATE statement.

SQL> ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' IMMEDAITE;
SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDAITE;

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Server).
The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources.
If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

Whether or not the session has an ongoing transaction, Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.

Written At
31 OCT 202119:40
OEL Release
7.9 x64
Database Release
19.12.0.0.0