Deadlock using double-checked locking in SQL

Question

I'm experiencing deadlocks from my SQL statement in which I want to select an ID if it exists, else insert and then select it. I'm using double checked locking to prevent locking overhead, as suggested here.

Obviously I'm doing this to support concurrent inserts, and I'm running multiple threads. My SQL know-how is very low, so I might have missed something basic about locking? Here's my procedure:

CREATE PROCEDURE InsertAndOrSelectZipCity
@PostalDistrict nvarchar(25),
@CityName nvarchar(34),
@MunicipalityId smallint,
@ZipCode smallint
AS

DECLARE @id AS INT
SELECT @id = ZipCityId FROM ZipCity (NOLOCK) WHERE MunicipalityID=@MunicipalityId AND ZipCode=@ZipCode
IF @id IS NULL
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        SELECT @id = ZipCityId FROM ZipCity WHERE MunicipalityID=@MunicipalityId AND ZipCode=@ZipCode
        IF @id IS NULL
        BEGIN
           INSERT INTO ZipCity (PostalDistrict, CityName, MunicipalityId, ZipCode) VALUES (@PostalDistrict, @CityName, @MunicipalityId, @ZipCode)
           SELECT @id = SCOPE_IDENTITY()
        END
    COMMIT TRANSACTION
END
SELECT @id

UPDATE

This is fixed by using the appropriate locks (XLOCK, ROWLOCK, HOLDLOCK) on the Select statement inside the transation.

Below is the procedure written using the MERGE statement instead, no transactions needed:

DECLARE @id as INT

MERGE INTO ZipCity WITH (TABLOCK) AS Target
USING (SELECT @PostalDistrict, @CityName, @MunicipalityId, @ZipCode) AS Source (PostalDistrict, CityName, MunicipalityId, ZipCode)
ON Target.MunicipalityId = Source.MunicipalityId AND Target.ZipCode = Source.ZipCode
WHEN MATCHED THEN
    UPDATE SET @id = Target.ZipCityId
WHEN NOT MATCHED THEN
    INSERT (PostalDistrict, CityName, MunicipalityId, ZipCode) VALUES (@PostalDistrict, @CityName, @MunicipalityId, @ZipCode)
OUTPUT INSERTED.ZipCityId;

Answer 1

    SELECT @id = ZipCityId 
    FROM ZipCity 
    WHERE MunicipalityID=@MunicipalityId 
      AND ZipCode=@ZipCode

Here your select is acquiring an S-lock. This can happen with multiple threads. Later, the insert tries to X-lock which is a deadlock.

Acquire the X-lock straight away:

    SELECT @id = ZipCityId 
    FROM ZipCity WITH (XLOCK, ROWLOCK, HOLDLOCK) ...

Here, ROWLOCK, HOLDLOCK are not strictly required but the pattern XLOCK, ROWLOCK, HOLDLOCK is pretty standard and I try to follow it everywhere for consistency.

Btw, you might want to switch to a MERGE statement. I think it will acquire U-locks automatically so no locking hints are required for it. Not sure about that, though. In any case it would be a code improvement as well as a performance improvement.

Answer 2

Well, it is hard to reproduce your scenario but it does look interesting.

Try using ROWLOCK i.e. doing something like this

SELECT @id = ZipCityId FROM ZipCity WITH(ROWLOCK) WHERE MunicipalityID=@MunicipalityId AND ZipCode=@ZipCode

and see if it helps (I hope it does).

Also, you might want to check this article and see if it's relevant for your scenario. Seems to me it is.

http://support.microsoft.com/kb/323630