I have the following situation.
I have RAC. On both nodes there are the locks.
On the First Node
SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME LOCKWAIT SQL_ID STATUS
1 102 enq: TX - row lock contention MYUSER 155 136972 TABLE1V 0000000810EFA958 5f4bzdg49fdxq ACTIVE
2 111 enq: TX - row lock contention MYUSER 155 136972 TABLE1V 0000000810EFAC98 5f4bzdg49fdxq ACTIVE
Blocking session info
SID EVENT USERNAME ROW_WAIT_OBJ# OBJECT_NAME LOCKWAIT SQL_ID STATUS
1 155 SQL*Net message from client MYUSER 136971 MyTABLEIMAGES_IDPK 4hw85z8absbjc INACTIVE
On the Second Node
SID EVENT USERNAME BLOCKING_SESSION ROW_WAIT_OBJ# OBJECT_NAME LOCKWAIT SQL_ID STATUS
1 65 enq: TX - row lock contention MYUSER 155 137033 FactTABLE1V 0000000810EF9B58 1mznc2z75ksdx ACTIVE
2 111 enq: TX - row lock contention MYUSER 155 136972 TABLE1V 0000000810EF9818 5f4bzdg49fdxq ACTIVE
Blocking session info
SID EVENT USERNAME ROW_WAIT_OBJ# OBJECT_NAME SQL_ID STATUS
1 155 SQL*Net message from client MYUSER 127176 MYTableLOG INACTIVE
Additional Info : Blocking session SQL_TEXT
create or replace procedure ACTIONProcedureDELETE
(
p_ID NUMBER
)
is
cursor oldval is select r.id,r.sessionstatus
from MyTABLEIMAGES r where r.idparent=p_ID;
begin
update actionmyTableblock r set r.status='False' where ID=p_ID;
for oldvalItem in oldval loop
if oldvalItem.Sessionstatus='True' then
update MyTABLEIMAGES r set r.sessionstatus='False' where r.id=oldvalItem.Id;
else
update MyTABLEIMAGES r set r.sessionstatus='True' where r.id=oldvalItem.Id;
end if;
end loop;
end ACTIONProcedureDELETE;
How do I troubleshoot this ?
As you can see blocking session is INACTIVE but still locking.
If I select v$sql_bind_capture
there is no value for VALUE_STRING
for blocking session sql_id
.
From where to start?
I can guess that there is missing commit/rollback but application developer says "I have everything ok, I have written commit where it is necessary"
Please help.
Best Answer
Query
v$transaction
on each node to see uncommitted sessions: