Cancel concurrent request "could not lock request" message

As part of our day to day Apps DBA job, we get the ask from other product support teams to cancel concurrent requests based on business requirement. However, at times we get into a situation where even we are unable to cancel request or it simply just hangs. For Example:- 

• When you cancel or terminate concurrent request from SYSADMIN it  throws "could not lock request"
• Even running the below sql in backend to terminate the request also hangs doing nothing

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id=12345;

- When the above update state was running in session 1 , you can open duplicate session 2 and run the below statement to find out which SID is causing the lock to session 1 ( "update" statement).  Then clear the blocking session

SELECT inst_id, DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM GV$LOCK

   WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request;

- Some times, you may not find  any database blocking session, the option would be to find any FNLIBR process that is still accessing concurrent request id.  In AIX system, it's easy, run fuser command 

   fuser -u $APPLCSF/$APPLLOG/l12345.req  10682440(oracleebs)

- LINUX system, you can use below query  

Set Pages 1000

Set head on

Column Manager   Format A12

Column Request   Format 999999999

Column Program   Format A30

Column User_Name Format A15

Column Started   Format A15

Column FNDLIBR  Format A9

prompt Managers that is running a request and FNDLIBR PROCESS;

select substr(Concurrent_Queue_Name,1,12) Manager, Request_Id Request, User_name,

       Fpro.OS_PROCESS_ID "FNDLIBR",

       substr(Concurrent_Program_Name,1,35) Program, Status_code, To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started

  from apps.Fnd_Concurrent_Queues Fcq, apps.Fnd_Concurrent_Requests Fcr,

      apps.Fnd_Concurrent_Programs Fcp, apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes Fpro

where

       Phase_Code = 'R' And Status_Code <> 'W' And

       Fcr.Controlling_Manager = Concurrent_Process_Id       and

      (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    and

       Fcq.Application_Id      = Fpro.Queue_Application_Id ) and

      (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id and

       Fcr.Program_Application_Id = Fcp.Application_Id )     and

       Fcr.Requested_By = User_Id and Fcr.request_id=&request_id

         order by Started;MANAGER         REQUEST USER_NAME       FNDLIBR PROGRAM                        S STARTED

------------ ---------- --------------- --------- --------- ------------------------------ - ---------------

STANDARD        12345 XXYYY          9240602     XLAACCPB                       R 25-AUG-17 15:03

Now check the process in CM tier 

ps -ef|grep 9240602|grep -v grep

  oraebs  9240602  8060984   0   Aug 25      -  0:49 FNDLIBR 

Once you have FNDLIBR process details,  you can find the corresponding database session  with below SQL and in our case the session has been waiting for a very long time 23409 secs

select  ses.sid,ses.serial#, proc.spid,ses.sql_id,ses.process, ses.last_call_et, ses.event from gv$session ses, gv$process proc where ses.paddr = proc.addr and ses.process='9240602';

   SID SERIAL# SPID   SQL_ID        PROCESS                  LAST_CALL_ET EVENT

------ ------- ------ ------------- ------------------------ ------------ ---------------

   804   14487 51118106 fnpyvpk41nd5s 9240602                        243409 Streams AQ: waiting for messages in the queue

After clearing the DB session , you can kill the concurrent request from backend

alter system kill session '804,14487,@1'

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id=12345;

commit;