Intermitent Primary key violation in Nightly jobs

Question

Post reply

Intermitent Primary key violation in Nightly jobs

alext

Valued Member

Points: 67
More actions
March 31, 2010 at 3:26 am

#103919

We have jobs that perform inserts into a table with an alphanumeric primary key using the following SQL (see below) . Rightly or wrongly it gets the next primary key by updating a special primary key table 'tblPrimaryKeyTrigger'. This fires a trigger to update the next value in tblPrimaryKeyValues. This normally works ok, but occasionally fails because it tries to insert a value that has already been inserted. Its like it goes round the while loop before the trigger has time to update the next value. Any advice on how to prevent this greatly appreciated!!
-------------------------------------------------------
while @C <= (select count(*) from @tablevar) begin
insert into tblContactProfilesCR (contactprofileid, DateVerified, DateEntered, ActiveStatus, EducationNumber, contactid, StatementId, StatementAnswerId)
select (SELECT 'C' + CAST(NextPrimaryKeyValue AS varchar) AS PrimaryKeyValue FROM tblPrimaryKeyValues WHERE (TableName = 'tblContactProfilesCR')), DateVerified, DateEntered, ActiveStatus, EducationNumber, contactid, StatementId, StatementAnswerId from @tablevar where id = @C
update tblPrimaryKeyTrigger set tablename = 'tblContactProfilesCR'
set @C = @C +1
end
-----------------------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

post tables defintions

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lowell SSC Guru Points: 323518 More actions · Answer 2

Alex i think i can see a couple of issues here.

I'm not trying to bash you, just hoping to help you make your process better.

first, it's clear that you are using a cursor/while loop, instead of using a set based INSERT INTO... SELECT

you can get rid of that and do it in a single operation for an order of magnitude performanc eenehancement.

second, it appears that update statement "update tblPrimaryKeyTrigger set tablename = 'tblContactProfilesCR'" is doing something manually that an identity() column would do automatically; am i right? why are identity() columns NOT being used? even if you didn't want to use an identity(), if you needed some value to increment, you could use row_number() function for a multi row insert, probably with an offset of the highest value in the table.

you posted a snippet of the code in question; can you post the whole thing? if it's huge,add it as an attachment.

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!

Paul White SSC Guru Points: 150468 More actions · Answer 3

You just need to prevent the race condition.

The following code demonstrates a safe implementation:

USE tempdb;

GO

-- Create the sequence table

CREATE TABLE dbo.PrimaryKeys

(

table_name SYSNAME NOT NULL PRIMARY KEY,

next_value INTEGER NOT NULL

);

GO

-- Create the record for this table

INSERT dbo.PrimaryKeys

(table_name, next_value)

VALUES (N'ContactProfilesCR', 1);

GO

-- Variable to hold the new PK

DECLARE @pk INTEGER;

-- Correct way to get a new PK value into a variable

UPDATE dbo.PrimaryKeys WITH (READCOMMITTEDLOCK)

SET @pk = next_value,

next_value = next_value + 1

WHERE table_name = N'ContactProfilesCR';

-- Show the PK

SELECT @pk;

GO

DROP TABLE dbo.PrimaryKeys;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 4

Quoting the OP's first post:

We have jobs that perform inserts into a table with an alphanumeric primary key...

it seems like the major issue is the alphanumeric key. Therefore neither Lowells nor Pauls approach would help here (at least not as described).

But since both recommended solutions are known to be reliable and fast, my guess would be that the problem is the decision to have an alphanumeric key...

There are some more downsides when using alphanumeric PK's, especially when used as the clustered index.

Example: when the business rule for the PK does not enforce ascending (or descending) order of each new PK value, then you might end up with frequent page splits, therewith decreasing performance.

Another downside would be that you need to make sure you don't end up with any primary key value that would be offending or illegal in some way. E.g. a valid char(9) key would be "Terr0rist"... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 5

lmu92 (4/3/2010)
...it seems like the major issue is the alphanumeric key...

I may have misread the code, but it seems that the 'alphanumeric key' is simply the character 'C' followed by a number. The mechanism for updating the sequence number appears to be an AFTER UPDATE trigger on the sequence table. The code I posted was to illustrate a better design.

If the key is as simple as 'C' + number...

CREATE TABLE dbo.PrimaryKeys

(

table_name SYSNAME NOT NULL PRIMARY KEY,

next_value INTEGER NOT NULL,

next_key AS 'C' + CONVERT(VARCHAR(11), next_value)

PERSISTED NOT NULL

);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

alext Valued Member Points: 67 More actions · Answer 6

Many thanks for replies. I have bypassed the trigger method and updated the table direct and so far so good. I am not sure why it was originally implemented using a trigger because there was no more logic in it than to update the key value to +1.