>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?
>


George,

See Re: Record ID generator question Message #611104