Re: Oracle Select/Insert Locking

"M2" <m2_at_nowhere.com> writes:

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

Instead of check-then-set, why not do it in one step by just trying to insert a row. Catch any constraint violation that occurs, and if you get one you know that a row was already present, otherwise you know that the row has been safely inserted.

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

Oh, I see. I thought you were concerned about a race condition where you might 'display' the same message again to a user who had already seen it. If all you want is a record, after the fact, to show that user X has seen message M then you could make a table with

    username message_id counter

where counter is a monotonically increasing value taken from a sequence. (I was going to suggest using a timestamp but of course you could get two requests in the same second or millisecond.) Then a view on this table of (username, message_id) will be enough to check whether a user has seen a message.

-- 
Ed Avis <ed_at_membled.com>