How can a session be blocked by an session that is idle?

All we need is an easy explanation of the problem, so here it is.

I’m trying to identify blocking sessions using the following query:

SELECT
  v1.sid esid,  v1.blocking_session bsid,  v1.process,  v1.wait_class,  v1.event,  v1.sql_id,  v1.seconds_in_wait waitss,  v2.blocking_session bsid2,  v2.process,
  v2.sql_id,  v2.wait_class,  v2.event
from
  v$session v1, v$session v2
where
  v1.blocking_session is not null and v1.blocking_session = v2.sid
order by
  waitss desc

Here is sample output:

ESID    BSID    PROCESS WAIT_CLASS  EVENT                           SQL_ID          WAITSS  BSID2   PROCESS_1 SQL_ID_1 WAIT_CLASS_1 EVENT_1
2707    2313    8526    Application enq: TX - row lock contention   05v90pbavkptk   11      (null)  28152     (null)   Idle         SQL*Net message from client

Which indicates that the blocking session is idle. Maybe I’m missing something but I don’t understand how an idle session that is not executing any statement can be blocking anything.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It would appear the idle session has a lock on at least one record required by the blocked query. The user has already executed a query that acquired a lock. Normally the lock will be held until they commit or rollback the transaction.

I’ve run into this a few times when a developer went for lunch while in the middle of updating or inserting data. The lock was removed shortly after they returned from lunch.

If their session times out or you kill their session, the transaction should rollback and release the lock.

If you can determine what location they are connected from you may be able to contact them and request they commit or rollback their transaction.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply