I actually just noted that I said that "Process 2 inserts", I should have
said "tries to insert" as it actually causes a constraint violation and
doesn't success (as you'd expect). This is trapped in the code and causes no
dire consequences I just don't like the design that relies on this as the
trap.
"M2" <m2_at_nowhere.com> wrote in message
news:M9%Vb.576$KW.36672_at_news.optus.net.au...
> Perhaps I misexplained myself. This DOES use a sequence and they ARE
unique
> (as mentioned they're the primary key after all). Regardless, I do not
> understand how this actually relates to my problem as it' essentially
about
> how to avoid a race condition.
>
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1076392827.490976_at_yasure...
> > M2 wrote:
> >
> > > Hi,
> > > I have an application that records when a user has read a message in a
> table
> > > that looks like so:
> > >
> > > MESSAGE_ID
> > > USERNAME
> > >
> > > The code that inserts into this table does a select prior to the
insert
> to
> > > check that it won't try to do something that will cause a constraint
> > > violation (there's a PK on the two columns). This works fine most of
the
> > > time but every now and again when really hitting the site you can get
a
> > > problem. As far as I can tell the processes overlap so that one
inserts
> in
> > > between the select and the insert .e.g
> > >
> > > process 1 selects to see if cool for insert
> > > process 2 selects to see if cool for insert
> > > process 1 finds ok so inserts
> > > process 2 finds ok so inserts
> > >
> > > How can I block against this? I thought perhaps SELECT FOR UPDATE
might
> be
> > > the key but it didn't seem to do much.
> > >
> > > Matt.
> >
> > What you are describing looks like a case of bad design. There is
> > no excuse for the problem existing in the first place. Why aren't
> > the message_id values unique? Why aren't you generating them with
> > a sequence?
> >
> > The entire process you describe needs to be redesigned by someone
> > that understands how Oracle works. Do you have a DBA you can ask
> > for help?
> >
> > --
> > 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 Tue Feb 10 2004 - 00:51:34 CST
