How to connect to specific session id & serial# ? Blocked sessions

Question

I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How?

Background: using V$SESSION_BLOCKERS I see sessions are blocked by each other by INSERT statements to one of two tables: _REQUEST and _RESPONSE

Blocked sessions wait event: enq: TX - row lock contention

Query 1:

insert into _REQUEST (creation_date, IS_PROCESSED, name, packet, PARENT_SKID, BATCH_SKID, retry_delay, revision, SERVICE_NAME, ttl, type, REQUEST_SKID)
values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 )

Query 2a (from DB trigger, :B1 is a payload):

SELECT COALESCE(ERROR_DETAILS, ERROR_MESSAGE, FAULT_REASON, SOAP_ERROR) AS ERROR_MESSAGE, ERROR_CODE 
FROM XMLTABLE( '//*:DataHandlerWebServiceException | //*:Fault | //*:PLMDataHandlerError' 
     PASSING XMLTYPE(:B1 )
     COLUMNS ERROR_DETAILS VARCHAR2(256) PATH '*:errorDetails'
            ,ERROR_MESSAGE VARCHAR2(256) PATH '*:errorMessage'
            ,ERROR_CODE VARCHAR2(8) PATH '*:errorCode'
            ,FAULT_REASON VARCHAR2(256) PATH '*:Reason/*:Text'
            ,SOAP_ERROR VARCHAR2(256) PATH '*:COMMON_LOG_MESSAGE/*:LOG_MESSAGE' )

Query 2b (we already know APPEND is ignored here, see Note 1):

INSERT /*+ append */ INTO _response( RESPONSE_SKID, REQUEST_SKID, HAS_FAILED, WAS_TIMEOUT, IS_PROCESSED, SOAP_REQUEST_TIMESTAMP, SOAP_RESPONSE_TIMESTAMP, RESPONSE_HTTP_STATUS, ERROR_MESSAGE, RESPONSE_BODY )
values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )

How is it possible that INSERTs, to different tables, are blocking the sessions? I was looking into V$ACTIVE_SESSION_HISTORY but don't find anything yet (I'm not a DBA yet)

Note 1: APPEND hint ignored

Answer 1

That will not happen. You can kill a session, or cancel the SQL it is currently executing from another session, but you can not end its transaction (with leaving the session alive). Usually such blocking sessions are just simply killed.

For your other question: blocking another transaction in another session that inserts to another table may be blocked through foreign keys. Example:

create table request(c1 number(10, 0) not null primary key);
create table response (c1 number(10, 0) not null,
                       request_c1 number(10, 0) not null references request(c1));

Session 1:

SQL> insert into request values(1);

1 row created.

No commit yet, session 1 is waiting. In session 2:

SQL> insert into response values(1, 1);

And session 2 hangs there until session 1 performs a commit or rollback.

Answer 2

It is not possible to connect to another session in that way. The original client needs to submit the commit, or kill the session and allow the transaction to rollback.