Re: Oracle Select/Insert Locking

Ok, I'll ignore the continued patronisation and implications on my intelligence and attempt to clarify by supplying some information I had disregarded in the hope of brevity.

The message table looks like this:

MESSAGE

MESSAGE_ID - sequence generated number

MESSAGE - content of the message

etc etc other message meta data

Then there's the table that records that X user has read Y message so it can be differentiated visually in the client.

READS

MESSAGE_ID USERNAME Usually there is no problem with any of this. MESSAGE_ID is the unique primary key in MESSAGE and MESSAGE_ID + USERNAME (given each user can read the message) is the unique primary key in READS. There's no possibility of trouble as the primary key constraints block them. But, that was never the issue. The problem is the race condition that is possible when parallel hits occur for the same user and the same message.

Basically, when we load tested the application we intentionally did a mass number of reads of the same message as the same user to see how it would fare. Again, for the most part it's fine. However, there does appear to exist that very narrow window where the two parallel clients both check to see if the message has been read, both find it ok (since neither have inserted yet) and both try to insert. The first gets in fine, the second gets the constraint violation. While this violation has no real effect (the constraint blocked it and the code trapped it) I am still not happy that I can't find a way in which to ensure the second insert is never attempted. This seemed to me to be something that must occur regularly in the database world which is I have I have sought assistance here.

Matt.

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1076423870.503664_at_yasure...
> M2 wrote:
>
> > 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.
>
> I understand English may not be your first language but either your
> description is inadequate or the problem you describe impossible.
>
> If an insert consists of two columns, MESSAGE_ID and USERNAME, and
> MESSAGE_ID is unique because it is sequence generated there is no
> constraint conflict possible. Just insert the things as fast as the
> machine will allow. There is nothing to check until you run out of disk
> space.
>
> --
> 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 - 15:42:41 CST