Can multiple CPUs cause Primary Key Violations?

Question

Can multiple CPUs cause Primary Key Violations?

DBADave

SSChampion

Points: 10962
More actions
January 12, 2007 at 8:39 am

#68132

We are working with a vendor to troubleshoot performance problems with their application. The vendor recently asked if our server contained multiple processors and if it did that could be the reason we are experiencing primary key violations. Their explanation is as follows:
The errors are caused a result of the script not taking into account execution on a machine with multiple processors. Basically an additional ‘where’ clause is required to filter out the additional thread data that is identical in every aspect expect for the thread id value that is caused as a result of multiple processors.
I've asked to see the before and after versions of the code because I can't picture what they are saying. Is what they are saying possible?
Thanks, Dave

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736632 More actions · Answer 1

I don't think this is possible. There should not be anything in an insert that needs to know about other threads unless this is some type of performance monitoring application.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I think you should publish the name of the vendor so we can all have a laugh - and avoid their products!!!

Agree with Steve - not feasable - multi cpu's can cause problems but not he violation of a PK, unless anyone can publish a demo script - I can't think the last time I used a single threaded server, even some of my home kit is dual proc or dual core.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

DBADave SSChampion Points: 10962 More actions · Answer 3

The vendor just clarified their statement when they sent me the code changes. They are referring to Microsoft's blocker script they asked me to run to detect blocking and waiting. Throughout the year we have experienced PK violations with their application and back in September I ran their version of the MS blocker script and the script spit out a number of PK violations. I thought their latest email was referring to the application-related issues and not their blocker script. They are indicating the MS blocker script's insert statement should be checking "where (blocked!=0 or waittype != 0x0000) and ecid = 0. I searched the Internet and found no indication the blocker script can cause a PK violation on a multiple proc server, but at least this makes more sense then what I thought they were originally indicating.

Thanks, Dave

MohammedU SSCoach Points: 19644 More actions · Answer 4

Is PK voilation error related to blocker table or application table?

If it is application table then what is the relaton between application table and blocker script?

MohammedU
Microsoft SQL Server MVP

Lowell SSC Guru Points: 323518 More actions · Answer 5

Correct me if I'm wrong, but the blocker script is an example of what happens when you have high volume inserts, right? I've read that the identity() column can have performance issues when it approaches 400 inserts per second...but that creates lock and timeout issues, not PK violations.

Does this application have high volume inserts into a table like that? (maybe a log or audit table?)

a PK violation is most likely being caused on a table with multiple columns being part of the primary key, and the application inserting duplicate data, instead of maybe updating where the column combination already exists.

can you confirm that the PK violation is on an identity() column , or does the table have a wider PK definition involving multiple columns?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBADave SSChampion Points: 10962 More actions · Answer 6

It's just the standard MS blocker script that the vendor customized a bit. It almost looks like an older version of the script when compared with the MS version for 2000 and 2005. I'm not going to spend any time troubleshooting their script. If the PK errors happen again I'll just download the MS script and replace the vendor's version with the MS version.

Thanks, Dave

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What they may be talking about is the fact that on a multi-proc machine, sysprocesses can return more than one entry with the same spid (for a process that parallels across multiple servers)

If they're doing some form of insert into ... select ... from sysprocesses and are putting the pk on the spid, then yes, it is possible.

If that is the case, then it's a poorly written, poorly tested script and the vender should fix it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

I still think you should publish the vendor name - modified ms procs now too!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/