Will DbContextTransaction.BeginTransaction prevent this race condition

Question

I have a method that needs to "claim" a payment number to ensure it is available at a later time. I cannot just get a new payment number when ready to commit to the database, as the number is added to a signed token, and then the payment number is taken from the signed token later on when committing to the database to allow the token to be linked to the payment afterwards.

Payment numbers are sequential and the current method used in existing code is:

• Create a Payment
• Get the last payment number from the database
• Increment the payment number
• Use this payment number for the Payment
• Update the database with the incremented payment number

In my service I am trying to prevent the following race-condition:

1. My service reads the payment number (eg. 100)
2. Another service uses and updates the payment number (now 101)
3. My service increments the number locally (to 101) and updates the database (still 101)

This would produce two payments with a payment number of 100.

Here is my implementation so far, in my Transaction class:

private DbSet<PaymentIdentifier> paymentIdentifier;

//...

private int ClaimNextPaymentNumber()

{
    int nextPaymentNumber = -1;

    using(var dbTransaction = db.Database.BeginTransaction())
    {
        int lastPaymentNumber = paymentIdentifier.ElementAt(0).Identifier;
        nextPaymentNumber = lastPaymentNumber + 1;

        paymentIdentifier.ElementAt(0).Identifier = nextPaymentNumber;
        db.SaveChanges();

        dbTransaction.Commit();
    }
    
    return nextPaymentNumber;
}

The PaymentIdentifier table has a single row and a single column "Identifier" (hence the .ElementAt(0)). I am unable to change the database structure as there is lots of legacy code relying on it that is very brittle.

Will having the code wrapped in a transaction (as I have done) protect against the race condition, or is there some Entity Framework / PostgreSQL idiosyncrasies I need to deal with to protect the identifier from being read whilst performing the transaction?

Thank you!

(As a side point, I believe lots of legacy code in the other software connecting to the database simply ignores the race condition and relies on it being "very fast")

Answer 1

The database prevents the race condition by throwing a concurrency violation error in this case. So, I looked at how this is handled in the legacy code (following the suggestion by @sergey-l) and it uses a simple retry mechanism. So, I did the same:

private int ClaimNextPaymentNumber()
{
    DbContextTransaction dbTransaction;
    bool failed;
    int paymentNumber = -1;

    do
    {
        failed = false;

        using(dbTransaction = db.Database.BeginTransaction())
        {
            try
            {
                paymentNumber = TryToClaimNextPaymentNumber();
            }
            catch(DbUpdateConcurrencyException ex)
            {
                failed = true;
                ResetForClaimPaymentNumberRetry(ex);                        
            }

            dbTransaction.Commit();
            concurrencyExceptionRetryCount = 0;
        }
    }
    while(failed);

    return paymentNumber;
}