Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locking on insert

Re: Locking on insert

From: Mark <simmons_mark_at_yahoo.com>
Date: 24 Mar 2004 08:47:40 -0800
Message-ID: <5366fb41.0403240847.60a86846@posting.google.com>


Very cool. I hadn't thought of doing it that way.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

Connor McDonald <hamcdc_at_yahoo.co.uk> wrote in message news:<4060CD79.59CD_at_yahoo.co.uk>...
> 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
Received on Wed Mar 24 2004 - 10:47:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US