Take a look at
Record ID generator question Message #
611104.
>I have this SP to retrieve PK values from a table that has a row for each user table:
>
>
>BEGIN TRANSACTION
>
>SELECT @cNext = NextId
> FROM IdTable (HOLDLOCK)
> WHERE Table = @tcTablename
>IF (@@rowcount = 1)
> BEGIN
>
> UPDATE IdTable
> SET NextId = NextId + 1
> WHERE Table = @tcTablename
> COMMIT TRANSACTION
> END
>ELSE
> SET @cNext = NULL
> ROLLBACK TRANSACTION
>END
>
>
>
>Intermittantly, no PK value is being assigned. I am wondering if this could be a problem with the HOLDLOCK? If another query runs this SP while the first one is in a transaction will the second one block or will it fall into the ELSE clause? Will an UPDATE lock fix it?
>
>TIA
--sb--