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
>-- Note the HOLDLOCK!
>SELECT @cNext = NextId
>  FROM IdTable (HOLDLOCK)
>  WHERE Table = @tcTablename
>IF (@@rowcount = 1)
>  BEGIN
>    -- get the next Pk
>    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