I am getting persistent but intermittent "Violation of PRIMARY KEY constraint" Errors

I am the person in my company who tries to solve coldfusion errors and bugs. We get daily emails with full details of coldfusion errors etc, as well we store this information in our database.

And for a few different applications in ColdFusion, they seem to sporadically generated "Violation of PRIMARY KEY constraint" errors.

In the code we always check for the existence of a row in the database before we try to do an insert, and it still generate's that error.

So my thinking is, either we need to a cftransaction around these each of the check, insert or update blocks. But I am not sure this will truly solve the problem.

These are coded in standard coldfusion style/framework. Here is an example in pseudo-code.

cfquery name="check_sometable" datasource="#dsn#" select id from 开发者_如何学Gosometable /cfquery

if check_sometable.recordcount gt 0 -do insert else -do update /endif

So why would this intermittently, cause primary key violations?

Is this a sql server problem, are we missing a configuration option?

Are we getting all of this because we are not on the latest hotfixed version of coldfusion 8 standard?

Do we need to upgrade our jdbc/odbc drivers?

Thank You.

Sounds like race conditions to me. Two connections check for the next available id at the same time, get the same one and then the insert fails on the second one. Why are you not using an identity field to create the PK if it is a surrogate key?

If you have a PK that is a natural key, then the violation is a good thing, you have two users trying to insert the same record which you do not want. I would try to fail it gracefully though, with an error that says someone else has created the same record. And then ask if they want to update it after loading the new values to their screen. I'm not sure I would want it to set up so that the data is automatically updated by the second person without them seeing what the first person put into the database.

Further this might be an indication that your natural key is not as unique as you think it is. Not sure what this application does, but how likely is it that two people would want to be working with the same data at a the same time? So if your natural key were something like company name, be aware that they are not guaranteed to be unique and you might have users overwriting good data for one company with data for another company already. I've found in life there are truly very few really unique, never changing natural keys. So if your natural key really isn't unique, you may already have bad data and the PK violations are just a symptom of a differnt problem not the real problem.