Is it feasable to have a candidate index on this combination of fields?
>I have a situation where I am looking for the best approach. I need to assure that only a specific record (based on a combination of fields) is created in a table. So, before doing the INSERT INTO command I am looking into the table to see if a specific user has created a record for a specific primary key field ID. If those two values are matching a record, we don't proceed to add it in there as it's already there.
>
>The problem I have is that two users can issue the same command as the same time. So, when both are executing the LOCATE in the table to find the record, it is not there. So, both users go into the condition and the INSERT INTO is involved. However, that creates a duplicate record. I'm looking for the best to assure that this won't happen.
>
>I need to avoid doing a FLOCK(). I'm looking for another approach.
>
>This is also different that locking a record, such as getting the next primary key as this is a process that need to lock it at the table level.
Steve Gibson