Opinions - with MS SQL Server - how do you lock?

[szlamany]

This is a good example where you should specify the locking hint yourself. I'm guessing you use TABLOCKX or a XLOCK hint.

We avoid locking for situations like this by using a WHERE clause that only updates if the row is not changed by another user - here's an example SPROC (not a real one - just whipped it up)...

Create Procedure GetValue 
As
Declare @LastVal int
Declare @NextVal int

Do_Again:

Set @LastVal=(Select LastValue From SomeTable Where ConfItem='LastTrans')
  -- Get the last value from the config table

Set @NextVal=@LastVal+1
  -- Increment it by one

Update SomeTable Set LastValue=@NextVal
   Where ConfItem='LastTrans' and LastValue=@LastVal
  -- Update the row - but only if it's not changed by another user
  -- Shouldn't really be possible anyway since this SPROC is a "transaction"

If @@RowCount=0 Goto Do_Again
  -- If the ROWCOUNT=0 - meaning we did not update - must be because
  -- we need to do it again...

Select @NextVal "NextValue"
  -- Return the value