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. — David, 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

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.");
        }
    }

score -1 · Answer 3 · 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

3 Answers3