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.
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 🙂