Violation of PRIMARY KEY constraint? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Violation of PRIMARY KEY constraint?

Author

Topic

bdaviduck
Starting Member

1 Post

Posted - 2010-06-29 : 23:58:29

Hello,

This is driving me crazy. I have a SQL table that keeps track of various "object" statistics (by statistic type and date). However, when the stored procedure that updates the table is executed, we receive random failures due to the following error:

Error Message:
Violation of PRIMARY KEY constraint 'PK_stats'. Cannot insert duplicate key in object 'stats'.

Below are the environment, table and stored procedure details:

Environment:
SQL 2000

Table:


CREATE TABLE [dbo].[stats] (
	[object_uid] [uniqueidentifier] NOT NULL ,
	[stat_type] [int] NOT NULL ,
	[stat_date] [int] NOT NULL ,
	[stat_count] [int] NOT NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[stats] WITH NOCHECK ADD 
	CONSTRAINT [PK_stats] PRIMARY KEY  CLUSTERED 
	(
		[object_uid],
		[stat_type],
		[stat_date]
	) WITH  FILLFACTOR = 90  ON [PRIMARY]

Stored Procedure:


/*
*
* Update statistics for a given object UID, stat type and date ...
* stat_date is in the form: YYYYMMDD
*
*/
CREATE PROCEDURE sp_stats_update
 @object_uid UNIQUEIDENTIFIER,
 @stat_type INT,
 @stat_date INT
AS

DECLARE @err_code AS INTEGER

SET NOCOUNT ON

SET @err_code = 0

BEGIN TRANSACTION

/*
* If the statistic type/date already exists, increment the counter 
* otherwise insert a new record ...
*/
IF ( EXISTS( SELECT * FROM stats
              WHERE object_uid = @object_uid
                AND stat_type = @stat_type
                AND stat_date = @stat_date ) )
   BEGIN

   UPDATE stats
      SET stat_count = stat_count + 1
      WHERE object_uid = @object_uid
      AND stat_type = @stat_type
      AND stat_date = @stat_date

   SET @err_code = @@ERROR

   END
ELSE
   BEGIN

   INSERT stats
      VALUES ( @object_uid, @stat_type, @stat_date, 1 )

   SET @err_code = @@ERROR

   END

/* If ok, commit ... */
IF ( @err_code = 0 )
   COMMIT TRANSACTION
ELSE
   ROLLBACK TRANSACTION

RETURN

When the above stored procedure is executed, it first checks if a record exists for the given object_uid, stat_type and stat_date. If no record exists, a record is inserted with a stat_count of 1. If the record does exists, the stat_count is simply incremented by 1.

So, -- why does SQL Server keep reporting a primary key violation at seemingly random times?? I thought that a TRANSACTION would prevent this from ever happening?

Does anyone see something I have missed in the stored procedure that could cause this error to occur?

Or... is this a bug with SQL Server 2000??

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-30 : 02:49:56

Hello,

Unfortunately, I don't see any error in your stored procedure and table DDL. You must check input parameters of the procedure, especial "object_uid".

Best regards,

Devart Team

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 03:39:29

Devart is right.
Check if @object_uid is NULL sometimes.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-30 : 11:50:39

How is the stored procedure being executed? Is from a exe or from within SQL?

Is there a chance that the caller is calling the stored proc with the same params (perhaps back to back) and that is causing a race condition?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-30 : 12:10:16

You need a higher isolation level for the check on stats.
Try something like:


SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO
CREATE PROCEDURE dbo.sp_stats_update
	@object_uid UNIQUEIDENTIFIER,
	@stat_type INT,
	@stat_date INT
AS

DECLARE @err_code AS INTEGER
	,@row_count int


SET NOCOUNT ON

SELECT @err_code = 0, @row_count = 0

INSERT INTO dbo.stats
SELECT @object_uid, @stat_type, @stat_date, 1
WHERE NOT EXISTS
(
	SELECT *
	FROM dbo.stats WITH (UPDLOCK, HOLDLOCK)
	WHERE object_uid = @object_uid
		AND stat_type = @stat_type
		AND stat_date = @stat_date
)


SELECT @err_code = @@ERROR, @row_count = @@ROWCOUNT

IF @err_code <> 0
BEGIN
	-- handle error
END

IF @row_count = 0
-- Do an update as no insert
BEGIN
	UPDATE dbo.stats
	SET stat_count = stat_count + 1
	WHERE object_uid = @object_uid
		AND stat_type = @stat_type
		AND stat_date = @stat_date

	SET @err_code = @@ERROR
	
	IF @err_code <> 0
	BEGIN
		-- handle error
	END
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources