UNIQUE index key violation

Question

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 SomeOtherTable.

I know I can mitigate the duplicate key error by setting IGNORE_DUP_KEY to 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.

Answer 1

There is a lot to grasp if you want to fully understand all of the intricacies of locking and isolation levels in SQL Server, and your question is essentially leading you in this direction.

All of the information is available at http://msdn.microsoft.com/en-us/library/ms173763.aspx, although it's not an easy read. An easier read is available at http://databases.about.com/od/sqlserver/a/isolationmodels.htm - pay particular attention to the "phantom reads" section.

For what you are trying to do you would need BOTH transactions running with an isolation level of "SERIALIZABLE". What this basically means is that they will run "as if" one was run after the other - that they were actually serialized.

What is happening is that BOTH the transactions decide that the same record needs to be inserted, and then BOTH try to insert that same record. The lock that is taken by the test logic is separate from the lock that is taken for the insert, which allows this to happen. SERIALIZABLE will stop this from happening with it's "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes" clause. Another way to think of this is that the read locks from the test need to be held until the whole transaction completes. This can be specified using a HOLDLOCK query hint; but I have to say that I've not personally played with using HOLDLOCK, but just with the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" version.