Using HoldLock Incorrectly in SQL Server stored procedure

I believe I am using HOLDLOCK incorrectly.

The reason I think this is because I have a table that acts like a queue. The queue receives its items from the SQL below and gets processed, one by one in a console application. I haven’t tested yet but I believe when this console application starts processing this table, during some of the long selects the code below fails. Why do I think that…because I am logging the GameID when grabbing everything from the table queue and processing them one by one in that console application. The funny thing is the games that I believe didn’t make it through didn’t make it in the log, therefore I dont believe they are being inserted in my queue table and I believe it’s because of the HOLDLOCK below.

Thoughts?

MERGE Test WITH (HOLDLOCK) AS GL
USING (SELECT @GameId AS ID) AS NewTest ON GL.ID = NewTest.ID
WHEN NOT MATCHED THEN
INSERT
(
    Id,
    FailedAttempts,
    DateCreated
)
VALUES
(
    NewTest.ID,
    0,
    SYSDATETIME()
);

Answer

I suspect your issue is unrelated to your use of MERGE or HOLDLOCK. I see no reason to introduce cumbersome MERGE syntax here, since it provides no benefit, and especially given the potential issues it can cause in other areas. I suggest a very simple INSERT ... WHERE NOT EXISTS:

INSERT dbo.Test(Id, FailedAttempts, DateCreated)
  SELECT @GameId, 0, SYSDATETIME()
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.Test WITH (HOLDLOCK) 
    WHERE Id = @GameId
  );

I’d prefer this over just blindly trying to insert and getting a PK violation for the reasons outlined here and here – in almost all cases, forcing SQL Server to try and get an exception instead of checking yourself first will yield worse performance.