Jump to content


Magnus

Member Since 26 Oct 2005
Offline Last Active Oct 26 2010 08:28 PM
-----

Posts I've Made

In Topic: File Locking Inquiry

09 April 2009 - 01:06 PM

William,

There are a bunch of issues here, it's a bit hard to comment without seeing your application but..

I'm not quite clear if PROIV is configured to give you control in the event a lock is encountered, otherwise your lock logic may be ineffective (I think).

Is your lock logic being executed? Note that "Waiting for database response" is a PROIV built-in message, not an Oracle message and suggests that control is not returning to PROIV when a lock is encountered.

If not, one relevant piece of configuration might be the LOCKED_ROWS_RETURNED setting. BUT - you need to consult your colleagues because changing such a setting systemwide may well not be supported by your application.

IIRC, it is possible to set up alternative record-is-locked messages in a file definition, this should override the standard PROIV record-is-locked message for you but, again, it would be an application-wide change. Probably you can just generate a local MSG() in the lock logic.



No, the lock logic is not being executed. However I already found a work around. But thank you and appreciate your advise. Anyway as an alternative, I created this script which is called by my function to check for record locking. The work around is almost the same as what Magnus advised.

procedure check_record_locked(p_ccn ccn.ccn%type,
p_po_num po.po_num%type,
p_po_line po.po_line%type)
is
cursor check_lock is
select 'dummy' from po
where
po_num = p_po_num and
ccn = p_ccn and
po_line = p_po_line
for update nowait;

e_resource_busy exception;
pragma exception_init(e_resource_busy, -54);
begin
open check_lock;
close check_lock;
rollback;
exception
when e_resource_busy then
-- update lock table (C_FLLOCK), then PRO-IV deletes lock table before exiting the function ---
end check_record_locked;





No problem.

I have a question though. You issued an explicit rollback in your procedure, you know that that would rollback all your transactions (updates, adds, deletes) from the very start, right? Unless, you have your commit somewhere before calling the procedure. I think you can use AUTONOMOUS_TRANSACTION pragma in your procedure if the only reason why you issued the rollback is to release the record locked by the 'for update' statement.

Just a thought.

In Topic: File Locking Inquiry

06 April 2009 - 08:23 PM

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

In Topic: File Locking Inquiry

06 April 2009 - 08:23 PM

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF

In Topic: File Locking Inquiry

06 April 2009 - 08:23 PM

I'm not really sure about this but here's what you can try:

In the ARNE logic (instead of the lock logic)...

#LOCK = 0
SQL
BEGIN
SELECT 1 (will return 1 if record is not locked)
INTO :#LOCK
FROM <TABLE>
WHERE <CONDITION>
FOR UPDATE NOWAIT;
END;
ENDSQL
IF #LOCK = 0 (record is locked)
<WRITE YOUR STATEMENTS HERE...>
ENDIF