Re: Locking on insert

Daniel Morgan wrote:
>
> Connor McDonald wrote:
>
> > Matthew Keene wrote:
> >
> >>"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<8vN7c.60968$1p.1003888_at_attbi_s54>...
> >>
> >>>What version of Oracle? In 9i there is an "upsert" functionality (see merge)
> >>>Jim
> >>
> >>Oops, sorry, I meant to specify in the original post that this was
> >>8.1.7 and therefore merge functionality was not available.
> >
> >
> > You can synthesize this with dbms_lock
> >
> > hth
> > connor
>
> Would you have a demo you could send me?
>
> Thanks.
>
> --
> 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)

For simplicity sake I'll assume a numeric primary key (if its not, you can use dbms_utility.get_hash_value).

The algorithm would then change from:

select for update where col = N (the primary key value) if found, then update
if not found, then insert

to:

dbms_lock.request(N)
select for update where col = N (the primary key value) if found, then update
if not found, then insert
dbms_lock.release

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"