Why does the insert that groups the primary key give an error violating the primary key constraint?

Question

I have an insert statement that throws a primary key error, but I don't see how I could insert duplicate key values.

First I create a temporary table with a primary key.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED //Note: I've tried committed and uncommited, neither materially affects the behavior. See screenshots below for proof. IF (OBJECT_ID('TEMPDB..#P')) IS NOT NULL DROP TABLE #P; CREATE TABLE #P(idIsbn INT NOT NULL PRIMARY KEY, price SMALLMONEY, priceChangedDate DATETIME); 

Then I pull the prices from the Prices table, grouping by idIsbn, which is the primary key in the temp table.

 INSERT INTO #P(idIsbn, price, priceChangedDate) SELECT idIsbn , MIN(lowestPrice) , MIN(priceChangedDate) FROM Price p WHERE p.idMarketplace = 3100 GROUP BY p.idIsbn 

I understand that grouping by idIsbn by definition makes it unique. The idIsbn value in the price table: [idIsbn] [int] NOT NULL .

But every time I run this request, I get this error:

 Violation of PRIMARY KEY constraint 'PK__#P________AED35F8119E85FC5'. Cannot insert duplicate key in object 'dbo.#P'. The duplicate key value is (1447858). 

NOTE. I have a lot of questions about the timing. I will select this operator, press F5, and there will be no error. Then I will do it again and it will fail, then I will run it again and again and it will succeed several times before it will fail again. I suppose I say that I can’t find the template when it will succeed and when it will not.

How can I insert duplicate rows if (A) I just created a brand new table before pasting into it, and (B) I am grouping a column for the primary key?

I am currently IGNORE_DUP_KEY = ON problem with IGNORE_DUP_KEY = ON , but I would really like to know the root cause of the problem.

This is what I actually see in my SSMS window. There is nothing more and nothing less:

@@Version:

 Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64) Apr 3 2015 14:50:02 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Execution plan:

Here is an example of how it looks when it works fine. Here I use READ COMMITTED, but that does not matter. B / c. I get an error, regardless of whether I read it perfect or inactive.

Here is another example of this failure, this time w / READ COMMITTED.

also:

• I get the same error if I populate a temporary table or a permanent table.
• When I add option (maxdop 1) to the end of the insert, it seems like it fails every time, although I cannot be completely sure about this b / c, I cannot run it for infinity. But it seems to be so.

Here is the pricing table definition. The table has 25M rows. 108 529 updates in the last hour.

 CREATE TABLE [dbo].[Price]( [idPrice] [int] IDENTITY(1,1) NOT NULL, [idIsbn] [int] NOT NULL, [idMarketplace] [int] NOT NULL, [lowestPrice] [smallmoney] NULL, [offers] [smallint] NULL, [priceDate] [smalldatetime] NOT NULL, [priceChangedDate] [smalldatetime] NULL, CONSTRAINT [pk_Price] PRIMARY KEY CLUSTERED ( [idPrice] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [uc_idIsbn_idMarketplace] UNIQUE NONCLUSTERED ( [idIsbn] ASC, [idMarketplace] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

And two non-clustered indexes:

 CREATE NONCLUSTERED INDEX [IX_Price_idMarketplace_INC_idIsbn_lowestPrice_priceDate] ON [dbo].[Price] ( [idMarketplace] ASC ) INCLUDE ( [idIsbn], [lowestPrice], [priceDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Price_idMarketplace_priceChangedDate_INC_idIsbn_lowestPrice] ON [dbo].[Price] ( [idMarketplace] ASC, [priceChangedDate] ASC ) INCLUDE ( [idIsbn], [lowestPrice]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 

Answer 1

You did not specify your table structure.

This is a reproduction with some alleged details that lead to a reading issue (NB: now you have provided the definition that I see in your case. Updates in the priceChangedDate column will move rows around the IX_Price_idMarketplace_priceChangedDate_INC_idIsbn_lowestPrice index if the one you are looking for)

Compound 1 (setup tables)

 USE tempdb; CREATE TABLE Price ( SomeKey INT PRIMARY KEY CLUSTERED, idIsbn INT IDENTITY UNIQUE, idMarketplace INT DEFAULT 3100, lowestPrice SMALLMONEY DEFAULT $1.23, priceChangedDate DATETIME DEFAULT GETDATE() ); CREATE NONCLUSTERED INDEX ix ON Price(idMarketplace) INCLUDE (idIsbn, lowestPrice, priceChangedDate); INSERT INTO Price (SomeKey) SELECT number FROM master..spt_values WHERE number BETWEEN 1 AND 2000 AND type = 'P'; 

Compound 2

Parallel DataModifications that move the line from the beginning of the search range (3100,1) to the end (3100,2001) and are repeated again.

 USE tempdb; WHILE 1=1 BEGIN UPDATE Price SET SomeKey = 2001 WHERE SomeKey = 1 UPDATE Price SET SomeKey = 1 WHERE SomeKey = 2001 END 

Compound 3 (Insert a unique constraint into the temp table)

 USE tempdb; CREATE TABLE #P ( idIsbn INT NOT NULL PRIMARY KEY, price SMALLMONEY, priceChangedDate DATETIME ); WHILE 1 = 1 BEGIN TRUNCATE TABLE #P INSERT INTO #P (idIsbn, price, priceChangedDate) SELECT idIsbn, MIN(lowestPrice), MIN(priceChangedDate) FROM Price p WHERE p.idMarketplace = 3100 GROUP BY p.idIsbn END 

The plan has no aggregate, since there is a unique restriction on idIsbn (a unique restriction on idIsbn, idMarketplace will also work), so the by group can be optimized because there are no duplicate values.

But when reading a fixed isolation level, split row locks are released as soon as the row is read. Thus, a string can be moved in places and read a second time by the same search or scan.

The ix index does not explicitly include SomeKey as a secondary key column, but since it is not declared unique, SQL Server silently includes a clustering key behind the scenes, so updating this column value can move rows around it.