DB Version : 19.18
OS : Oracle Linux 7 (Exadata)
We are seeing blocking session in our database from application schema. One thing we observed that there are sessions which are blocking each other (see below) :
select sid, sql_id, blocking_session, event, seconds_in_wait from gv$session where blocking_session is not null order by blocking_session;
SID SQL_ID BLOCKING_SESSION EVENT SECONDS_IN_WAIT
---------- ------------- ---------------- ------------------------------ ---------------
622 9xhv1cq2z423b 351 enq: TX - row lock contention 13788
518 9xhv1cq2z423b 449 enq: TX - row lock contention 12388
449 9xhv1cq2z423b 518 enq: TX - row lock contention 9368
454 9xhv1cq2z423b 622 enq: TX - row lock contention 8904
315 9xhv1cq2z423b 622 enq: TX - row lock contention 13789
351 9xhv1cq2z423b 622 enq: TX - row lock contention 13789
The sql ID is an update statement and the blocking seesions are staying forever, if we don't kill it. Both the SID & Blocking sessions are active session and logon_time is recent (few hours back). Blocking session is also running the same sql id (update)
Question is :
- If sessions are blocking each other, shouldn't it be a deadlock ? We don't see any deadlock alert in our database ?
- The application team says that they are clearing their session and managing the connection pool and they don't see any issue on application side. But we see these blocking session very frequently (almost all the time).
- when I look at the OEM blocking session page, I don't see sessions blocking each other (possibly due to the way the blocking session report is presented in OEM ) - see below .

Has anyone experienced it ? Any suggesstions ?
Here is the blocking session detail from gv$session with inst_id and final_blocking_session.
