Re: Pre-Checking for Lock

Simon Hedges wrote:
>
> Does anyone know if there's a way of processing only records that records
> are not locked, preferably using SQL (i.e. using bulk UPDATE and DELETE
> statements), but I'll take a PL/SQL row-by-row solution if I have to.
>
> I have a batch task that I want to run in the background that processes a
> lot of data. I don't want it to be stopped because of locking, but I'm
> happy to process the unlocked records now, and then come back and do the
> rest in half an hour. After 3 hours, I can check for any records that are
> still unprocessed and then raise an alert for me to investigate.

You are going to have to do it row by row in PL/SQL.

The only way of testing whether a row is locked is

SELECT ... FOR UPDATE NOWAIT You will want to put

PRAGMA EXCEPTION_INIT(resource_busy, -54);   /* ORA-00054 is "resource busy and acquire with NOWAIT specified" */

at the top of your code. Then you can write exception handlers to deal with locked rows:

BEGIN
  SELECT ... FOR UPDATE NOWAIT;
  /* Process the row you succeded in locking */ EXECPTION WHEN resource_busy THEN
  /* Deal with the lock you could not get */ END;

-- 
Charles Jardine - Computing Service, University of Cambridge
cj10_at_cam.ac.uk    Tel: +44 1223 334506, Fax: +44 1223 334679