Locking on updation or Deletion (merged) [message #401669] |
Wed, 06 May 2009 01:34  |
salmankhokhar
Messages: 1 Registered: February 2009 Location: lahore, pakistan
|
Junior Member |
|
|
i am facing the locking issue in my application
i have used given below code, just tell me am i right or wrong
DECLARE
V_ROWID ROWID;
resource_busy1 EXCEPTION;
resource_busy2 EXCEPTION;
PRAGMA EXCEPTION_INIT(resource_busy1, -54);
PRAGMA EXCEPTION_INIT(resource_busy2, -30006);
v_failure_cause VARCHAR2(500);
crlf VARCHAR2(2) := CHR( 13 ) || CHR( 10 );
BEGIN
FOR I IN (SELECT ROWID
FROM pathology.pathology_result r
WHERE r.section_no = 'RCH08000002'
AND r.serial_no = 1
FOR UPDATE NOWAIT) LOOP
--- Delete the row
begin
DELETE FROM pathology.pathology_result
WHERE rowid = i.rowid;
EXCEPTION
WHEN OTHERS THEN
V_FAILURE_CAUSE := 'LOCK ON TABLE ORDERENTRY.EMERGENCY_VISIT_QUEUE, PLEASE TRY AFTER FEW SECONDS ' || CRLF ||
'PR_NO:' || :CPT_RETURN_DETAIL.CPT_RETURN_NO || CRLF ||
'SR_NO:' || :CPT_RETURN_DETAIL.SERIAL_NO;
DISPLAY_ALERT('MESSAHE', V_FAILURE_CAUSE || ' ' || SQLERRM);
ROLLBACK;
RAISE FORM_TRIGGER_FAILURE;
end ;
--dbms_output.put_line('lock successful...' || i.ROWID);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_failure_cause := 'LOCK ON TABLE BILLING.CPT_RETURN_MASTER, PLEASE TRY AFTER FEW SECONDS ' || crlf ||
'PR_NO:' || :CPT_RETURN_DETAIL.CPT_RETURN_NO || crlf ||
'SR_NO:' || :CPT_RETURN_DETAIL.SERIAL_NO;
DISPLAY_ALERT('MESSAHE',v_failure_cause||' '||SQLERRM);
--rollback;
--ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
--EXECUTE_QUERY;
--GO_RECORD(CURR_RECORD);
RAISE FORM_TRIGGER_FAILURE;
WHEN resource_busy1 OR resource_busy2 THEN
v_failure_cause := 'LOCK ON TABLE BILLING.CPT_RETURN_MASTER, PLEASE TRY AFTER FEW SECONDS ' || crlf ||
'PR_NO:' || :CPT_RETURN_DETAIL.CPT_RETURN_NO || crlf ||
'SR_NO:' || :CPT_RETURN_DETAIL.SERIAL_NO;
DISPLAY_ALERT('MESSAHE',v_failure_cause||' '||SQLERRM);
--rollback;
--ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
--EXECUTE_QUERY;
--GO_RECORD(CURR_RECORD);
RAISE FORM_TRIGGER_FAILURE;
WHEN OTHERS THEN
v_failure_cause := 'LOCK ON TABLE BILLING.CPT_RETURN_MASTER, PLEASE TRY AFTER FEW SECONDS ' || crlf ||
'PR_NO:' || :CPT_RETURN_DETAIL.CPT_RETURN_NO || crlf ||
'SR_NO:' || :CPT_RETURN_DETAIL.SERIAL_NO;
DISPLAY_ALERT('MESSAHE',v_failure_cause||' '||SQLERRM);
--rollback;
--ERROR_FORM(v_failure_cause,:PARAMETER.P_OBJECT_CODE);
--EXECUTE_QUERY;
--GO_RECORD(CURR_RECORD);
RAISE FORM_TRIGGER_FAILURE;
END;
|
|
|
|
Re: Locking on updation or Deletion (merged) [message #401714 is a reply to message #401669] |
Wed, 06 May 2009 04:26   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
We ca't tell you if you're right or wrong until you tell us what you're trying to do.
I can tell you that you'll never get to the WHEN OTHERS inside the loop with a Locking Error - Oracle will get the locks as soon as the loop cursor is opened, and error at that point. So, the error raised by the When Others in the loop will always be the wrong error, and as you don't return the correct error, you'll never find the actual problem.
In the main exception handler:
Why do you think that a NO_DATA_FOUND error indicates a locking problem?
There's nothing in your posted code that will raise a NDF anyway.
Why do you think that the only error you can get in this code is a Locking Error? You've explicitly trapped what you believe to be the Locking exceptions, so why have you got the code that you do for the When Others?
If (as your error message suggests) you believe that the locks you're going to hit are transient, why not make the FOR UPDATE into a NOWAIT, or a WAIT 30?
|
|
|
Re: Locking on updation or Deletion (merged) [message #401728 is a reply to message #401714] |
Wed, 06 May 2009 04:44   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JRowbottom wrote on Wed, 06 May 2009 10:26 |
If (as your error message suggests) you believe that the locks you're going to hit are transient, why not make the FOR UPDATE into a NOWAIT, or a WAIT 30?
|
That should be not a NOWAIT surely?
|
|
|
|