StoredProc insert and update multiuser issue

Question

Post reply

StoredProc insert and update multiuser issue

jason.havelock

SSC Enthusiast

Points: 178

June 26, 2019 at 3:24 pm

#3657779

Happy to day to all,

I am looking into an issue with a stored procedure where are where there are multiple users the code does the select but the update may fail: (as well i want to be educated on the solution so please push into a direction of learning as well)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

       ALTER PROCEDURE [dbo].[GetNextTransactionNo]
              @LocationId uniqueidentifier,
              @NextTransactionNo nvarchar(20) out
       AS
       BEGIN
  BEGIN TRANSACTION

   set @NextTransactionNo=null           
                  
   select @NextTransactionNo=[Next Transaction No.] from dbo.[database] 
 
   UPDATE dbo.[Database] set [Next Transaction No.]=CONVERT(bigint,[Next Transaction No.])+1    
  COMMIT TRANSACTION
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sterving Newbie Points: 4 More actions · Answer 1

There is an inherent flaw in this code and generally manifests itself under heavier than normal load. This is because "select" statement does not lock the row being read that would have prevented concurrent access to transaction number.

The simple fix is to put the update before the select. The update locks the row so that no other session can read transaction number until current session commits.

We had this same issue in production application that worked fine for years until load levels reached certain levels and clients started getting same transaction numbers.

Also, it may be worth noting that SQL Server 2016 introduces Sequence Object that eliminates this issue altogether.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

I am assuming by this code that you want the value after the table has been updated. You can do this in a single statement now - using the OUTPUT clause.

Here is a quick example:

Declare @tranTable Table (
        LocationID int
      , NextTransactionNo bigint
        );

 Insert Into @tranTable (LocationID, NextTransactionNo)
 Values (1, 1)
      , (2, 1);

Declare @LocationID int = 1;

Declare @outputTable Table (CurrentTransactionNo bigint, PreviousTransactionNo bigint);

 Update tt
    Set tt.NextTransactionNo += 1
 Output inserted.NextTransactionNo, deleted.NextTransactionNo Into @outputTable(CurrentTransactionNo, PreviousTransactionNo)
   From @tranTable tt
  Where tt.LocationID = @LocationID;

 Select * From @outputTable ot;
 Select * From @tranTable tt;

The table @tranTable is just for sample data - you would use your actual table. This is just to show how it could be done with the Output clause.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jason.havelock SSC Enthusiast Points: 178 More actions · Answer 3

Thank you all, very informative and my load testing on this proc exhibited the correct mal-behaviours. Appreciate everyone. Cheers.