I have a table with a PK and a unique non-clustered index, as follows:
CREATE TABLE Table1 ( Id INT IDENTITY(1,1) NOT NULL, Field1 VARCHAR(25) NOT NULL, Field2 VARCHAR(25) NULL, CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Id ASC) ) CREATE UNIQUE NONCLUSTERED INDEX IX_Field1_Field2 ON Table1 ( Field1 ASC, Field2 ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
I have 2 jobs whose execution times I find are overlapping each other. They both include the same
INSERT into this table and frequently the job that starts last fails because it tries to insert a record into
Table1 with a duplicate index key value.
INSERT Table1 SELECT Field1, Field2 FROM SomeOtherTable sot WITH (NOLOCK) WHERE NOT EXISTS ( SELECT 1 FROM Table1 t1 WHERE sot.Field1 = t1.Field1 AND sot.Field2 = t1.Field2 )
From what I've been able to discern, the
INSERT in Job1 is still executing when the
NOT EXISTS from Job2 is evaluated resulting in Job2 trying to insert a duplicate key value. It seems to me that the locking for
Table1 is not happening as expected.
I'm at a loss as to why this is happening. Would this have anything to do with the
NOLOCK hint used in the
INSERT? I didn't think that that hint would include
Table1 in its scope, only
I know I can mitigate the duplicate key error by setting
ON for the index, and that would be fine for us in this situation. I would like to know, though, why the duplicate is showing up in the 2nd INSERT.