并发取主键 加锁

create PROCEDURE UP_NewTableID

@TCode VARCHAR(20),@NextID INT OUTPUT

 

AS

DECLARE @CurTKey INT,@NextTKey INT

BEGIN TRAN TransID

    SELECT @CurTKey=TKey FROM KeyTable WITH (UPDLOCK)--采用更新锁，并保持到事务完成

      WHERE TCode=@TCode

    IF @@ROWCOUNT=0    BEGIN

            ROLLBACK TRAN TransID

            RAISERROR('Warning: No such row is exists',16,1)

             RETURN

      END

      SET @NextTKey=@CurTKey+1

 

        WAITFOR DELAY '00:00:05'

 

       UPDATE KeyTable

         SET TKey=@NextTKey WHERE TCode=@TCode--此处无需验证TKey是否与SELECT的相同

 

COMMIT TRAN TransID

 

SET @NextID=@NextTKey

 

GO