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:49:16 CST
