Sql-server – semaphore in SQL Server

sql servert-sql

I wrote a stored procedure in sql to take a number from table.
it has lots on records which has only column.
whenever a call it , is should take the first record and return it after that delete the record.
it works properly when we have just one thread,the problem appears when I use multithreading in my program and the procedure return the duplicate number.
the procedure is like below:

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      SET @subscribeNo = -1;

      BEGIN TRAN T1;

      UPDATE UNIT_SBS_NOS
      SET    Free_SBS_NO = Free_SBS_NO
      WHERE  Sbs_Trf_Type = @Sbs_Type;

      SELECT @subscribeNo = (SELECT TOP(1) FREE_SBS_NO
                             FROM   UNIT_SBS_NOS WITH(nolock)
                             WHERE  Sbs_Trf_Type = @Sbs_Type);

      DELETE FROM UNIT_SBS_NOS
      WHERE  FREE_SBS_NO = @subscribeNo;

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END 

Best Answer

Generally your current code shouldn't allow two concurrent transactions to process the same @Sbs_Type simultaneously.

Two concurrent threads can't both acquire U locks on the rows matching WHERE Sbs_Trf_Type = @Sbs_Type.

If two threads simultaneously call GetSubscribeNo with the same @Sbs_Type when there are no matching rows whilst meanwhile a third transaction inserts a row for that @Sbs_Type I can see how this would fail however.

One or both of the GetSubscribeNo calls would need to get past the UPDATE statement before the insert and thus fail to lock anything that would block the other one.

A better way of doing this anyway would be with the OUTPUT clause.

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      DECLARE @Out TABLE (Subscribe_No INT);

      SET XACT_ABORT ON;
      BEGIN TRAN T1;

      WITH T
           AS ((SELECT TOP(1) *
                FROM   UNIT_SBS_NOS WITH (ROWLOCK, READPAST)
                WHERE  Sbs_Trf_Type = @Sbs_Type))
      DELETE FROM T
      OUTPUT DELETED.FREE_SBS_NO INTO @Out;

      /*Default to -1 if no rows*/
      SELECT @subscribeNo = ISNULL(MAX(Subscribe_No), -1)
      FROM   @Out

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END