2

System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'AK_SeqNo'. Cannot insert duplicate key in object 'dbo.SeqNo'.

I get the above SQL Server 2005 Express error randomly. Maybe once every 3 weeks from the stored procedure below. Can anyone see why?

CREATE PROCEDURE [dbo].[mspResetSeqNo] @Today DATETIME
AS 
BEGIN
  SET NOCOUNT ON;

  DECLARE @DateSrc DATETIME
  DECLARE @MyGUID UNIQUEIDENTIFIER

  -- Check Input is Valid
  IF @Today IS NULL 
     BEGIN
           RAISERROR (N'@Today cannot be NULL', 10, 1); 
           RETURN 1;
     END

  -- Chop off the time part:
  SET @DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today));

  -- Get Current Location GUID
  SET @MyGUID = dbo.MyGUID();

  -- If this is the first entry for the day then initialise
  INSERT INTO dbo.SeqNo(MyGUID, TheDay, LastNo)
  SELECT @MyGUID, @DateSrc, 0
  WHERE NOT EXISTS ( 
      SELECT 1 FROM dbo.SeqNo AS sn
      WHERE sn.MyGUID = @MyGUID AND sn.TheDay = @DateSrc 
      );

  RETURN(0);
END

The constraint for AK_SeqNo is:

ALTER TABLE [dbo].[SeqNo] ADD CONSTRAINT [AK_SeqNo] UNIQUE NONCLUSTERED 
(
[TheDay] ASC,
[MyGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

The table column for those 2 data types are:

[MyGUID] [dbo].[DForeignKey] NOT NULL,
[TheDay] [datetime] NOT NULL,

The user defined type DForeignKey is:

CREATE TYPE [dbo].[DForeignKey] FROM [uniqueidentifier] NULL

The MyGUID() function is simply retrieving the local system ID. Every location has a different ID.

CREATE FUNCTION [dbo].[MyGUID]()
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @me as uniqueidentifier
SELECT @me = MyGUID FROM Self
RETURN @me
END
Cheval
  • 403
  • 4
  • 14
  • How is the `AK_SeqNo` constraint defined? – PinnyM Feb 07 '13 at 04:54
  • I've updated the message with those details. – Cheval Feb 07 '13 at 04:59
  • Are there any triggers on table dbo.SeqNo? – Igor Borisenko Feb 07 '13 at 05:26
  • What are the datatypes of SeqNo.TheDay and SeqNo.MyGUID in the database? If TheDay is `DATE`, that'd do it for example. – Joachim Isaksson Feb 07 '13 at 05:33
  • @TildalWave That only tells the me the types of `@DateSrc` and `@MyGUID`, not the datatypes in the table. – Joachim Isaksson Feb 07 '13 at 05:36
  • 2
    @TildalWave For example, `2012-10-01 12:00` and `2012-10-01 14:00` are different `DATETIME`, but if inserted into the same `DATE` column, they're not unique. – Joachim Isaksson Feb 07 '13 at 05:37
  • Triggers on table are only for system created merge replication ones. The column TheDay is a DateTime, but as you see the time part is removed on the insert. This SP works every day except for some random ones. – Cheval Feb 07 '13 at 05:45
  • The MyGUID just gets the system ID for the location. Every instance of the application has a unique location ID which is stored in the Self table. My first thought was that MyGUID() was returning NULL, but then there would be a different error. – Cheval Feb 07 '13 at 05:52
  • @JoachimIsaksson, looks like you should write this up as an answer... – s.bandara Feb 07 '13 at 05:53
  • Cutting off the time part of `@DateSrc` could result in a constraint violation. What time is this procedure triggered and how long does it take to complete? What's your timezone? – s.bandara Feb 07 '13 at 05:58
  • I'm not following you. This stored procedure's job is to initialise the SeqNo table for today for the current location. ie. Set the LastNo field to 0. It can run many times a day when the application starts, but only initialises once. – Cheval Feb 07 '13 at 06:03
  • The procedure usually runs between 8:30am and 10:00am, but it can be run throughout the day. The timezones are all across Australia. One side point I've noticed is that the error occurs more often after SQL Express has been asleep "Server resumed execution after being idle". – Cheval Feb 07 '13 at 06:06
  • @s.bandara I also initially thought that until I realised that the date only is used both in the where clause as well as the insert. – Cheval Feb 07 '13 at 06:12
  • 1
    @TildalWave I see what you are saying but isn't that the point of the WHERE NOT EXISTS portion of the query? If the SeqNo has already been initialised, then don't do the insert. This works perfectly for 99% of the time. – Cheval Feb 07 '13 at 06:14
  • @TildalWave Sorry, I missed that. No, there is no transaction. This SP is called stand alone before moving on in the code. – Cheval Feb 07 '13 at 06:26
  • I don't know about atomicity of stored procedure calls and I also don't know MS SQL Server, but could your wakeup notification mean that two calls are run concurrently, one for today, and one for yesterday? What about locks? Could a lock be used to protect from such a "race condition"? – s.bandara Feb 07 '13 at 06:29
  • Do you have all SQL Server 2005 Service Packs installed? Can you provide result of `SELECT @@Version` – Igor Borisenko Feb 07 '13 at 06:38
  • @s.bandara Yes, I checked that via a SQL Server profile trace and there is only one connection and the calls are sequential. As far as yesterday or today, the SP is called using the system datetime at that point, so no shared data. No locks shown in the profile data. Strange thing is this is one single SQL statement, so maybe we should wrap that statement in a transaction?? – Cheval Feb 07 '13 at 07:44
  • @TildalWave Yes, no transaction, no separate thread just the UI, no concurrency that we can see as it happens so infrequently. Maybe we'll try wrapping that statement in a transaction to full check for that. No releasing data aware object? I've tried taking that line and running it thousands of times in our staging and test systems, but it doesn't fail, plus it only runs on app start. It's run from VB.NET and via LLBLGen PRO. – Cheval Feb 07 '13 at 07:49
  • @Igor Yes, all Service Packs are installed to Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3) – Cheval Feb 07 '13 at 07:52
  • I think I found the bug... DATEADD(d, 0, DATEDIFF(d, 0, Today)); should be DATEADD(d, DATEDIFF(d, 0, Today), 0); (Had to remove the AT symbol.) – Cheval Feb 08 '13 at 21:46
  • @Cheval: Did changing the order of the `DATEADD` parameters work? If so, you can post this as the answer and accept it. – gmm Feb 12 '13 at 00:48
  • Yes the changed order still works and no it didn't fix the problem as it was caused by a concurrency issue. – Cheval Feb 15 '13 at 06:04

2 Answers2

1

The problem was a concurrency issue. The single instance check was moved to after the point in code where this stored procedure was called. Thanks for your help. At least I learned a thing of 2 more about t-sql functions.

Cheval
  • 403
  • 4
  • 14
0

Is this the only way in which records get added to the table?

I'd also use CONVERT for my comparison, something like this:

  INSERT INTO dbo.SeqNo(MyGUID, TheDay, LastNo)
  SELECT @MyGUID, @DateSrc, 0
  WHERE NOT EXISTS ( 
      SELECT 1 FROM dbo.SeqNo AS sn
      WHERE sn.MyGUID = @MyGUID
      AND
      CONVERT(VARCHAR(11), sn.TheDay, 101) = CONVERT(VARCHAR(11), @Today, 101)
      );

This gets you away from having to store the intermediate value in a variable.

David T. Macknet
  • 3,112
  • 3
  • 27
  • 36