Re: Determining if a record is locked

From: Neville Sweet <sweet.neville.nj_at_bhp.com.au.no_junk_today_thanks>
Date: 1998/03/10
Message-ID: <01bd4bd2$0668f8c0$45f11286@itwol-pc3963.itwol.bhp.com.au>#1/1

Kent,

Kent Anderson <anderske_at_agcs.com> wrote in article <34FDD353.EA3C92DC_at_agcs.com>...
> Is there a way to tell if a record is locked without
> actually blocking on it?
>
> My situation is that we have two processes acting
> on the same tables in a database. If the first process
> updates a record, then the second process's update on that
> record will block until the first process ends its
> transaction.
>
> Is there any way for the second process to determine
> if the record is locked so it can put it off until
> later?
>

As far as I am aware, there is nothing readily provided, however you don't have to wait on a record when is already locked.

Do a 'select for update nowait' and code an exception to cater for the row being locked.

eg.
CURSOR c1 IS
  SELECT 1
     FROM your_table
   WHERE 'criteria'

       FOR UPDATE OF
         your_column/s
 NOWAIT;
         
 row_locked EXCEPTION;         

 tx_dummy CHAR(1);

BEGIN
 /* ORA-00054 Resource busy and acquire with NOWAIT. */  PRAGMA EXCEPTION_INIT(row_locked, -54);          

  OPEN c1;
 FETCH c1
  INTO tx_dummy;
 CLOSE c1;          

 EXCEPTION
   WHEN row_locked
  THEN
-- Whatever you want
  END; Received on Tue Mar 10 1998 - 00:00:00 CST