TRUNCATE TABLE followed by INSERT produces Primary Key Constraint Violation
-
As anyone comes across this problem before?
In essence as part of a large stored procedure (with no explicit transactions i.e. BEGIN TRANSACTION/COMMIT TRANSACTION) we have the following T-SQL
TRUNCATE TABLE Table1
INSERT INTO Table1 WITH (TABLOCK) (Field1, Field2, Field3, Field4) SELECT Value1, Value2,Value3,Value4 FROM DB2..Table1
where DB2 is a different database on the same SQL Server Instance
What we get is error 2627 (primary key violation) from the INSERT Statement, but THERE ARE NO duplicate primary key values IN DB2..Table1. Our supposition is that since the SQL Server instance is running on 4 CPUs parallelism is kicking in between the TRUNCATE TABLE and INSERT statement and each statement is executing on different processors i.e. the execution plan is not recognising the interdependency between the TRUNCATE and INSERT statements (i.e. the INSERT must run AFTER the TRUNCATE).
Has anyone experienced the same problem or we missing something blindingly obvious?
-
What is the table structure of both tables ?
-
I don't understand why the table structure would have any impact?
Suffice it to say that Table1 has a primary COMPOUND key on Field1+Field2.
It's worth noting that If we replace the TRUNCATE statement with a DELETE statement, we do not get the error which seems to substantiate our theory.
-
I tried to reproduce this behavior but was not able to do so. Therefore it is still interesting to get the table definitions and enough sample data to reproduce this.
Even if your server is using parallellism that does not mean it would start processing one statement before another statement in the same batch is completed. So that is not the cause here.
-
I understand your point about parallelism not being the issue but the evidence does indicate it is. Anyway for information, the table definitions are:-
CREATE TABLE [Table1] (
[Field1] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Field2] [numeric](6, 0) NOT NULL ,
[Field3] [datetime] NULL ,
[Field4] [nvarchar] (18) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Main] PRIMARY KEY CLUSTERED
(
[Field1],
[Field2]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
on DB2 the table is
CREATE TABLE [Table1] (
[Value1] [nvarchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Value2] [numeric](6, 0) NOT NULL ,
[Value3] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[Value4] [nvarchar] (18) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [pk_Main] PRIMARY KEY NONCLUSTERED
(
[Value1],
[Value2]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
1. Please note that for confidentiality, I've changed the field names. However, what I've spotted is interesting i.e. the implicit conversion of Value2 an nvarchar(8) to a datetime value. I'm going to explicitly convert this to see what effect this has (if any).
2. As regards table sizes, the insert is performing a 2million row insert
3. As mentioned in the original post, if we replace the TRUNCATE statement with a DELETE statement it works hence my concern about parallelism since I believe DELETE is a serial statement which presumably starts by performing row locking but then cascades the locking up. I believe that TRUNCATE performs TABLE and PAGE locking from what I've read.
We're doing further testing at this end to try and get to the bottom of this one but I'd appreciate any tips or advice
-
I am fairly certain that it is not parallellism that is causing this. If anything I would guess it could be a bug with TRUNCATE. The TRUNCATE statement 'deletes' all rows from a table by deallocating the pages that the rows are stored in from the table. This is very quick and does not use much log space since only the page deallocations are logged. A DELETE logs every deleted row, so it takes a lot more time and requires a lot more log space. What kind of locks they use are not an issue here.
Just as a test, try adding a CHECKPOINT between the truncate and insert statements and see what happens.
-
you said... i.e. the implicit conversion of Value2 an nvarchar(8) to a datetime value
Hi David,
In the structures you've mentiones Value2 is a numeric in both tables.
I think that your prim key violation is for a common bug... a conversion from a datetime to a varchar(8) will get only the DATE part from a datetime value, and the hour is miss... so, if you have
table 1
Value1 Value 2
1 01/01/1980 10:00:00
1 01/01/1980 12:00:00
iuf you pass this datetime values to a varchar(8) and this are part of the prim key, surely a PK Violation will occur.
I always try to handle date and time values using full conversions, usin the CONVERT function.
-
Not only this is a convertion issue you must also keep in mind that datetime is sql server has a limited resolution of 3ms, integers do not suffer from that
Cheers,
* Noel -
Sorry gents my comment about Value2 should have read Value3 which is not part of the Primary key or any other key and is probably a red herring although I understand you comments about date conversion.
I tend to go with the theory that there may be a bug/feature with SQL TRUNCATE statement and I'll try the CHECKPOINT statement to see what effect that has.
-
Another thing you should do is try to reproduce it. If possible with a small set of data and a schema you are allowed to show, so that you can easily distribute the repro here or even to PSS. If anything you should try to reproduce it with a different set of data, so you can be sure that there is no error in your existing data.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply