Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sessions blocking each other

793316Jul 12 2023 — edited Jul 12 2023

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 :

  1. If sessions are blocking each other, shouldn't it be a deadlock ? We don't see any deadlock alert in our database ?
  2. 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).
  3. 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.

Comments
Post Details
Added on Jul 12 2023
6 comments
929 views