database - SQL Server - updlock, serializable doesn't block 100%

Question

I have a stored procedure where my code basically inserts a record if the ids don't exist in the table, all wrapped in a transaction called from app which also uses a transaction scope.

The stored procedure code snippet looks like:

begin transaction

set @exist = (select top 1 id from table with (updlock, serializable)  
              where uid = @uid and gid = @gid)

-- if doesn't exist... insert
commit transaction

For more background, there are multiple servers calling this exact stored procedure.

I have read multiple resources and they all seem to point out that using (updlock, serializable) should ensure blocking, similar to this Why does my SQL Server UPSERT code sometimes not block?

Some where also mentioned that if the query plans are different, the resources locked could be different but surely this shouldn't apply to my case as it is identical in table, parameters, query etc..

But I have noticed there are duplicate records inside the table (not a lot), so my question is are there gaps in my knowledge about the usage of updlock and what could be causing this behaviour?

question from:https://stackoverflow.com/questions/65948932/sql-server-updlock-serializable-doesnt-block-100

Answer 1

To confirm this you should run an Extended Events session and capture the actual locks taken when the procedure is called. It seems likely that the locks are only being taken at the row level and looking at the definition of HOLDLOCK states it is only for the statement.

You may either need to include a TABLOCK or consider setting the TRANSACTION ISOLATION LEVEL to SERIALIZABLE for the transaction.