Hi,

I have an application where I need to create a unique ID. Because of my application's requirements I cannot use an IDENTITY column for this purpose. I want to use a classic VFP solution where, when you call a stored procedure, you pass as an input parameter the name of a table, and the stored procedure returns the new ID, either as an output parameter or result set.

My procedure looks like this:

CREATE PROCEDURE sp_sel_nextNumber 

@nvcObjectName 	nvarchar(80), 
@intNextNumber 	int OUTPUT 

AS

-- Force the Object Name string to lower case.
SET @nvcObjectName = LOWER( @nvcObjectName )

BEGIN TRANSACTION

-- Query the nextNumber table for the next available number.
-- Force and Exclusive lock on the row if it's found.

SELECT	@intNextNumber = intNextNumber 
FROM	tblNextNumber WITH (XLOCK, ROWLOCK)
WHERE	nvcObjectName = @nvcObjectName

IF @@ROWCOUNT = 0
BEGIN
	SET @intNextNumber = 1

	INSERT INTO tblNextNumber 
	( nvcObjectName, intNextNumber ) 
	VALUES 
	( @nvcObjectName, @intNextNumber )
END

ELSE

BEGIN
	SET @intNextNumber = @intNextNumber + 1

	-- Increment the next id counter
	UPDATE	tblNextNumber 
	SET	intNextNumber	= @intNextNumber
	WHERE	nvcObjectName	= @nvcObjectName  
END

COMMIT TRANSACTION
GO

I am using the locking hints "WITH (XLOCK, ROWLOCK)" in the select statement to prevent other users from reading the same row before my stored procedure can increment the value. However, this technique is creating an excessive amount of deadlocks.

I have even simplified the procedure by eliminating the INSERT logic that executes if the objectName doesn't exist. I have also reversed the sequence the the SELECT statement and the UPDATE statement to try to prevent deadlocks that occur as a result of lock escalaltions. Neither of these changes has helped.

So my question is this: What's the best way to make sure I have a unique return value and eliminate deadlocks?

TIA,

George