Locking problem [message #82586] |
Wed, 11 June 2003 05:55 |
Padmalaya Nanda
Messages: 7 Registered: June 2003
|
Junior Member |
|
|
I've an application developed using developer 2000. The appl. needs to be accessed
by multiple users, as there is a post-query trigger on one block(having multiple records)
, which assigns some value to one of the items of the block, so the records are locked.
So whne the second user accesses the application, he gets
"FRM-40501:Oracle error:Unable to reserve record for update or delete" error for
each record of the block. How can I make the block read only when the second user
accesses the application, which trigger i should use, if the trigger is on-lock,
then what should I write in that trigger.
I can set the locking mode to immediate or manual, but it has to be immediate as the application is very big and this change has to be made for around 100 forms. We've a central library which handles the on-lock trgger, what should I write there, so that if the form is accessed by the first user the records should be locked and first use should be allowed to change, but all other users should see the records in read-only mode. Could u pls. help me the solution asap?
|
|
|
Re: Locking problem [message #82593 is a reply to message #82586] |
Thu, 12 June 2003 00:14 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Let's say you have a block EMP and you'd like to raise the SAL by 2% , so in the POST-QUERY, the code is like this:Begin
:EMP.SAL := :EMP.SAL*1.02;
End; In a multi user environment, you want to avoid that 2 people perform the same action on the same time. Only the first user should to the increase in salary, for the rest there should be no change.
I think you should use a function (program unit) like this: Function fun$lock(p_empno emp.empno%TYPE) Return Number
Is
record_locked Exception;
PRAGMA EXCEPTION_INIT(record_locked,-54);
Cursor c
Is
Select 1
From emp
Where empno = p_empno
For Update Nowait;
Begin
Open c;
Close c;
Return 0;
Exception
When record_locked Then
Return 1;
End fun$lock; If the function returns 0, there's no error encountered, the record wasn't locked by any other person. If the function returns 1, the record is locked (ORA-00054: resource busy and acquire with NOWAIT specified encountered).
Now you include this function in the post-query:Begin
If fun$lock(:EMP.EMPNO) = 0 -- no locks
Then
:EMP.SAL := :EMP.SAL*1.02;
Else -- record is currently locked, prevent updates.
Set_Item_Instance_Property('EMP.SAL', CURRENT_RECORD, UPDATE_ALLOWED, Property_False);
Set_Item_Instance_Property(... -- repeat for any other changeable database field to avoid the FRM message
End If;
End; I didn't test it, but I know I've seen a similare mechanism somewhere.
MHE
|
|
|