Re: Oracle Select/Insert Locking

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

Ed Avis wrote:
> "M2" <m2_at_nowhere.com> writes:
>
>

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

>
>
> 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
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 - 14:34:14 CST