Sample Header Ad - 728x90

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

0 votes
2 answers
674 views
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
Asked by Jakub P (167 rep)
Jun 2, 2020, 06:29 AM
Last activity: Jun 2, 2020, 04:10 PM