3

I am issuing the following query with an UPDLOCK applied:

select @local_var = Column
from table (UPDLOCK)
where OtherColumn = @parameter

What happens is that multiple connections hit this routine which is used inside a stored procedure to compute a unique id. Once the lock acquires we compute the next id, update the value in the row and commit. This is done because the client has a specific formatting requirement for certain Object ID's in their system.

The UPDLOCK locks the correct row and blocks the other processes, but every now and then we get a duplicate id. It seems the local variable is given the current value before the row is locked. I had assumed that the lock would be obtained before the select portion of the statement was processed.

I am using SQLServer 2012 and the isolation level is set to read committed.

If there is other information required, just let me know. Or if I am doing something obviously stupid, that information is also welcome.

Szymon
  • 42,577
  • 16
  • 96
  • 114
J Cox
  • 311
  • 3
  • 9
  • Possible duplicate of [updlock-holdlock](http://stackoverflow.com/questions/7843733/confused-about-updlock-holdlock) – bastos.sergio Sep 26 '13 at 16:15
  • My question is not about UPDLOCK in general, it is about when the Select portion of the SQL statement is evaluated in relation to obtaining a record lock, or blocking while waiting for the lock. Since it is part of a single statement, I expect( right or wrong ) that the Select would not be evaluated until the lock was acquired. – J Cox Sep 26 '13 at 16:33

2 Answers2

3

From the SQL Server documentation on UPDLOCK:

Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.

That means that other processes can still read the values.

Try using XLOCK instead, that will lock other reads out as well.

slugster
  • 49,403
  • 14
  • 95
  • 145
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • I think the question is whether other processes would be able to read the same values using the same SELECT statement. Since a second process would try to obtain the UPDLOCK, it should be blocked from performing the SELECT until the first one has finished its UPDATE, right? – Ben Aaronson Sep 09 '14 at 15:28
  • The actual problem I encountered was that the assignment to the @local_var occurred BEFORE the lock was acquired and did not block so one of the callers got an old value. I was expecting the block to apply to the Entire Statement before the assignment took place. – J Cox Jan 17 '15 at 14:19
  • I think you have that the wrong way around. I think the assignment is happening after the lock is acquired, but before the subsequent update (see my answer) - hope this helps although I'm no expert. – MultiMat Aug 03 '16 at 15:14
0

I think the issue is that your lock is only being held during this Select. So once your Stored Proc has the Value, it releases the Lock, BEFORE it goes on to update the id (or insert a new row or whatever).

This means that another query running in Parallel is able to Query for the same value and then Update/Insert the same row.

You should additinoally add a HOLDLOCK to your 'with' statement so that the lock gets held a little longer.

This is treated quite well in this Answer

Community
  • 1
  • 1
MultiMat
  • 93
  • 8