0

I have come up with scenario and i am really struggling with it. Please help me in this. i have table below and store procedure is calling for this table on button click in c# and data source is assign to grid.

Id    Product   QtyReceive  QtyDispatch  Location
1      HP2030     20            0          A

Below is procedure which is call on click event and it just select the require quantity of product which we require from specific location.

        DECLARE @Data TABLE
         (
         Id int identity(1,1)
        , Product varchar(10)
        , QtyReceive  int
        , QTYDispatch int
        , Location varchar(10)
        )
        DECLARE @Qty int = 10

        INSERT @Data VALUES
        ('HP2030', 20 ,5,'A');

        WITH sumqty AS 
        (
         SELECT *, SUM(QtyReceive -QTYDispatch) OVER (PARTITION BY Product ORDER BY Id) AS TotalQty FROM @Data
        )
        ,takeqty AS (
        SELECT *, 
        CASE 
          WHEN @Qty >= TotalQty THEN QtyReceive
          ELSE @Qty - ISNULL(LAG(TotalQty) OVER (PARTITION BY Product ORDER BY Id), 0)
        END AS TakeQty
        FROM sumqty
        )
        SELECT 
                Product
            ,   TotalQty
            ,   TakeQty
            ,   Location
        FROM takeqty WHERE TakeQty > 0
        ORDER BY Location;

Now there is a problem let say two user sitting at two different computer and User 1 required 10 quantity and other user required 5 quantity. They click button to get quantity at same time and save it at same time and dispatch quantity update with quantity 10 instead of 10+5=15. It happens when both user hit save at same time. No of user can be vary. Is there any solution to this problem?

I try to understand the whole scenario.

Prabhat Sinha
  • 1,500
  • 20
  • 32
user1891251
  • 41
  • 1
  • 1
  • 12
  • Look for timestap. The idea is you select timestamp and when updating check if timestaps are equal. If equal then update, else someone updated already and throw exception. – Giorgi Nakeuri Apr 08 '16 at 21:08
  • How do you save data? Technically, you should insert one record per user. it is not a good idea to have one record and update that on every save. or follow the above comment to have timestamp or rowversion field to manage concurrency. – FLICKER Apr 08 '16 at 21:11
  • I am working on warehousing system and in my case it happens one record can be updated by multi user because they required quantity is available in one Row as you can see in above scenario. – user1891251 Apr 08 '16 at 21:17
  • You need to have some transactionality in the process - and it doesn't look like you list out the piece where the values are stored in the database, which is where you're running into trouble. Please provide more code. – David T. Macknet Apr 08 '16 at 21:21
  • can we use transaction on specific procedure call? – user1891251 Apr 08 '16 at 21:27
  • As everyone is mentioning here you could you transactions and then if desiered a combination of that and a "ModifiedDate" column. The "ModifiedDate" column could even be used to return a warning back to the UI to tell the user if their data is out of date from when it was first loaded in. See this for using transactions: http://stackoverflow.com/questions/1195858/how-to-deal-with-concurrent-updates-in-databases. – David Oesterreich Apr 08 '16 at 21:36

0 Answers0