0

I am trying to resolve an issue where the application user is prompted ORA-00054 in Oracle AgilePLM. From sql logs I came to know that the following command is causing this issue.

SELECT 1 FROM CLUSTER_THREAD_LOCKS WHERE LOCK_TYPE = 3 FOR UPDATE OF LOCK_COUNT, LAST_LOCK_TIME NOWAIT

Status:FAILURE

Reason - ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I cannot set auto commit on as it is a session based setting and same goes to ddl_lock_timeout, and I wanted it to be global. However, I am trying to a write a procedure and then schedule it in a DBMS_JOB, it will find the blocking session(s) on CLUSTER_THREAD_LOCKS and commit that session(s). The problem I am facing is how to issue a commit to that session(s) on the basis of it sid and serial# in a stored procedure. Following is the procedure:

CREATE OR REPLACE PROCEDURE SP_COMMIT_SESSION()
AS
v_session_no int;
v_sid int;
BEGIN
  
for c in 
  (
  select obj.object_id, ses.sid, ses.serial#,user_objects.object_name, to_char(ses.logon_time, 'MM-DD-YYYY HH24:MI:SS') lock_date
from v$session ses, V$LOCKED_OBJECT obj, user_objects where 
ses.sid = obj.session_id and user_objects.object_id = obj.object_id
AND USER_OBJECTS.OBJECT_NAME = 'CLUSTER_THREAD_LOCKS'
)
loop
  v_session_no := c.serial#;
  v_sid := sid;
  
  /*
  Issue commit here
  */
end loop;

end;

I can neither kill a session as I do not know its' impacts, nor I can change how the SQL command is issued from the application. Could anyone point me to the right direction?

Burhan Khalid Butt
  • 275
  • 1
  • 7
  • 20
  • You cannot commit outside of the session. You should redesign the application that holds objects for long time or redesign your application that will take such case into account and retry its action after some interval. – astentx Nov 11 '21 at 14:59
  • Several things don't make sense. `autocommit` has no bearing on the situation. Neither does `ddl_lock_timeout` since the timeout is coming from a DML statement not DDL. You can't cause another session to commit-- the only way to forcibly cause another session's transaction to end is to roll it back by killing the session. Forcibly committing another session's transaction would have vastly more (and worse) impacts than forcibly killing it so it makes no sense to do the former in order to avoid doing the latter. – Justin Cave Nov 11 '21 at 15:01
  • Is there any other option to resolve this issue? Or killing session is the only option? – Burhan Khalid Butt Nov 11 '21 at 15:01
  • 3
    The immediate problem is that the application doing the `select` is explicitly asking for a `nowait` so it is actively asking Oracle to throw this error if some other session holds the lock. If you don't want the application to get this error, you'd want to change the `select` to specify a `wait`. Or to somehow ensure that no other session can ever possibly have a row level lock for an instant of time. – Justin Cave Nov 11 '21 at 15:04
  • 3
    Ensuring there is never, ever the briefest of lock contention seems like an unlikely proposition so you're left with changing how the application that does the `select ... for update` makes its request or how it handles the error it is actively asking Oracle to throw. – Justin Cave Nov 11 '21 at 15:04

0 Answers0