Re: Wait for lock?

Just being picky here, but all the responses that suggest the loop option with a sleep put in a sleep for one second. The two sleep implementations in PL/SQL (in dbms_lock and user_lock) allow for a sleep time granularity of 1/100 sec, so the sleeps can be a lot shorter if you expect all the transactions that might collide to be very short transactions.

(With typical Oracle consistency, you express dbms_lock.sleep() in seconds with 2 d.p. but you express user_lock.sleep in hundredths !)

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Connor McDonald wrote in message <39C48D1E.133A_at_yahoo.com>...


>dan wright wrote:

>>

>> Interesting that I got a much greater response from the Informix

>> newsfroup about using Oracle after heavy use of Informix than I did from

>> the Oracle newsfroup.

>>

>> Anyway - apparently Oracle has no equivalent to Informix's ability to

>> specify a timeout period when waiting for a lock.

>>

>> Is this correct?  You can either tell Oracle to wait forever, or to not

>> wait at all.

>>

>> So, if I expect the possibility of contention on a database table, but

>> setting isolation to dirty read is not an option, I can't tell Oracle to

>> keep trying for a few seconds?

>>

>> So, what in Informix would be a simple "SET LOCK MODE TO WAIT 5" would

>> be implemented by trapping lock exceptions and retrying several times

>> before giving up.  What a pain....

>>

>> And another thing - just what is the ANSI standard for an outer join?  I

>> thought both Informix and Oracle were ANSI-compliant, but the syntax for

>> outer joins doesn't seem similar at all  (At least in Informix it's

>> simple and obvious).....

>> (Incidentally Sybase has a really screwed up syntax for outer joins as

>> well)

>>

>> Thanks for any input

>

>This is sort of true - the NATIVE ability for a timeout is not there,

>but its trivial to implement with basically

>

>loop up to 'x' times

>  try get lock

>  if ok, then break out of loop

>  if not, the sleep 1 second and loop around

>end of loop

>

>which gives you a timeout of 'x' seconds..

>

>HTH

>--

>===========================================

>Connor McDonald

>http://www.oracledba.co.uk

>

>We are born naked, wet and hungry...then things get worse