Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Select/Insert Locking

Re: Oracle Select/Insert Locking

From: Daniel Morgan <>
Date: Tue, 10 Feb 2004 12:34:14 -0800
Message-ID: <1076445209.548913@yasure>

Ed Avis wrote:
> "M2" <> writes:

>>I have an application that records when a user has read a message in a table
>>that looks like so:
>>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.

> I am not an expert on this but as every response so far has
> conspicuously failed to answer the question, perhaps one solution
> would be to not have a primary key. You can still have an index on
> these two columns, but it need not be unique. Then an occasional
> duplicate insert will not matter. You can check if a user has read a
> message with
> exists (select * from t where message_id = m and username = u)
> which is probably the same check you'd use even with a unique index.
> (If you don't like the idea of a table without a key, you could add a
> third column, taken from some sequence, just to make each row unique.)
> Alternatively, you might be able to insert rows with
> insert into t (message_id, username)
> values (m, u)
> where not exists (
> select * from t
> where message_id = m
> and username = u
> )
> I believe this is safe but I have not tested it. I hope that the
> single SQL statement will always be an atomic transaction, so it
> cannot ever insert a row if one already exists.

No one suggested it because it is an idea that would be enough to cause me to Fail a student on a final and demonstrates a complete lack of understanding of relational database architecture, concepts, and theory. In other words ... You don't remove a pimple from your nose by cutting off your head.

I strongly urge you to take a class on relational database basics.

Daniel Morgan
(replace 'x' with a 'u' to reply)
Received on Tue Feb 10 2004 - 14:34:14 CST

Original text of this message