HOLDLOCK OR SET CONCURRENCY 1

[TitleistDBA]

I am having a problem with the following proc. Two processes can call the proc and they both get the same next id. I need to lock the record when I select it. Does anybody know how to use HOLDLOCK or SET CONCURRENCY LOCKCC.


CREATE PROCEDURE Webm_GetNextTransactionID
@Transaction_Type varchar(10),
@Next_Avail_Transaction_ID int OUTPUT,
@RtnMessage varchar(250) OUTPUT,
@Successind int OUTPUT
AS

DECLARE @err int
DECLARE @rowcnt int

SELECT @Successind = 1
SELECT @RtnMessage = ' '

/* This procedure retrieves the last transaction ID, increments and returns the transaction ID to the caller */

BEGIN TRANSACTION

/* Get Next Up Number from table */
SELECT @Next_Avail_Transaction_ID = Next_Avail_Transaction_ID from Transaction_ID_Next_Up where Transaction_Type = @Transaction_Type

SELECT @err = @@ERROR, @rowcnt = @@ROWCOUNT
IF( @rowcnt = 0 )
BEGIN
SELECT @RtnMessage = 'Transaction_ID_Next_Up row not found to increment'
SELECT @Successind = 0
END
IF( @err <> 0 )
BEGIN
SELECT @RtnMessage = description FROM master.dbo.sysmessages WHERE error = @err
SELECT @Successind = 0
END

/* If you got a record, increment and update */
IF (@rowcnt = 1)
BEGIN

SELECT @Next_Avail_Transaction_ID = @Next_Avail_Transaction_ID + 1
UPDATE Transaction_ID_Next_Up SET Next_Avail_Transaction_ID = @Next_Avail_Transaction_ID where Transaction_Type = @Transaction_Type

SELECT @err = @@ERROR, @rowcnt = @@ROWCOUNT
IF( @rowcnt = 0 )
BEGIN
SELECT @RtnMessage = 'Transaction_ID_Next_Up row not updated'
SELECT @Successind = 0
END
IF( @err <> 0 )
BEGIN
SELECT @RtnMessage = description FROM master.dbo.sysmessages WHERE error = @err
SELECT @Successind = 0
END

END


COMMIT

GO

http://www.acushnetcompany.com

 

[vongrunt]

If you don't have INSERTs, HOLDLOCK is equivalent to isolation level 2. Therefore try:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
...

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[TitleistDBA]

Thanks..
I'll try that.

http://www.acushnetcompany.com

 

[TitleistDBA]

That worked
Thanks for your help.

http://www.acushnetcompany.com