Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
I think you hit the nail on the head when you said that the locking is not happening as expected. In other words, I think your expectations are not correct. It will take shared locks only on the rows queried in Table1 in the subquery and probably only exclusively lock minimal resources for the row being imported. So the subquery looking for rows to insert is not getting blocked but the insert part does, and it waits for the first query to release its exclusive lock and now the row already exists. –  Robert L Davis Mar 18 '13 at 19:09
    
@Robert Thanks! I figured as much... So I should really look at removing the NOLOCK hint is what I'm reading into this. I just was also reviewing the table hints section in MSDN which states that all hints propogate to all objects in the query plan. –  squillman Mar 18 '13 at 19:30
    
WITH (NOLOCK) is allowing both queries to read the same items, and attempt to insert them. –  Max Vernon Mar 19 '13 at 19:56

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.