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")

CodePudding user response：

It helps you with the race condition only if all code, including legacy, will use this method. If there is still code that continue using client side incrementing without transaction, you'll get the same problem. Just exchange 'My service' and 'Another service' in your description.

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

Note that you can replace your code with simpler one by executing this query without explicit transaction.

update PaymentIdentifier set Identifier = Identifier   1 returning Identifier;

But again, it will not solve your concurrency problem until you replace all places where the Identifier is incremented. If you can change that, you would better use SEQUENCE or Generators that will safely provide you with incremental Ids.

CodePudding user response：

A transaction does not automaticaly lock your table. A Transaction just ensures that multiple changes to the database are done altogether or nothing at all (see the A (atomic) in ACID). But the thing you want is that only one session can read, add one, update the value. And after that is done the next session is allowed to do the same thing.

So you now have different possibilities:

1. Use a Sequence you can get the next value for example like that SELECT nextval('mysequencename'). If if two sessions try to get a value at the same time they will get two differnt values.
2. If you have more complex needs and want to store every "token" with additional data in a table. so every token is a row in the table with additional colums you could use table locking. With this you could restrict the access to table. So only one session is allowed to access the table at a time. But make sure that you use locks for as short as possible because this will become your performance bottleneck.