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
Best Answer
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:
Session 1:
No commit yet, session 1 is waiting. In session 2:
And session 2 hangs there until session 1 performs a commit or rollback.