Home » SQL & PL/SQL » SQL & PL/SQL » Locking on updation or Deletion (merged)
icon4.gif  Locking on updation or Deletion (merged) [message #401669] Wed, 06 May 2009 01:34 Go to next message
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 #401674 is a reply to message #401669] Wed, 06 May 2009 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think we will reverse engineer you code?
Explain what it does (or is intended to do), detail each step you make.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Locking on updation or Deletion (merged) [message #401714 is a reply to message #401669] Wed, 06 May 2009 04:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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?
Re: Locking on updation or Deletion (merged) [message #401734 is a reply to message #401728] Wed, 06 May 2009 04:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well caught.
I'm not having a good morning so far.....
Previous Topic: ora 28000 account is locked
Next Topic: How to fast insert table
Goto Forum:
  


Current Time: Sat Dec 03 11:55:44 CST 2016

Total time taken to generate the page: 0.10413 seconds