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.

What you are describing looks like a case of bad design. There is no excuse for the problem existing in the first place. Why aren't the message_id values unique? Why aren't you generating them with a sequence?

The entire process you describe needs to be redesigned by someone that understands how Oracle works. Do you have a DBA you can ask for help?

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