Re: Timeout on DBMS-Locks
Date: 1995/08/30
Message-ID: <AA8u1Hm4J8_at_percombank.kiev.ua>#1/1
> From: brorsg_at_ibm.net (Guenter Brors)
> Subject: [NEWS] Timeout on DBMS-Locks
>
> Is there any way to get a timeout when waiting on DBMS-locks?
>
> When I do SELECT FOR UPDATE and the row is locked by another user, I
> do not want to wait forever, but I'd rather get an error code after
> 60 seconds. I have found no way to program this and no Oracle
> parameter to specify it (only a parameter for distributed databases).
>
> I cannot believe that the highly praised and commonly used Oracle
> system does not provide this feature.
>
> I'm using Oracle 7.1.16 on RS/6000 and SQL*Net 2.0 on MS-Windows, with
> Pro*Cobol embedded SQL and ODBC.
>
Try SELECT FOR UPDATE NOWAIT statement, and handle ORA-00054 error at your frontend, or try something like this to make your implementation frontendindependent:
FUNCTION PersistentSelect(...) RETURN BOOLEAN IS
retry INTEGER DEFAULT 9;
delay NUMBER DEFAULT 0.33;
count INTEGER DEFAULT 0;
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT(resource_busy, -00054);
BEGIN
<<ENTRY>> -- entry point, see exception handler
count:=count+1;
IF (count > retry) THEN -- you are got me, let it do by somebody else
RETURN FALSE;
END IF;
BEGIN
SELECT ...
FOR UPDATE NOWAIT;
.................. -- do your job here
EXCEPTION
WHEN resource_busy THEN
DBMS_LOCK.SLEEP(delay); -- wait for <delay> seconds, then try again
GOTO ENTRY;
END;
RETURN TRUE;
END PersistentSelect;
Regards,
Michael Reviakin
PERCOMBANK,
Kiev, Ukraine
Received on Wed Aug 30 1995 - 00:00:00 CEST