If you remove all ISOLATION LEVEL commands and remove the explicit BEGIN TRAN/COMMIT from Lynn's code, 90% of your deadlocks will simply vanish. You'll need to move usage of the proc outside of any external transactions to get rid of the 10%.

So far as when to use something like this "sequence table" method in SQL Server goes, my answer would be "almost never". Improper design and use of a similar function caused an average of 640 deadlocks per day with spikes to 4,000 per day at a previous company I worked for. Yes, the new correctly written function solved most of that but even the new function which would allow you to "reserve" a given "increment" of numbers was a pain to use and, done incorrectly, would result in duplication of some IDs.

Here's the code that my old DBA and I came up with. The day we replaced the original code with the following code, the deadlocks dropped for about 640 to 12. Most of the deadlocks that remained were from other sources. I also have to tell you that all of the error checking we built into the code is ridiculous because the core code has never failed since we installed it. Since it takes virtually no time to do those error checks, we left them in.

If you can't figure out how to use the code to do setbased updates using the increment value instead of using a loop to do just one row at a time, please post back because it's an important concept to using such (ugh!) sequence tables in a setbased manner.

CREATE PROCEDURE dbo.GetNextID

/****************************************************************************************

Purpose:

This stored procedure is used to get a NextID for the table identified by the @KeyID

parameter. It will "reserve" a block of ID's according to the @IncrementValue parameter.

The @NextID returned is always the first ID of a reserved block of numbers. The reserved

block size defaults to 1.

Usage:

EXEC @return = dbo.GetNextID @KeyID,@IncrementValue,@NextID=@NextID OUTPUT

Outputs:

1. Returns a -1 if error and 0 if success.

2. @NextID will be a -1 if an error occured. Otherwise, it will contain the first

NextID of the requested block of NextID's.

Notes:

1. This procedure has been enhanced compared to the original...

a. The UPDATE statement sets both the @NextID variable and the NextID column in the

NextID table eliminating the need for a separate SELECT from NextID after the

UPDATE.

b. Because of (1.a) above, there is no longer a need for a transaction. If the

UPDATE didn't work, there is no need for a ROLLBACK because nothing was updated.

c. Previous error handling did not correctly return the invalid KeyID if present.

d. A test has been added to ensure a negative value for @IncrementValue was not

passed in.

e. A test to ensure that @NextID was correctly updated has been added.

f. Repairs to the previous error routines have been made so that the values returned

to @@ERROR and @@ROWCOUNT are correctly used by more than one statement.

Revisions:

REV 01 - 01 Mar 2005 - Kalpa Shah, Jeff Moden --Rewrite original

REV 02 - 06 Feb 2010 - Jeff Moden -- Removed all company references

****************************************************************************************/

--=======================================================================================

-- Define the I/O parameters used by this procedure

--=======================================================================================

--===== Declare the passed parameters

@KeyID INTEGER, --Identifies table to get the NextID for

@IncrementValue INTEGER = 1, --Number of NextIDs to "reserve"

@NextID INTEGER OUTPUT --Returns start # of block of IDs

AS

--=======================================================================================

-- Main body of procedure

--=======================================================================================

--===== Suppress auto-display of row counts for appearance and speed

SET NOCOUNT ON

--===== Declare variables local to the loop

DECLARE@MyError INTEGER --Holds @@ERROR for additional processing

DECLARE @ErrMessage VARCHAR(100) --Holds calculated error messages because RaisError

--cannot calulate messages on the fly.

DECLARE @MyRowCount INTEGER --Hold @@ROWCOUNT for additional processing

--===== Preset @NextID to an error condition

SET @NextID = -1 --Defaults don't work consistently on OUTPUT parameters

--===== If the increment is not greater than zero, raise and error and exit immediately

IF @IncrementValue <= 0

BEGIN --Start of error processing

--===== Process errors (RaisError cannot do calcs for error message)

SET @ErrMessage = 'The NextID row could not be updated. '

+ 'Increment was set to '

+ CONVERT(VARCHAR(11),@IncrementValue) + '.'

RAISERROR (@ErrMessage,1,1)

RETURN -1 --Returns an error indication to calling procedure

END --End of error processing

--===== Update the correct NextID row according to the KeyID passed in.

-- Sets @NextID and the column to the previous value + the increment

-- simultaneously so we don't need to read from the NextID table to

-- get the value of @NextID in the following steps.

UPDATE dbo.NextID WITH (UPDLOCK)

SET @NextID = NextID = NextID + @IncrementValue

WHERE KeyID = @KeyID

-- Get the error value and rowcount

SELECT @MyError = @@ERROR, @MyRowCount = @@ROWCOUNT

--===== Check for errors, a rowcount of 1, and a non-default value for @NextID

IF @MyError <> 0 --An error did occur

OR @MyRowCount <> 1 --The row was not updated

OR @NextID = -1 --A new value for @NextID was not returned

BEGIN --Start of error processing

--===== Process errors (RaisError cannot do calcs for error message)

IF @MyError <> 0 --Error occured

SET @ErrMessage = 'The NextID row could not be updated.'

ELSE --1 row or @NextID was not not updated

SET @ErrMessage = 'The NextID row could not be updated. KeyID '

+ CONVERT(VARCHAR(11),@KeyID)

+ ' may not exist.'

RAISERROR (@ErrMessage,1,1)

RETURN -1 --Returns an error indication to calling procedure

END --End of error processing

--===== Calculate and return the first number in the block of reserved NextID's

-- to the @NextID output parameter

SELECT @NextID = @NextID - @IncrementValue

--===== Return a "success" indication to the calling procedure

RETURN 0

GO

To reiterate, using sequence tables in SQL Server just isn't the right thing to do and it took me a lot to say it that nicely ;-).