Re: Oracle Select/Insert Locking

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

The "usual" solution would be to have a trigger, that selected a value from a sequence, and use that as primary key (message_id, if I understand correctly).
Once used, a sequence will not give out the same number (unless defined with cycle, or re-created, of course).

So, you would just insert the user_id, and leave message_id to the trigger (which should be a row-level trigger)

-- 

Regards,
Frank van Bortel