+8Hi,
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
Best answer by Tomas Ruderfelt
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;
+11Hi Kresten (
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
+8It would be occasional and for most part on custom methods.
+22Hi
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
+8That 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.
+11I think we need to get a complete picture of the problem here.
i would like to understand the nature of these locks.
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;
+8So simple ! Brilliant!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.