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:

  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
  v$session v1, v$session v2
  v1.blocking_session is not null and v1.blocking_session = v2.sid
order by
  waitss desc

Here is sample output:

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 or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply