Stored Procedure reporting Primary key violation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure reporting Primary key violation

Author

Topic

Toregilb
Starting Member

4 Posts

Posted - 2011-09-24 : 05:14:58

Hi ,
The following stored procedure reports a violation of primary keyfor the table White_PHONESUBSCRIBER, and White_PHONESUBSCRIBER

White_PHONENUMBER :

WORD_PART (PK, varchar(50), not null) /* telefonnr */
SUBSCRIBER_ID (PK, varchar(50), not null)
PHONE_TYPE (PK, int, not null)

WHITE_PHONESUBSCRIBER :

SUBSCRIBER_ID (PK, varchar(50), not null)

---------------------------------------------------------------------

GO
/****** Object: StoredProcedure [dbo].[UpdateSubscriberInWhitePhoneSubscriber] Script Date: 09/24/2011 09:46:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[UpdateSubscriberInWhitePhoneSubscriber] (@matchstr varchar(255))
as
set nocount on
declare @dbdid varchar(50)

/*** now delete the corresponding matchstr entry, if any ****/
delete from dbo.White_PHONENUMBER where matchstr = @matchstr

/*** now delete the corresponding matchstr entry, if any ****/
delete from dbo.White_PHONESUBSCRIBER where matchstr = @matchstr

select top 1 @dbdid = DBDK_ID from white where matchstr = @matchstr order by matchstr,dbdk_id

/** try to remove any duplicate entries in phone subscriber before inserting a new one **/
delete from White_PHONESUBSCRIBER where subscriber_id = @dbdid
delete from White_PHONENUMBER where subscriber_id = @dbdid

insert into White_PHONESUBSCRIBER
( SUBSCRIBER_ID, title, NAME, ADDRESS, ZIP, DISTRICT, PLACENAME, STREET, HOUSENUMBER, STRID, COUNTY_ID, HOUSENUMBERLIST, matchstr, email, www, sortingstringfornnp, timestamp) SELECT
top 1 DBDK_ID, title, name, address, zipcode, district, village, street, houseNumberFrom, streetID, municipalityID, dbo.makeHouseNumberList(houseNumberFrom, houseNumberTo), matchstr, email, www, isnull(zipcode, '')+';'+isnull(street, '')+';'+isnull(name,'')+';', timestamp from white where DBDK_ID = @dbdid
update White_PHONESUBSCRIBER set timestamp =(select MAX(timestamp) from White where matchstr = @matchstr) where matchstr = @matchstr

/*** add dbdk where clause in order to avoid primary key violation when multiple
phone numbers exist for the same phone type ****/

insert into White_PHONENUMBER (WORD_PART, SUBSCRIBER_ID, PHONE_TYPE, ADM_SUBSCRIBER_ID, matchstr) select phone, @dbdid, 1, dbdk_id, @matchstr from white where matchstr =@matchstr and dbdk_id = @dbdid and phone is not null
insert into White_PHONENUMBER (WORD_PART, SUBSCRIBER_ID, PHONE_TYPE, ADM_SUBSCRIBER_ID, matchstr) select mobil, @dbdid, 2, dbdk_id, @matchstr from white where matchstr =@matchstr and dbdk_id = @dbdid and mobil is not null
insert into White_PHONENUMBER (WORD_PART, SUBSCRIBER_ID, PHONE_TYPE, ADM_SUBSCRIBER_ID, matchstr) select fax, @dbdid, 3, dbdk_id, @matchstr from white where matchstr =@matchstr and dbdk_id = @dbdid and fax is not null
set nocount off

------------------------------------
And during the subsequent attempt at inserting a row into White_PHONENUMBER, a similar PK Violation is reported.

How can that be ? The last thing I do before trying to insert a new row into White_PHONESUBSCRIBER is to delete any records there.
Same applies to the White_PHONENUMBER table, where any rows containing a potential duplicate is removed.

Your help is most appreciated.

toregilb

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 07:24:27

"The last thing I do before trying to insert a new row into White_PHONESUBSCRIBER is to delete any records there"

With this code you mena?


select top 1 @dbdid = DBDK_ID from white where matchstr = @matchstr order by matchstr,dbdk_id

what if there is more than 1 matching record?

Toregilb
Starting Member

4 Posts

Posted - 2011-09-28 : 03:31:04

I was referring to the line
delete from White_PHONESUBSCRIBER where subscriber_id = @dbdid

Thus, an insertion of a new record with the same dbdk_id should not be possible, as long as I have just deleted the current entry, if any.
That is what confuses me.

However, I no longer experience this pk violation problem, after several file loads. Is there any delay between the time I modify a stored procedure, until it actually becomes the active one ? Using Enterprise server, with "parse" and "execute" of the stored procedure in order to save it. But does it automatically come into effect ?

toregilb

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 04:07:47

"Thus, an insertion of a new record with the same dbdk_id should not be possible, as long as I have just deleted the current entry, if any."

Could there be another user / process running the stored procedure at the same time? That could create a race-condition for that particular ID

Any chance the PK has been changed for the [White_PHONESUBSCRIBER] table? Perhaps double check that it is indeed [SUBSCRIBER_ID]

Check that the "PK Violation" message refers to THIS stored prodcedure's name. It might be in a trigger, for example.

"Is there any delay between the time I modify a stored procedure, until it actually becomes the active one?"

No, it will be immediately available as soon as the EXECUTE CREATE/ALTER PROCEDURE ... has finished running

Any chance that the modification you made was saved as a different Schema rather than DBO? (The code you posted explicitly says DBO so that seems unlikely)

Toregilb
Starting Member

4 Posts

Posted - 2011-09-28 : 04:44:17

Thanks Kristen
Unfortunately, there is not another, simultaneous process creating a race condition. I am actively controlling the process.
It is indeed SUBSCRIBER_ID which is the Pk for that table [White_PHONESUBSCRIBER]

I added debug information in the script, and the error occurs while executing that particular stored procedure. It returns with
the matchstr that actually causes the violation. When I later examined the data (DBDK_ID) related to that particular matchstr, I did indeed find an entry in the White_PHONESUBSCRIBER table, for that DBDK_ID. But since there is no time stamp on the entry, I cannot see whether it cause the PK violation because it was there already, or whether it was actually inserted by the stored procedure, despite the error message.
Could there be a delay in the database itself, i.e. that the deletion of the row prior to insertion of a new one, is not "registered" by the stored procedure ? I have never experienced such behavior before, but I am running out of ideas.

The modifications that I made were made for that schema. It is also evident from when I open the stored procedure for modification again, where I can see that the changes I made recently are still there.

Like I mentioned earlier, I have not experienced the PK violation recently. Even when reloading the same files which initially caused the violation.

Thanks for taking the time.

regards
toregilb

toregilb

Kristen
Test

22859 Posts

Posted - 2011-09-28 : 05:13:41

"Could there be a delay in the database itself, i.e. that the deletion of the row prior to insertion of a new one"

Not that I know of, but maybe there is some isolation mode, or snapshot or something that could induce that. Goes against all I know about databases though!

No replication trying to insert it, or something else, is there?

Put a trigger on the table and record changes to an Audit table so you can see when the DUP was created? Next time it happens that might help.

"I am running out of ideas"

Me too !!

"I have not experienced the PK violation recently"

I expect you would like to pin-point a cause, and then you'll know you've fixed it / it won't occur again.

Maybe there was a bug in the Sproc, since fixed, and that's solved it.

Toregilb
Starting Member

4 Posts

Posted - 2011-09-28 : 07:11:51

The worst part of it is when I cannot pinpoint the exact reason why it is working now..

No, there is no replication trying to insert this row.

Since this stored procedure is called up to 4-5 million times during a load session, I must be careful not to add performance-degrading "debug" features to it. I guess a trigger affects performance, even when it is not triggered ?

I will await another occurrence of it (hopefully it won't happen again), and take it from there.

toregilb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources