October 27, 2009 at 10:25 am
Good afternoon,
since few months, we're working with MSSQLServer 2005 on W2k8 Server .. sometimes (with high load of requests/inserts) our applications logs:
Error SQL. Message: Violation of PRIMARY KEY constraint 'PK__XXXXX'. Can not insert a duplicate key in object 'dbo._XXXX.', Error code: 2627
It's like SQLServer could not properly manage the primary keys ....
is it performance issue?
Any clue to solve this issue?
Thanks in advance,
Gerard Espona
October 27, 2009 at 10:27 am
is there any code that sets identity_insert on and then specifies a key value?
Do you have any replication running on the table?
October 27, 2009 at 1:15 pm
Is that primary key dependent on a datetime or time field? You could be getting duplicate values during the insert under high loads.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 27, 2009 at 1:21 pm
The problem with your question is that there isn't enough information to actually give you an answer. We really need more information to help you.
October 28, 2009 at 3:16 am
Primary key is:
CONSTRAINT [PK__XXXX] PRIMARY KEY CLUSTERED
(
[A] ASC,
ASC,
[C] ASC,
[D] ASC,
[E] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
where:
[A] [varchar](5) NOT NULL,
[datetime] NOT NULL,
[C] [datetime] NOT NULL,
[D] [varchar](3) NOT NULL,
[E] [varchar](20) NOT NULL,
It's running replication but after duplicated key error appears.
More clues:
Problematic inserts are done with TRIGGER:
USE [YYYYYY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ZZZZ]
ON [dbo].[CCCCCCC]
AFTER INSERT
AS
declare @vid as int, @vf as datetime, @vp as varchar(3), @vn as varchar(4), @va as varchar(20), @van as varchar(20)
select @vid=inserted.id, @vf=inserted.f, @vp=inserted.p, @vn=inserted.n, @vas=inserted.a, @va=left(inserted.a,20)
from inserted
if upper(@vp)='S' and @vn='7' and (upper(@va)='N')
begin
insert UUU.dbo.IIIIII (..........................)
end
--------------------
<b> Only field D are different: right('000'+@vid,3) </b>
So I think duplicated key is generated when high-load produces simultaneous calls to TRIGGER and sometimes tries to insert the same "key" ... (?)
How can I prevent it?
Regards,
Gerard Espona
October 28, 2009 at 5:30 am
From a performance standpoint that's a fairly wide clustered index. I sure would hesitate to make it wider, but the best way to fix it would be to add something that makes each row unique, either an identity field (better choice) or a globally unique identifier (guid, not better choice). Since your key is dependent on dates and logical values, the dates, especially under high loads, can be identical. If the logical values are identical too... so you need a tie breaker, hence the identity or the guid.
But, fundamentally, I think the design is flawed. A good primary key can never be duplicated. Because yours is able to be, you should reexamine the design.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy