Re: Oracle Select/Insert Locking

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

-- 
Ed Avis <ed_at_membled.com>