Re: Locking Multiple records

Runa,

What kind of checking do you need to do? Could it be done in the WHERE clause?

You mentioned 'bulk records' -- how many records are being processed?

To identify and lock all records in the batch , and avoid looping (typically a performance bottleneck) it may be good if you could try something like:

    update source_table
    set batch_id = xxxx, batch_status = 'PENDING'     where <selection criteria>

    insert into target_table (...)
    select ...
    from source_table
    where batch_id = xxxx

    update source_table
    set batch_status = 'COMPLETE'
    where batch_id = xxxx

• mcs

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1068748369.329339_at_yasure...
> Runa wrote:
>
> >Thanks for all the replies...actually the tran i am talking about is
> >something which loads records from one DB to another. So I need to
> >select bulk records at a time. So I thot I would use 'select for
> >update'. We need to check each record, copy it to the other DB &
> >finally update the process_flag in the original record, so that I
> >don't select it again. But now I think, the selecting & locking will
> >have to be done seperately.
> >
> >
> After reading this I am a bit concerned with whether you understand
> Oracle's architecture.
> What do you think you are accomplishing by locking a record if all you
> are doing is
> copying it?
>
> Do you think someone could modify the record while it is being copied?
> Do you think someone could block the copying if you don't lock it?
> What is the problem you are trying to solve?
>
> Because I see no reason to lock anything based on what you've posted so
far.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Thu Nov 13 2003 - 13:05:01 CST