0

I've got a C#, Kendo MVC, Telerik Open Access web app that's incrementing a counter in a table and using that value to create a record in another table. When multiple users try to write to the table at the same time, the 2nd table ends up with non-consecutive numbers. Any ideas how to prevent this?

So here's a table NextNumber

----------------
| Next Number  |
----------------
| 50           |
----------------

When a record is added to the table Orders, it uses the number in NextNumber to get the order number and then updates NextNumber:

--------------------------------------
| Order Id    | Order Number | Date  |
--------------------------------------
| 1           |           48 | 10/15 |
--------------------------------------
| 2           |           49 | 10/15 |
--------------------------------------
| 3           |           50 | 10/15 | <- New record
--------------------------------------

----------------
| Next Number  |
----------------
| 51           | <- Value updated
----------------

What's happening is that multiple people are updating the NextNumber table and a (somehow) a number gets skipped so the tables end up like this:

--------------------------------------
| Order Id    | Order Number | Date  |
--------------------------------------
| 1           |           48 | 10/15 |
--------------------------------------
| 2           |           49 | 10/15 |
--------------------------------------
| 3           |           50 | 10/15 | 
--------------------------------------
| 4           |           52 | 10/16 | <- 51 was skipped
--------------------------------------
| 5           |           53 | 10/16 | 
--------------------------------------

----------------
| Next Number  |
----------------
| 54           |
----------------

What happens is a report comes out later and it looks like order 51 is missing or never entered.

Here's the code:

public void UpdateNext()
{
    using (OrderProject.EntitiesModel db = new OrderProject.EntitiesModel())
        {

            NextNumber nextID = db.TblNextNumbers.FirstOrDefault();
            int ID = nextID.NextNumber
            nextID.NextNumber = ID + 1

            try
            {
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                try
                {
                    db.SaveChanges();
                }
                catch(Exception exception)
                {
                    throw;
                }
            }
        }
    }

*NOTE: I'm not the original developer on this and don't know the full reason why the client wanted to use this NextNumber table instead of setting the ID as an identity column. Just know that I've already suggested that and that it has to be this way.

boilers222
  • 1,901
  • 7
  • 33
  • 71
  • 'has to be this way' = why crap code exists in the world. "We want you to improve our system" ... "You cannot do anything new to anything, that code just works! Don't touch it!" (facepalm). Your best bet is depending how this is implemented is to implement a lock, mutex, concurrency handling pattern to not enable this scope to be reached until it is completed. https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/lock-statement – djangojazz Oct 26 '17 at 16:32
  • Client should have no input in that - `how` number is generated is an implementation detail. Client has input on `business rules only`. in this case if I were you I'd figure out the order number generation business rules. Likely there is no magic there and this nightmare can be just fixed. Also the current solution just does not work, which makes `this code just works, don't touch it` argument irrelevant. – Alex Buyny Oct 26 '17 at 18:34

0 Answers0