0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TFK
  • 275
  • 1
  • 2
  • 11

2 Answers2

0

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.

Martin Cairney
  • 1,714
  • 1
  • 6
  • 18
  • 1
    The definition of UPDLOCK states that it holds the lock until transaction completes. – TFK Jan 29 '21 at 07:24
0

Alright this is embarrassing and I feel like deleting this question but the point is after double checking the stored procedure on production, I found that the version differs from testing environment.

It is using UPDLOCK without serializable which explains why there are duplicated records, since if the row didn't exist there will be no locking during the select.

TFK
  • 275
  • 1
  • 2
  • 11