0

I have a system in which I generate the transaction ids based on a table. The number must have a specific format therefore using database sequences is not an option. Moreover, the number transactions types is variable which means creating variable number of sequences. The table has the following structure:

public class TransactionSequence : BaseEntity<int>, IAggregateRoot
{
    public int Year { get; set; }
    public string Prefix { get; set; }
    public long Sequence { get; set; }        
    public string Service { get; set; }
    public int Length { get; set; }
    public bool IsCurrent { get; set; }
}

The code for the service which generates the number is as shown below:

public class NumberingService : INumberingService
{
    public static int yearLength = 2;
    public static int monthLength = 2;
    public static int dayLength = 2;

    private readonly IRepository<TransactionSequence> _repository;
    private readonly NumberingConfiguration _numbering;
    
    
    public NumberingService(IRepository<TransactionSequence> repository, 
        NumberingConfiguration numbering)
    {
        _repository = repository;
        _numbering = numbering;
    }
    
    public async Task<Result<string>> GetNextNumberAsync(string service, int maxlength, UserEntity sysUser, string prefix = "")
    {
        string transactionId = string.Empty;

        try
        {
            var spec = new CurrentYearNumberingSpec(service);
            var sequence = await _repository.GetBySpecAsync(spec);
            if (sequence == null)
            {
                await AddServiceNumberingAsync(service, maxlength, sysUser, prefix);
                sequence = await _repository.GetBySpecAsync(spec);
            }

            sequence.Sequence = sequence.Sequence + 1;
            await _repository.UpdateAsync(sequence);
            int month = DateTime.Now.Month;
            int day = DateTime.Now.Day;
            var length = GetLength(sequence);

            transactionId = sequence.Prefix + (sequence.Year % 100).ToString("D" + 2) + month.ToString("D" + 2) + day.ToString("D" + 2) + sequence.Sequence.ToString("D" + length);
        }
        catch (Exception ex)
        {
            return Result<string>.Error(ex.Message);
        }
        return Result<string>.Success(transactionId, "Retrieved the next number in the sequence succesfully!");
    }
    
    private static int GetLength(TransactionSequence sequence)
    {
        return sequence.Length - sequence.Prefix.Length - dayLength - monthLength - yearLength;
    }
}

Note: I am only showing an excerpt of the code that contains the relative info!

The problem: Since the system is highly concurrent, each request tries to obtain a transaction id when submitted. Thus, there is a high contention for TransactionSequence row which is currently active since it will be generating the transaction id via subsequent updates. This means that there will absolutely be locking.

Solutions I tried: 1- Optimistic Concurrency via ROWVERSION with retries, this had the worst performance since optimistic concurrency makes sense only if the collision possibility is rare! But since the collision is almost guaranteed this solution had the worst performance. Either that or I did not implement it correctly!

2- Locking via SemaphoreSlim, this had an acceptable performance but its problem was that it would not scale in load balanced scenarios.

3- Distributed Locking via Redis, this had an approximate performance to SemaphoreSlim but still not the performance I am looking for!

4- Queuing via RabbitMQ with prefetch size of 1, This had a better performance than the aforementioned solutions but still I wonder if there is an optimal solution!

5- Using HiLo algorithm, I did not implement this but I have read about it as in the link below: CQS with Database-Generated Ids

I want to know if there is a better or a well-known solution to this problem

My Environment: ASP .NET CORE 6 EF CORE 6 SQL SERVER 2019

I hope this was clear enough, and thanks in advance!

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Saleh Omar
  • 739
  • 2
  • 8
  • 29
  • 1
    I would suggest to create stored procedure which always return new Id even transaction fails. Current sequence number should be stored in some table. Something like that (sorry not checked syntax) `MERGE CounterTable as t USING CounterTable as s ON 1 = 1 WHEN MATCHED UPDATE SET Version = s.Version + 1 WHEN NOT MATCHED INSERT (Version) VALUES (1) OUTPUT INSERTED.Version` – Svyatoslav Danyliv Jan 25 '23 at 17:39
  • Thanks @SvyatoslavDanyliv for your suggestion. I will test your code to see how it performs.(Y) – Saleh Omar Jan 25 '23 at 18:39
  • 1
    i would suggest pregenerating a large number of transactionIDs, and then just dole them out when you need it. Once in a while, you populate up the IDs if you're running out. Alternatively, don't generate the IDs on the fly but rather store them without the ID, and then assign ID later. Not sure if second version is possible but it's usually how to do it. – siggemannen Jan 25 '23 at 20:39
  • @siggemannen, I have heard about this method before. But I wonder if there are already known algorithms or implementations in place. On the other hand, since I have a distributed environment, prepopulating them in the shared Redis cache would be logical would not it? – Saleh Omar Jan 25 '23 at 21:22
  • Hmm, now what i read your code. You have a transaction number which is some prefix, postfix, year and a sequence number. But the only thing you actually need to generate is sequence number. The rest is predefined. Also, each transaction type have own sequence, right. So why not use SQL Server sequence, one per each transaction type? If sequence is restarting each year, that's the only time you actually will have some sort of pressure is around 1st of january. For those matters, you can pre-create sequence numbers for each year and set them to 0. Then it will be ready. – siggemannen Jan 25 '23 at 21:40
  • Unfortunately, i'm not familiar with redis or other distributed system. But if i remember correctly, they have some sort of "sequence" support built-in as well. I also remember it being not competely reliable :) – siggemannen Jan 25 '23 at 21:42
  • @siggemannen, I am not using Database Sequences because the number of transaction types is variable and might increase as we have more providers or more services. Then, I would need to create a database sequence every time I have a new service or provider added. – Saleh Omar Jan 26 '23 at 19:59

0 Answers0