Re: Oracle Select/Insert Locking

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 10 Feb 2004 08:31:07 GMT
Message-ID: <c0a4sb$143cl5$2@ID-82536.news.uni-berlin.de>

> 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.

You still haven't made clear what the problem is.  

>
> "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?
>> >

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Tue Feb 10 2004 - 02:31:07 CST