Hi Kresten ( @krestensb )

The background jobs you are referring to; are these standard IFS methods or custom methods you have had developed?

I could understand the occasional lock but not the constant locking that you describe above.

Cheers,

Pete

It would be occasional and for most part on custom methods.

Hi @krestensb ,

You can build a workaround to auto re-execute these jobs by using the event BACKGROUND_JOB_IS_PROCESSED.

Add an SQL event action with following.

BEGIN

IF '&STATE' = 'Error' AND INSTR('&ERROR_TEXT', 'MyCustomLU.CustomErrorCode') > 0 THEN

Deferred_Job_API.Repost_Job('&JOB_ID' );

END IF;

END; 

In your custom code, handle exception using Error_Sys so you can use the error code to exactly filter the erroneous jobs

Note! Use above workaround if you are sure that re-execute would Finish the job. Otherwise this will re-execute jobs in infinite loop 

Cheers!

Damith

That is a good workaround, but I’m looking for a way to check if a row is locked before I try to do operations on that row.

I think we need to get a complete picture of the problem here.

@krestensb - can you post a screenshot/example of this error in Background Jobs and the associated code behind it?

i would like to understand the nature of these locks.

@dsj great solution! I often forget about the out of the box events.

If you want to check a row for locks before doing anything the easiest way is to try to lock it with NOWAIT.

You will get an Oracle error if it is locked by another process and you can handle the exception and do what you like.

IFS does that in the framework in method Lock_By_Keys_Nowait___ if you want to investigate.

Below is an example of a method I created from LU ReturnMaterial which locks the record if possible and returns ‘TRUE’ if lock is successful and ‘FALSE’ if not. This is a quick copy from Lock_By_Keys_Nowait___ which I modified so it can be improved. For example it requires the record to exist but that you might already know.

FUNCTION C_Lock_By_Keys_Nowait (
   rma_no_ IN NUMBER) RETURN VARCHAR2
IS
   row_locked  EXCEPTION;
   PRAGMA      EXCEPTION_INIT(row_locked, -0054);
   dummy_      NUMBER;
BEGIN
   SELECT 1
   INTO  dummy_
   FROM  return_material_tab
   WHERE rma_no = rma_no_
   FOR UPDATE NOWAIT;

   RETURN 'TRUE';
EXCEPTION
   WHEN row_locked THEN
      RETURN 'FALSE';
END C_Lock_By_Keys_Nowait;

So simple ! Brilliant!