Best way to manage increment counter in C# .Net Core

Question

Given 2 tables PaymentServices and PaymentTransaction, the requirement is, for each payment services they can set counter start from what number. So in PaymentServices table I added column Counter. So user can set minimum counter to start. EG 5000.

So every transaction created it will count to 5001, 5002 and etc then store the value in column ReceiptNo in table PaymentTransaction

So in my case, I can't use auto generate ID from database.

Here is the code:

var getPaymentServices = _context.PaymentServices.First(c=>c.Id == SelectedPaymentServiceId);

// Create new transaction and get current counter
var addNewPayment = new PaymentTransaction
{ 
   PaymentServiceId = getPaymentServices.Id,
   Amount = AmountToPay,
   ReceiptNo = getPaymentServices.Counter,
};

getPaymentServices.Counter++;

_context.Add(addNewPayment);

await _context.SaveChangesAsync;

My QA found, some of the transaction got duplicate ReceiptNo after do load test (around 50000 request). Even no to much but, it will impact the customer billing system.

What the best way to manage this?

You should add a unique constraint in the `ReceiptNo` column. — Guokas, Jan 25 '21 at 05:46
This looks like a race condition around the counter read and increment. I would consider `Interlocked.Increment` here. If the race condition was on the database side I would use a transaction with serializable isolation level. — Zer0, Jan 25 '21 at 05:52
Why do you need to store a counter? You just need to store the max. To enforce your business rules, take a count of the transaction table to make sure it is less than the max. — John Wu, Jan 25 '21 at 06:17
@Zer0, are you refer something like this? https://stackoverflow.com/questions/15669383/how-to-inc-dec-multi-user-safe-in-entity-framework-5/15669840#15669840 — Azri Zakaria, Jan 25 '21 at 06:24
@JohnWu, one of requirement they can start `ReceiptNo` from any number. No limit. — Azri Zakaria, Jan 25 '21 at 06:27
@AzriZakaria Oh okay I thought you were doing some kind of rate limiting or subscription plan. If you just want to make sure the receipt no is unique, put a uniqueness constraint on it. If there is an accidental re-use of a receipt no, catch the error (see [this answer](https://stackoverflow.com/a/31516402/2791540)) and resubmit. — John Wu, Jan 25 '21 at 06:50
I think you should also consider what happens if your service fails? Should the counter start from the beginning (duplicates numbers again) or should it proceed with the last value in db + 1 or smth else? — E. Shcherbo, Dec 23 '22 at 17:28

score 0 · Answer 1 · answered Jan 25 '21 at 05:27

0

I guess your answer is under this question. You can set the starting-value of an ID

Set start value for column with autoincrement

answered Jan 25 '21 at 05:27

Robert Zeßin

36
5

The table `PaymentTransaction` is shared table with another payment services. So I don't think can use for that method. – Azri Zakaria Jan 25 '21 at 05:31

jcHernande2 · Answer 2 · 2021-01-25T06:21:27.300

i think that a way is use a transaction for block the operations in the tables PaymentServices and PaymentTransaction. other way is apply a trigger in sql when insert a record of PaymentTransaction update PaymentServices.Counter

Example:

using (DbContextTransaction transaction = context.Database.BeginTransaction())
    {
        try
        {
          var getPaymentServices = _context.PaymentServices.First(c=>c.Id == 
          SelectedPaymentServiceId);

          // Create new transaction and get current counter
          var addNewPayment = new PaymentTransaction
          { 
            PaymentServiceId = getPaymentServices.Id,
            Amount = AmountToPay,
            ReceiptNo = getPaymentServices.Counter,
          };

             getPaymentServices.Counter++;
             _context.Add(addNewPayment);
             await _context.SaveChangesAsync;
             transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            Console.WriteLine("Error.");
        }
    }

mahooresorkh · Answer 3 · 2022-12-23T19:36:32.660

I think what's happening here, is race condition. That may occur when many requests are being sent to the server and the server wants to handle them through different threads.

For example :

One request is being processed in Thread-A and it gets the PaymentService that has id equal to SelectedPaymentServiceId but before the Counter property of the selected PaymentService gets increased, the other thread (let's say Thread-B) starts the block and gets the PaymentService that has id equal to SelectedPaymentServiceId again. Notice that the Counter property of PaymentService is the same in the two threads. So both of them add PaymentTransaction with the same ReceiptNo !!

To prevent that from happening you can use lock statement in C#. When a thread reaches to the starting point of the locked block, the other threads are not allowed to enter the locked block until the entered thread finishes its job and exits the block. In other words, the lock statement ensures that a single thread has exclusive access to the lock object (take a look at this).

Thus, add the lock statement to the code block.

lock (LockObject) {
  var getPaymentServices = _context.PaymentServices
  .First(c=>c.Id == SelectedPaymentServiceId);

  var addNewPayment = new PaymentTransaction
    { 
       PaymentServiceId = getPaymentServices.Id,
       Amount = AmountToPay,
       ReceiptNo = getPaymentServices.Counter,
    };

    getPaymentServices.Counter++;

    _context.Add(addNewPayment);

    _context.SaveChanges();
}

Notice that await can not be used inside the locked block (so it is omitted) and LockObject is a private static property of the class that contains these lines of code(often a Controller class) and you can define it like this, in the Controller class body:

private static object LockObject = new object();

Yes, you are totally right. I did not see the `await` keyword. I edited that. Thank you. — mahooresorkh, Dec 23 '22 at 19:38
to use async lock `SemaphoreSlim` with count `1` can be considered, `await` can be used inside it :) — E. Shcherbo, Dec 23 '22 at 21:02

score -1 · Answer 4 · answered Jan 25 '21 at 06:03

This is a typical logic in retail. There are many different way tackling this type of business logic.

Allow me to simplify this use case (in mainly retail domain):

We want to allocate a check ID, which from 1 to N. N is usually capped, let's say 5000. If you have 2 registers, you may want to have register 1 allocated with check ID (1 - 5000) and register 2 (5001 to 10000), and so on. Check ID is generally reset every day. So even a register hasn't used all check IDs today, it will reset back to 1 again the next day.

Here are 2 straightforward options

Option 1

Every day when reset, allocate CheckID: A to B by register number in a table.

Next, every time opening a new check, do a database transactional get and mark operation, say GetNextCheckIdByRegister(regNo), which will do

get next Id
mark the Id used

An abandoned transaction does not need to rollback the check Id, as it will be kept as an abandoned check, for auditing purpose.

The drawback of this is it does not scale very well in distributed system, or with many stores and registers. Depending on your scenario, it might work.

Option 2

Don't worry about the check Id upfront, instead, worry about it later.

In the time of transaction, you simply use transaction id in your data. Once the entire transaction is finalized, you publish the final transaction events to a service that consolidate checks. In here, you can start generating check numbers. This can be either near-real-time or by the end of the business day. Either way, it does not care latency too much, so you can have the locking strategy in table, in code, or whatever.

The drawback is it might overkill your scenario.

Best way to manage increment counter in C# .Net Core

4 Answers4