Skip to main content

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

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

 

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!


Reply