1

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 
6
  • Which DBMS are you using? It looks like SQL Server. Commented Feb 18, 2014 at 11:56
  • 1
    What is the purpose of SET Free_SBS_NO = Free_SBS_NO? Commented Feb 18, 2014 at 12:02
  • yes,Its SQL Server!
    – user34438
    Commented Feb 18, 2014 at 12:17
  • "SET Free_SBS_NO = Free_SBS_NO" I use this to lock the records to avoid accessing other threads to get duplicate numbers
    – user34438
    Commented Feb 18, 2014 at 12:18
  • CREATE TABLE [dbo].[UNIT_SBS_NOS]( [ID] [int] NOT NULL, [OBJ_VERSION] [numeric](8, 0) NOT NULL, [FREE_SBS_NO] [numeric](7, 0) NOT NULL, [SBS_TRF_TYPE] [numeric](1, 0) NOT NULL, CONSTRAINT [PKUNITSBSNOS] PRIMARY KEY CLUSTERED )
    – user34438
    Commented Feb 18, 2014 at 12:43

2 Answers 2

2

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 
0

I just stumbled across his question on accident. For some reason I'm compelled to explain why this issue has occurred as it will build a strong database architecture concept as well as a better understanding of programming logic.

Deletes in SQL server are sequential/serial. You cannot multi-thread deletes for many reasons.

Rough example: thread one "delete from tblX where tblX.ID = @myVar" if you fire off 2 threads, they will "step on each other's toes". Layman's terms: one piece of pie, you and a friend want it, you both go for it, ends up bloody.

*Note: this is an over simplification, but hopefully you get the point.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.