Solved

Postpone background job execution when "...object is locked by another user."

  • 5 October 2020
  • 7 replies
  • 827 views
krestensb
Rank
Userlevel 3
Badge +8

Hi,

Very often we see background jobs in status ERROR with the message “...object is locked by another user.“.

How can I handle this in my PLSQL procedure?

 

There is a lock_sys package and oracle has a v$lock table.

The lock_sys takes lock_name_ as a parameter. I don’t know what that is.

The second includes an ADDR column. I don’t know what that refers to.

 

Can any of this be used to handle this error, by fx postpone the execution of the job if the object is locked?

BR Kresten

icon

Best answer by Tomas Ruderfelt 6 October 2020, 10:12

View original
BR Kresten

7 replies

pwlm
Rank
Userlevel 4
Badge +11

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

Pete McDermott - https://linkedin.com/in/pwlmcdermott
krestensb
Rank
Userlevel 3
Badge +8

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

BR Kresten
dsj
Rank
Userlevel 7
Badge +20

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 :wink:

 

Cheers!

Damith

 

DSJ - https://dsj23.me
krestensb
Rank
Userlevel 3
Badge +8

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.

BR Kresten
pwlm
Rank
Userlevel 4
Badge +11

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.

Pete McDermott - https://linkedin.com/in/pwlmcdermott
T
Rank
Userlevel 7
Badge +19

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;

 

krestensb
Rank
Userlevel 3
Badge +8

So simple ! Brilliant!

BR Kresten

Reply


Terms & ConditionsCookie settings